Excel Excel userform

Joined
Feb 11, 2012
Messages
1
Reaction score
0
I want the data below to look for a searh word and return the compete row of found data ie A6,B6,C6,D6,E6, but it only returns info in colum A. Can you help THANKS.
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:E1228" & 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
 
Back
Top