VBA - Filling sequence from 1 to N
up vote
0
down vote
favorite
I'm not sure the title is clear, but essentially what I need is to automate the assignation of sites. The number of rows is usually around 60, but could go up to 200. The assignation of site number could vary sometimes from 5 through 10, depending on the site.
I have a reference tab showing, for example Delaware = 6, New York = 4.
So what I need is for column G to populate with numbers 1 through whatever the limit of the location is ("Delaware" or "New York" will be selected elsewhere in the file). So if it's Delaware, it would need to populate 1,2,3,4,5,6,1,2,3,4,5,6,and so on down to the last populated row. If New York, 1,2,3,4,1,2,3,4,1,2,3,4,and so on down to the last populated row.
I've found a number of VBA functions that will handle autopopulating to a specified number of rows, but I haven't seen anything that will populate a series up to N (where N=number assigned to the site in reference table) that may often change. Is this possible?
microsoft-excel vba microsoft-excel-2013
asked Nov 21 at 16:40
Lenny
11
New contributor
Lenny is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
up vote
0
down vote
favorite
I'm not sure the title is clear, but essentially what I need is to automate the assignation of sites. The number of rows is usually around 60, but could go up to 200. The assignation of site number could vary sometimes from 5 through 10, depending on the site.
I have a reference tab showing, for example Delaware = 6, New York = 4.
So what I need is for column G to populate with numbers 1 through whatever the limit of the location is ("Delaware" or "New York" will be selected elsewhere in the file). So if it's Delaware, it would need to populate 1,2,3,4,5,6,1,2,3,4,5,6,and so on down to the last populated row. If New York, 1,2,3,4,1,2,3,4,1,2,3,4,and so on down to the last populated row.
I've found a number of VBA functions that will handle autopopulating to a specified number of rows, but I haven't seen anything that will populate a series up to N (where N=number assigned to the site in reference table) that may often change. Is this possible?
microsoft-excel vba microsoft-excel-2013
asked Nov 21 at 16:40
Lenny
11
New contributor
Lenny is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1
It would be helpful if you edit your question to include one of those VBA functions. If it gets close to what you want, it may just need some tweaking to work for you. If you don't know how to post the code, just paste it in and someone will format it for you.
– Rey Juna
Nov 21 at 17:08
2
It's simpleNextCell = (PrevCell MOD VLOOKUP(town, limit_table)) + 1
– Akina
Nov 21 at 17:20
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I'm not sure the title is clear, but essentially what I need is to automate the assignation of sites. The number of rows is usually around 60, but could go up to 200. The assignation of site number could vary sometimes from 5 through 10, depending on the site.
I have a reference tab showing, for example Delaware = 6, New York = 4.
So what I need is for column G to populate with numbers 1 through whatever the limit of the location is ("Delaware" or "New York" will be selected elsewhere in the file). So if it's Delaware, it would need to populate 1,2,3,4,5,6,1,2,3,4,5,6,and so on down to the last populated row. If New York, 1,2,3,4,1,2,3,4,1,2,3,4,and so on down to the last populated row.
I've found a number of VBA functions that will handle autopopulating to a specified number of rows, but I haven't seen anything that will populate a series up to N (where N=number assigned to the site in reference table) that may often change. Is this possible?
microsoft-excel vba microsoft-excel-2013
asked Nov 21 at 16:40
Lenny
11
New contributor
Lenny is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I'm not sure the title is clear, but essentially what I need is to automate the assignation of sites. The number of rows is usually around 60, but could go up to 200. The assignation of site number could vary sometimes from 5 through 10, depending on the site.
I have a reference tab showing, for example Delaware = 6, New York = 4.
So what I need is for column G to populate with numbers 1 through whatever the limit of the location is ("Delaware" or "New York" will be selected elsewhere in the file). So if it's Delaware, it would need to populate 1,2,3,4,5,6,1,2,3,4,5,6,and so on down to the last populated row. If New York, 1,2,3,4,1,2,3,4,1,2,3,4,and so on down to the last populated row.
I've found a number of VBA functions that will handle autopopulating to a specified number of rows, but I haven't seen anything that will populate a series up to N (where N=number assigned to the site in reference table) that may often change. Is this possible?
microsoft-excel vba microsoft-excel-2013
microsoft-excel vba microsoft-excel-2013
asked Nov 21 at 16:40
Lenny
11
New contributor
Lenny is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked Nov 21 at 16:40
Lenny
11
New contributor
Lenny is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked Nov 21 at 16:40
Lenny
11
New contributor
Lenny is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked Nov 21 at 16:40
Lenny
11
asked Nov 21 at 16:40
Lenny
11
11
New contributor
Lenny is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Lenny is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Lenny is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1
It would be helpful if you edit your question to include one of those VBA functions. If it gets close to what you want, it may just need some tweaking to work for you. If you don't know how to post the code, just paste it in and someone will format it for you.
– Rey Juna
Nov 21 at 17:08
2
It's simpleNextCell = (PrevCell MOD VLOOKUP(town, limit_table)) + 1
– Akina
Nov 21 at 17:20
add a comment |
1
It would be helpful if you edit your question to include one of those VBA functions. If it gets close to what you want, it may just need some tweaking to work for you. If you don't know how to post the code, just paste it in and someone will format it for you.
– Rey Juna
Nov 21 at 17:08
2
It's simpleNextCell = (PrevCell MOD VLOOKUP(town, limit_table)) + 1
– Akina
Nov 21 at 17:20
1
1
It would be helpful if you edit your question to include one of those VBA functions. If it gets close to what you want, it may just need some tweaking to work for you. If you don't know how to post the code, just paste it in and someone will format it for you.
– Rey Juna
Nov 21 at 17:08
It would be helpful if you edit your question to include one of those VBA functions. If it gets close to what you want, it may just need some tweaking to work for you. If you don't know how to post the code, just paste it in and someone will format it for you.
– Rey Juna
Nov 21 at 17:08
2
2
It's simple
NextCell = (PrevCell MOD VLOOKUP(town, limit_table)) + 1– Akina
Nov 21 at 17:20
It's simple
NextCell = (PrevCell MOD VLOOKUP(town, limit_table)) + 1– Akina
Nov 21 at 17:20
add a comment |
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
Lenny is a new contributor. Be nice, and check out our Code of Conduct.
Lenny is a new contributor. Be nice, and check out our Code of Conduct.
Lenny is a new contributor. Be nice, and check out our Code of Conduct.
Lenny is a new contributor. Be nice, and check out our Code of Conduct.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1377348%2fvba-filling-sequence-from-1-to-n%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
It would be helpful if you edit your question to include one of those VBA functions. If it gets close to what you want, it may just need some tweaking to work for you. If you don't know how to post the code, just paste it in and someone will format it for you.
– Rey Juna
Nov 21 at 17:08
2
It's simple
NextCell = (PrevCell MOD VLOOKUP(town, limit_table)) + 1– Akina
Nov 21 at 17:20