Find all records that match criteria shown in list box

**Josh_123456** · 12-27-2007, 03:52 PM

Is there anyway to have all records that match a criteria shown in a list box.

Here is my idea of what I think I need.

I have a textbox (TextBox1). A user will put in a search word and hit a command button

The procedure will

1. activate Sheet1 (has all the records)
2. Loop through all the records (row 1 - column b, until empty, no blank rows in middle)
3. Every record that has part of the search criteria in it (ex. Jones) would be able to be displayed in a listbox, with it showing the full name (column b) as well as column c. So the listbox would have a columncount of 2

My problem is I don't know if another sheet is needed to put all the criteria on the new sheet, and have the listbox just read from the new sheet.

Thanks for any help

Josh

**Charles** · 12-27-2007, 06:12 PM

Josh,

This code is simple.
See if this helps.

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim lrow As Long
Dim i As Integer
lrow = Sheets("Sheet1").Range("B65536").End(xlUp).Row
For i = 2 To lrow
    If Cells(i, 2).Text = UserForm1.TextBox1.Value Then
        UserForm1.ListBox1.AddItem Cells(i, 2) & "     " & Cells(i, 3)
    End If

Next i
End Sub

**Josh_123456** · 01-03-2008, 03:06 PM

Hi Charles,

Thank you so much for the code, I finally have had some time to test it and it works awesome. Just one question though????

I have noticed that it only finds the exact match, is there anyway to convert the code to a possible search loop and get all the matches that are (part) of the cell and not exact match. For instance when I type in the word Joe it would find all the Joe's in the database and all the Joelene's in the database and list in the listbox.

thanks for any help

Josh

**Charles** · 01-03-2008, 03:49 PM

Josh,

Try this find/findnext.

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim lrow As Long
Dim myname As String

With Worksheets(1).Range("B1:B500")
    myname = UserForm1.TextBox1.Text
    Set c = .Find(myname, LookIn:=xlValues)
    If Not c Is Nothing Then
        firstaddress = c.Address
        UserForm1.ListBox1.AddItem Cells(c.Row, 2) & "     " & Cells(c.Row, 3)
        Do
            Set c = .FindNext(c)
            If c.Address = firstaddress Then Exit Sub
            UserForm1.ListBox1.AddItem Cells(c.Row, 2) & "     " & Cells(c.Row, 3)
        Loop While Not c Is Nothing And c.Address <> firstaddress
    End If
End With
End Sub

**Josh_123456** · 01-03-2008, 04:35 PM

Thank you so much charles. The code works great so far.

Just one question about the variables, c and firstaddress weren't declared so I declared them as variant. Is there a better data type that I should declare them for speed and memory.

thanks

Josh

**Charles** · 01-03-2008, 05:43 PM

Josh,

If the database is not large then declare as variant should be ok.
However I think
firstaddress c.Address should be Range
c as string

delete "Dim lrow As Long" you do not need it.