In Excel, I want to iterate through two drop down lists to run all possible combinations using VBA

Question

My goal is to have an Excel Macro. In Excel, I want to iterate through two drop down lists to run all possible combinations using VBA.
The problem is that the drop down lists must be manually iterated through.
The second drop down list is dependent on the first drop down list. The first drop down list is 3 types of vehicles: Ford, Toyota, and Mazda. The second drop down list is the repair site: Ford Dealership, Toyota Dealership, Mazda Dealership, General Repair Shop A, and General Repair Shop B. The second list dependency is that the Ford Dealership can only repair Fords; the Toyota Dealership can only repair Toyotas. The Mazda Dealership can only repair Mazdas. General Repair Shop A and General Repair Shop B can repair any type of vehicle. The actual problem has a much larger data set and I must use Excel to solve the problem.

I tried using two four loops. But there are many combinations that I don't want to occur.

sub test()
Dim i as Integer
Dim j as Integer
For i =1 to 3
Next i
For j=1 to 5
Next j
End Sub()

You can also follow this [link](https://stackoverflow.com/questions/36398323/iterate-through-vba-dropdown-list) for more tips. — Justyna MK, Sep 15 '19 at 11:18
Another option, depending on what you want to do with this information, would be to create dependent dropdown lists. — Ron Rosenfeld, Sep 15 '19 at 11:42

score 0 · Answer 1 · answered Sep 15 '19 at 15:02

Assuming that the dropdowns are form-controls, the following does what you want.

Sub AutoIterate()
Dim i As Long, j As Long
Dim cmb1 As ControlFormat, cmb2 As ControlFormat
    Set cmb1 = ActiveSheet.Shapes("Drop Down 1").ControlFormat 'change the worksheet and the combobox name to those that fit for you.
    Set cmb2 = ActiveSheet.Shapes("Drop Down 2").ControlFormat
    For i = 1 To cmb1.ListCount
        For j = 1 To cmb2.ListCount
            If (cmb2.List(j) Like "*" & cmb1.List(i) & "*") Or (cmb2.List(j) Like "*General*") Then
                Debug.Print cmb1.List(i) & " - " & cmb2.List(j)
            End If
        Next
    Next
    Set cmb1 = Nothing
    Set cmb2 = Nothing
End Sub

Keep in mind that if:

every official shop in cmb2 includes the firm exactly as it is in cmb1 (case sensitive),
every general shop in cmb2 has the word "General" (case sensitive),

then this will also do the job for a larger set of data.

Otherwise just adjust the If statement accordingly.

In Excel, I want to iterate through two drop down lists to run all possible combinations using VBA

1 Answers1