How would I create a VB Macro to loop through several dropdown lists and then add all the values to cells on another worksheet?
I would like to automatically generate the various combinations for the values in a group of 10 dropdown lists, so when it reaches the end of the last dropdown list I need it to advance to the next option in the previous list and then restart the loop in all subsequent dropdowns (i.e. when it reaches the last option in list #10, it advances list #9 to the next option and resets list #10 to the 1st option.
All values are text. I am using Defined Names in the Data Validation Lists, but I’m trying to avoid having to use Table Names since there are 500+ tables.
I’ve done this in Javascript (with nested lists) but I’m having some difficulty figuring out the proper way to do this in Excel and VB. I haven’t found any information on how to loop through several dropdown lists in this manner, and though I’ve found a few postings with the code to loop though a single dropdown list (i.e. Loop through each row of a range in Excel - Stack Overflow) I've had trouble finding clear explanation or tutorials so that I could understand and adapt the code.
I would like to automatically generate the various combinations for the values in a group of 10 dropdown lists, so when it reaches the end of the last dropdown list I need it to advance to the next option in the previous list and then restart the loop in all subsequent dropdowns (i.e. when it reaches the last option in list #10, it advances list #9 to the next option and resets list #10 to the 1st option.
All values are text. I am using Defined Names in the Data Validation Lists, but I’m trying to avoid having to use Table Names since there are 500+ tables.
I’ve done this in Javascript (with nested lists) but I’m having some difficulty figuring out the proper way to do this in Excel and VB. I haven’t found any information on how to loop through several dropdown lists in this manner, and though I’ve found a few postings with the code to loop though a single dropdown list (i.e. Loop through each row of a range in Excel - Stack Overflow) I've had trouble finding clear explanation or tutorials so that I could understand and adapt the code.
