遍历 VBA 下拉列表 [英] Iterate through VBA dropdown list
问题描述
我有三个下拉验证列表,我正在尝试编写一些代码来遍历所有可用的管理方法"排列.
我可以找出遍历一个列表的第一步(例如
这是遍历作物类型下拉列表,但返回相同的害虫和管理方法.我用来创建下拉列表的代码如下:
选项显式Private Sub Worksheet_Change(ByVal Target As Range)如果 Target.Cells.Count >1 然后退出子如果不相交(目标,范围(C6"))什么都没有范围(D6:E6").清除内容万一结束子
执行将遍历所有可能组合的循环的最简单方法是使用 For Each
循环.您可以在此处和此处 和 这里 和 这里....... !!!等等....
这将遍历 3 个下拉列表的每个组合.您需要在我的代码中更改下拉菜单的位置.
Sub LoopThroughList()Dim Dropdown1、Dropdown2、Dropdown3 作为字符串将范围 1、范围 2、范围 3 调暗为范围将选项 1、选项 2、选项 3 调暗为范围' *** 在此处设置下拉位置 ***' ***************************************Dropdown1 =D8"Dropdown2 =E8"Dropdown3 = "F8"' ***************************************' ***************************************设置 Range1 = Evaluate(Range(Dropdown1).Validation.Formula1)设置 Range2 = Evaluate(Range(Dropdown2).Validation.Formula1)设置 Range3 = Evaluate(Range(Dropdown3).Validation.Formula1)对于范围 1 中的每个选项 1对于 Range2 中的每个 option2对于 Range3 中的每个 option3' *** 在这里执行代码 ***' 例子' Sheets(1).Cells(1, 1) = option1' Sheets(1).Cells(2, 1) = option2' 等等...下一个选项3下一个选项 2下一个选项 1结束子
Sub LoopThroughList()Dim Dropdown1、Dropdown2、Dropdown3 作为字符串将范围 1、范围 2、范围 3 调暗为范围将选项 1、选项 2、选项 3 调暗为范围Dim 计数器只要计数器 = 1' *** 在此处设置下拉位置 ***' ***************************************Dropdown1 = "C6"Dropdown2 =D6"Dropdown3 = "E6"' ***************************************' ***************************************设置 Range1 = Evaluate(Range(Dropdown1).Validation.Formula1)设置 Range2 = Evaluate(Range(Dropdown2).Validation.Formula1)设置 Range3 = Evaluate(Range(Dropdown3).Validation.Formula1)对于范围 1 中的每个选项 1对于 Range2 中的每个 option2对于 Range3 中的每个 option3Sheets(2).Cells(Counter, 1) = option1Sheets(2).Cells(Counter, 2) = option2Sheets(2).Cells(Counter, 3) = option3计数器 = 计数器 + 1下一个选项3下一个选项 2下一个选项 1结束子
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屋!