PDA

View Full Version : For each loop with data validation lists



waimea
07-03-2018, 02:31 PM
I am trying to do a for each loop with data validation lists but the macro recorder only gives me a range when I select the data validation drop downs.

This makes me uncertain on where to start. I would like to loop through 4 data validation lists, 2 pairs of identical values and I am looking at all possible combinations between the four.

I would like help with how to check if there are data validation drop downs in my worksheet, if yes, loop through all possible values in the drop downs and count a number in every loop and save the number and the combination make in a worksheet.

Any suggestions are very welcome!

Thank you in advance!

waimea
07-04-2018, 03:33 AM
Sub LoopThroughList()
Dim Dropdown1, Dropdown2, Dropdown3, Dropdown4 As String
Dim Range1, Range2, Range3, Range4 As Range
Dim option1, option2, option3 As Range


Dim Counter As Long


Counter = 1


Set Range1 = Evaluate(Range("B3").Validation.Formula1)
Set Range2 = Evaluate(Range("H3").Validation.Formula1)
Set Range3 = Evaluate(Range("B9").Validation.Formula1)
Set Range4 = Evaluate(Range("H9").Validation.Formula1)


For Each option1 In Range1
For Each option2 In Range2
For Each option3 In Range3
For Each option4 In Range4
Sheets(2).Cells(Counter, 1) = option1
Sheets(2).Cells(Counter, 2) = option2
Sheets(2).Cells(Counter, 3) = option3
Sheets(2).Cells(Counter, 4) = option4
Counter = Counter + 1
Debug.Print option1, option2, option3, option4
'Debug.Print option1, option2, option3
Next option4
Next option3
Next option2
Next option1

End Sub



This code does what I want. How can I improve it?

Instead of Debug.Print I would like to do a vlookup in VBA?

waimea
07-04-2018, 03:59 AM
I am using a dependent drop down list for two of my four data validation drop downs, I get a type mismatch because I am using INDEX and MATCH in the dependant drop down.

How can I change the code above to account for this?

waimea
07-05-2018, 09:13 AM
I have crossposted this question on https://stackoverflow.com/questions/51170356/for-each-loop-with-data-validation-lists and on https://www.mrexcel.com/https://www.excelforum.com/excel-programming-vba-macros/1236590-loop-through-a-data-validations-list-and-for-each-loop-count-the-number-of-occurences.html#post4929352orum/excel-questions/1061786-each-loop-data-validation-lists.html