For each loop with data validation lists

[waimea]

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]

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]

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]

I have crossposted this question on https://stackoverflow.com/questions/...lidation-lists and on https://www.mrexcel.com/https://www....ion-lists.html