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
– jsheeran, Commented Nov 15, 2016 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
– Noob2Java, Commented Nov 15, 2016 at 14:17
@user3340949 try the code in my answer below and see if it works for you — Shai Rado
– Shai Rado, Commented Nov 15, 2016 at 14:46

Shai Rado · Accepted Answer · 2016-11-15 15:04:28Z

4

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

edited Nov 15, 2016 at 15:04

answered Nov 15, 2016 at 14:22

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Noob2Java Over a year ago

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?

Shai Rado Over a year ago

@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

Noob2Java Over a year ago

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.

Collectives™ on Stack Overflow

Excel VBA loop through listbox

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest