Hello
I've scoured the internet trying to find a solution but havent been able to find a suitable solution. I found a search macro that almost suits what i need to do.
Currently the results are displayed in one column in a listbox. What i would like to happen is the complete row (columns A:F) displayed in 6 columns, with headers.
this is the macro i'm currently using to search
Private Sub CommandButton2_Click()
'SEARCH
StartRow = 2
Col = ComboBox1.ListIndex + 1
If Col = 0 Then
MsgBox "Please choose a category."
Exit Sub
End If
If TextBox1.Text = "" Then
MsgBox "Please enter a search term."
TextBox1.SetFocus
Exit Sub
End If
LastRow = Cells(Rows.Count, Col).End(xlUp).Row
LastRow = IIf(LastRow < StartRow, StartRow, LastRow)
Set Rng = Range(Cells(2, Col), Cells(LastRow, Col))
Set FoundMatch = Rng.Find(What:=TextBox1.Text, _
After:=Rng.Cells(1, 1), _
LookAt:=CInt(CheckBox2.Tag), _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=CheckBox1.Value)
If Not FoundMatch Is Nothing Then
FirstAddx = FoundMatch.Address
ListBox1.Clear
Do
Cnt = Cnt + 1
R = FoundMatch.Row
ListBox1.AddItem "Row " & R
For Each C In Range(Cells(R, "A"), Cells(R, "F"))
ListBox1.AddItem C.Text
Next C
Set FoundMatch = Rng.FindNext(FoundMatch)
A = FoundMatch.Address
Loop While CheckBox3 = True And FoundMatch.Address <> FirstAddx And Not FoundMatch Is Nothing
Label1.Caption = "Matches =" & Str(Cnt)
SearchRecords = Cnt
Else
ListBox1.Clear
Label1.Caption = ""
SearchRecords = 0
MsgBox "No match found for " & TextBox1.Text
End If
End Sub
Hope i'm making sense, any help would be great
thanks Goffa
Bookmarks