通过VBA下拉列表迭代 [英] Iterate through VBA dropdown list

问题描述

我有三个下拉列表验证列表，我正在尝试编写一些代码，遍历所有的可用的管理方法排列。

我可以找出迭代一个列表的第一步（例如

这是在裁剪类型下拉列表中循环，但返回虫害和管理方法也一样。我用于创建下拉列表的代码如下：

  Option Explicit 
 
 Private Sub Worksheet_Change （ByVal Target As Range）
 
如果Target.Cells.Count> 1然后退出Sub 
 
如果不相交（目标，范围（C6））没有，然后
范围（D6：E6）。ClearContents 
 End If 
 
 End Sub 
  

解决方案

最简单的方法执行循环遍历每一个可能的组合是使用 For Each 循环。您可以找到有关他们的更多信息 here 和此处和这里和这里 ....... !!! Etcetera ....

这将循环遍历3个下拉列表的每个组合。您将需要更改代码中的下拉列表位置。

  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
 
'************************* ************ 
'*********************************** ** 
 
设置Range1 = Evaluate（Range（Dropdown1）.Validation.Formula1）
设置Range2 = Evaluate（Range（Dropdown2）.Validation.Formula1）
设置Range3 =评估（Range（Dropdown3）.Validation.Formula1）
 
对于每个选项1在范围1 
对于每个选项2在范围2 
对于每个选项3在范围3 
 
' *** PERFORM CODE HERE *** 
'示例
'表（1）.Cells（1，1）= option1 
'Sh eets（1）.Cells（2，1）= option2 
'etc ... 
 
下一个选项3 
下一个选项2 
下一个选项1 
 
 
 End Sub 
  

编辑：

  Sub LoopThroughList（）
 Dim Dropdown1，Dropdown2，Dropdown3 As String 
 Dim Range1，Range2，Range3 As Range 
 Dim option1，option2，选项3作为范围
 
 Dim Counter As Long 
 
计数器= 1 
 
'*** SET DROPDOWN位置在这里*** 
' *********************************** 
 
 Dropdown1 =C6
 Dropdown2 =D6
 Dropdown3 =E6
 
'*************************** ********** 
'************************************* 
 
设置Range1 = Evaluate（Range（Dropdown1）.Validation.Formula1）
设置Range2 = Evaluate（Range（Dropdown2）.Validation.Formula1）
设置Range3 = Evaluate （Dropdown3）.Validation.Formula1）
 
对于每个选项1在范围1 
对于每个选项2在范围2 
 F或每个选项3在范围3 
 
表（2）.Cells（Counter，1）= option1 
表格（2）.Cells（Counter，2）= option2 
表格（2 ）.Cells（Counter，3）= option3 
 Counter = Counter + 1 
 
下一个选项3 
下一个选项2 
下一个选项1 
 
 
 End Sub 
  

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

解决方案

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

这篇关于通过VBA下拉列表迭代的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！