Excel VBA循环遍历列表框

Question

当我单击列表框中的项目时，我有此代码用于搜索范围。我从来没有循环过一个列表框，我想知道如何添加一个循环来执行我需要的操作，而无需单击列表框中的每个项目。这是我正在使用的代码:

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

Answer 1

为了遍历 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 ，最好定义你的 rSearch范围如下(不使用 Activate 和 ActiveSheet )

With Sheets("PN-BINS")
    Set rSearch = .Range("B1", .Range("B65536").End(xlUp))
End With

编辑 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