Excel VBA loop through listbox

Question

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

I'm not sure I understand the question. You've selected something from your list box and you want to use that value to find something in a range (which you can do with either a loop or the `Find` method) - so where does clicking through each item in the list box come into it? — jsheeran, Nov 15 '16 at 14:09
When I click the item in the listbox I am replacing the line with the searched information from the range which works just fine if I click each item. What I want to do is eliminate the need to click the items in the listbox all together and just search every line in the listbox. If its found, replace the line. If it's not found then just do nothing. I hope I am making sense. — Noob2Java, Nov 15 '16 at 14:17
@user3340949 try the code in my answer below and see if it works for you — Shai Rado, Nov 15 '16 at 14:46

Shai Rado · Accepted Answer · 2016-11-15T15:04:28.367

In order to loop through all items in the ListBox1, use the following loop:

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 rSearch range in the following way (without using Activate and ActiveSheet)

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

Edit 1: Whole code

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

Thanks for your help. I edited my question with the changes I just made based on your suggestion. I get an error on 'c.select'. Did I not put the code in correctly? — Noob2Java, Nov 15 '16 at 14:54
@user3340949 you are not suppose to use the code you get in your answers inside your post, by this you are chaniging your post. You are suppose to either accept the answer you get and mark a "v" next to them, or (if they don't work) comment where they still give you an error. Anyway, there is no need to use this line `c.Select`, you can just remove it — Shai Rado, Nov 15 '16 at 14:56
thanks for the help, the code works great. Thanks for the information on the post editing, I will make sure and not do that in the future. — Noob2Java, Nov 15 '16 at 16:08

Excel VBA loop through listbox

1 Answers1

Linked