Iterate through VBA dropdown list

Question

I have three dropdown validation lists and I am trying to write some code that iterates through all of the "Management Methods" permutations that are available.

I can figure out the first step of iterating through one list (for example Iterate through an Excel dropdown/validation list and others) but I can't work out how to go through three of them.

Ideally, I would like this to be written in a way that works even if you add more options.

To achieve this I figure you want a way of counting how many options there are in each list and then iterating through from 0-n.

Any help would be much appreciated.

Sub LoopThroughList() Dim Dropdown1, Dropdown2, Dropdown3 As String Dim Range1, Range2, Range3 As Range Dim option1, option2, option3 As Range ' *** SET DROPDOWN LOCATIONS HERE *** ' *********************************** Dropdown1 = "C6" Dropdown2 = "D6" Dropdown3 = "E6" ' *********************************** ' *********************************** Set Range1 = Evaluate(Range(Dropdown1).Validation.Formula1) Set Range2 = Evaluate(Range(Dropdown2).Validation.Formula1) Set Range3 = Evaluate(Range(Dropdown3).Validation.Formula1) For Each option1 In Range1 For Each option2 In Range2 For Each option3 In Range3 Worksheets("Sheet1").Range("C6:E6").Copy With Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1) .PasteSpecial Paste:=xlPasteColumnWidths .PasteSpecial Paste:=xlPasteValues End With Next option3 Next option2 Next option1 End Sub

At the moment I get this:

This is iterating through the crop type dropdown but returning the same for pest and management methods. The code I used to create the dropdown list is as below:

Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Not Intersect(Target, Range("C6")) Is Nothing Then Range("D6:E6").ClearContents End If End Sub

Brendan Gooden · Accepted Answer · 2016-04-04 10:18:16Z

The easiest way to perform loops that will iterate through every possible combination is to use a For Each loop. You can find more info about them here and here and here and here....... !!! Etcetera....

This will loop through every combination of 3 dropdown lists. You will need to change the location of the dropdowns in my code.

Sub LoopThroughList() Dim Dropdown1, Dropdown2, Dropdown3 As String Dim Range1, Range2, Range3 As Range Dim option1, option2, option3 As Range ' *** SET DROPDOWN LOCATIONS HERE *** ' *********************************** Dropdown1 = "D8" Dropdown2 = "E8" Dropdown3 = "F8" ' *********************************** ' *********************************** Set Range1 = Evaluate(Range(Dropdown1).Validation.Formula1) Set Range2 = Evaluate(Range(Dropdown2).Validation.Formula1) Set Range3 = Evaluate(Range(Dropdown3).Validation.Formula1) For Each option1 In Range1 For Each option2 In Range2 For Each option3 In Range3 ' *** PERFORM CODE HERE *** ' EXAMPLE ' Sheets(1).Cells(1, 1) = option1 ' Sheets(1).Cells(2, 1) = option2 ' etc... Next option3 Next option2 Next option1 End Sub

EDIT:

Sub LoopThroughList() Dim Dropdown1, Dropdown2, Dropdown3 As String Dim Range1, Range2, Range3 As Range Dim option1, option2, option3 As Range Dim Counter As Long Counter = 1 ' *** SET DROPDOWN LOCATIONS HERE *** ' *********************************** Dropdown1 = "C6" Dropdown2 = "D6" Dropdown3 = "E6" ' *********************************** ' *********************************** Set Range1 = Evaluate(Range(Dropdown1).Validation.Formula1) Set Range2 = Evaluate(Range(Dropdown2).Validation.Formula1) Set Range3 = Evaluate(Range(Dropdown3).Validation.Formula1) For Each option1 In Range1 For Each option2 In Range2 For Each option3 In Range3 Sheets(2).Cells(Counter, 1) = option1 Sheets(2).Cells(Counter, 2) = option2 Sheets(2).Cells(Counter, 3) = option3 Counter = Counter + 1 Next option3 Next option2 Next option1 End Sub

Thank you, I have tried to use this in my code (see the edit in my post) but it doesn't seem to be getting past the first option.
If you are trying to Get a list of all the possible combinations, try my second code above
Sorry I have been spending some more time on this and it actually only iterates through the first dropdown list but returns the same for the second and third: in my answer I have included and example of what it produces.

Collectives™ on Stack Overflow

Iterate through VBA dropdown list

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related