I have a database with 5 columns & 1232 rows, I want to do a search on columns A & B on 1232 rows. but the formula I am using does not return all the search results. the formula is as below. can you please help.


Private Sub CommandButton3_Click()
'search for searchText and populate list box with all data found
Dim searchText As String, FirstAddr As String
Dim FoundCell As Range, LastCell As Range, searchRange As Range
Dim i As Long, endRow As Long
Dim foundTarget As Boolean

searchText = TextBox6
If Len(searchText) = 0 Then Exit Sub

Application.ScreenUpdating = False
Range("A1").End(xlDown).Select
endRow = ActiveCell.Row
Range("A1").Select
Application.ScreenUpdating = True

Set searchRange = Range("A3:E1232" & endRow)

Me!ListBox1.Clear
foundTarget = True

With searchRange
Set LastCell = .Cells(.Cells.Count)
End With

Set FoundCell = searchRange.Find(what:=searchText, after:=LastCell)

If Not FoundCell Is Nothing Then
FirstAddr = FoundCell.Address
Else
foundTarget = False
End If

i = 0
Do Until FoundCell Is Nothing

Me!ListBox1.AddItem Cells(FoundCell.Row, 1).Value
Me!ListBox1.List(i, 1) = Cells(FoundCell.Row, 2).Value
Me!ListBox1.List(i, 2) = Cells(FoundCell.Row, 3).Value
Me!ListBox1.List(i, 3) = Cells(FoundCell.Row, 4).Value
Me!ListBox1.List(i, 4) = Cells(FoundCell.Row, 5).Value
Me!ListBox1.List(i, 5) = _
Format(Cells(FoundCell.Row, 4).Value, "$#,##0.00")

Set FoundCell = searchRange.FindNext(after:=FoundCell)
If FoundCell.Address = FirstAddr Then
Exit Do
End If
i = i + 1

Loop

If Not foundTarget Then
MsgBox "No data found for " & searchText
Else

End If

Me!ListBox1.SetFocus
End Sub