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
12
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
12
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
12
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
12
asked Nov 21 at 16:40
Lenny
12
asked Nov 21 at 16:40
Lenny
12
asked Nov 21 at 16:40
Lenny
12
asked Nov 21 at 16:40
Lenny
12
12
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
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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