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
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