Excel VBA 循环遍历列表框
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40611443/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA loop through listbox
提问by Noob2Java
I have this code that I am using to search a range when I click the item in my listbox. I have never looped through a listbox and want to know how I add a loop to perform what I need without clicking each item in the listbox. Here is the code I am using:
当我单击列表框中的项目时，我有此代码用于搜索范围。我从来没有遍历过列表框，想知道如何添加一个循环来执行我需要的操作，而无需单击列表框中的每个项目。这是我正在使用的代码：
Sub FindListValue()
Dim FirstAddress As String
Dim rSearch As Range 'range to search
Dim c As Range
With Sheets("PN-BINS")
Set rSearch = .Range("B1", .Range("B65536").End(xlUp))
End With
Dim i As Long
' loop through all items in ListBox1
For i = 0 To Me.ListBox1.ListCount - 1
' current string to search for
strFind = Me.ListBox1.List(i)
With rSearch
Set c = .Find(strFind, LookIn:=xlValues, LookAt:=xlWhole)
If Not c Is Nothing Then 'found it
c.Select
Me.ListBox1.AddItem strFind & " | " & c.Offset(0, -1).Value, Me.ListBox1.ListIndex + 1
Me.ListBox1.RemoveItem (Me.ListBox1.ListIndex)
'Exit Sub
Else: 'MsgBox strFind & " is not listed!" 'search failed
End If
End With
' the rest of your code logics goes here...
Next i
End Sub
回答by Shai Rado
In order to loop through all items in the ListBox1, use the following loop:
为了遍历 中的所有项目ListBox1，请使用以下循环：
Dim i As Long
' loop through all items in ListBox1
For i = 0 To Me.ListBox1.ListCount - 1
' current string to search for
strFind = Me.ListBox1.List(i)
' the rest of your code logics goes here...
Next i
B.T.W , it's better if you define your rSearchrange in the following way (without using Activateand ActiveSheet)
顺便说一句，如果你rSearch用以下方式定义你的范围会更好（不使用Activateand ActiveSheet）
With Sheets("PN-BINS")
Set rSearch = .Range("B1", .Range("B65536").End(xlUp))
End With
Edit 1: Whole code
编辑1：整个代码
Sub FindListValue()
Dim FirstAddress As String
Dim rSearch As Range 'range to search
Dim c As Range
Dim i As Long
With Sheets("PN-BINS")
Set rSearch = .Range("B1", .Range("B65536").End(xlUp))
End With
' loop through all items in ListBox1
For i = 0 To Me.ListBox1.ListCount - 1
strFind = Me.ListBox1.List(i) ' string to look for
Set c = rSearch.Find(strFind, LookIn:=xlValues, LookAt:=xlWhole)
' current ListBox1 item is found
If Not c Is Nothing Then
Me.ListBox1.AddItem strFind & " | " & c.Offset(0, -1).Value, i + 1
Me.ListBox1.RemoveItem (i)
' ****** not sure if you want to use the line below ? ******
Exit Sub
Else
MsgBox strFind & " is not listed!" 'search failed
End If
Next i
End Sub
