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?

  • 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 '18 at 17:08
  • 2
    It's simple NextCell = (PrevCell MOD VLOOKUP(town, limit_table)) + 1 – Akina Nov 21 '18 at 17:20

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.