Search all records in 3 columns via a userform and display the results in my listview

I have been able to, with the kind help and assistance of various resources in this forum, managed to create a userform that will search selected columns in my workbook via a userform and display the results in a listview.

I am trying to amend this so that I can search through all columns and display the results on my userform listview and not having to select a specific column.

I attach my workbook to where I have managed to get upto so far and am asking whether anyone knows how I can amend the code so that the searcg works for all columns, not just the one that I select via my combobox.

Please can anyone assist me with any help or guidance?

Many thanks for taking the time to read my post, it is much appreciated.

Hi pells

This has been tested ONLY perfunctorily. Let me know of issues.

Hi pells
Sorry, I misunderstood. I didn't clean the code up but something like this may work for you

Private Sub CommandButton1_Click()
'SEARCH
    Dim Cnt As Long
    Dim Col As Variant
    Dim FirstAddx As String
    Dim FoundMatch As Range
    Dim LastRow As Long
    Dim R As Long
    Dim StartRow As Long
    Dim Wks As Worksheet
    Dim rng As Range
    Dim C As Range
    Dim SearchRecords As Long
    Dim i As Long
    Dim colCnt As Long

    StartRow = 4

    If TextBox1.Text = "" Then
        MsgBox "Please enter a search term."
        TextBox1.SetFocus
        Exit Sub
    End If

    Set Wks = ActiveWorkbook.Sheets("Live Job List")
    colCnt = Wks.Cells(4, Columns.Count).End(xlToLeft).Column
    For i = 1 To colCnt
        Col = i
        LastRow = Wks.Cells(Rows.Count, Col).End(xlUp).Row
        LastRow = IIf(LastRow < StartRow, StartRow, LastRow)

        Set rng = Wks.Range(Wks.Cells(1, Col), Wks.Cells(LastRow, Col))

        Set FoundMatch = rng.Find(What:=TextBox1.Text, _
                After:=rng.Cells(1, 1), _
                LookAt:=xlPart, _
                LookIn:=xlValues, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, _
                MatchCase:=False)

        If Not FoundMatch Is Nothing Then
            FirstAddx = FoundMatch.Address
            ListView1.ListItems.Clear

            Do
                Cnt = Cnt + 1
                R = FoundMatch.Row

                ListView1.ListItems.Add Index:=Cnt, Text:=Wks.Cells(R, 1)
                For Col = 2 To 3
                    Set C = Wks.Cells(R, Col)
                    ListView1.ListItems(Cnt).ListSubItems.Add Index:=Col - 1, Text:=C.Text

                Next Col

                Set FoundMatch = rng.FindNext(FoundMatch)
            Loop While FoundMatch.Address <> FirstAddx And Not FoundMatch Is Nothing
            SearchRecords = Cnt

        Else
            '             ListView1.ListItems.Clear
            '             SearchRecords = 0
            '             MsgBox "No match found for " & TextBox1.Text
        End If
    Next i
End Sub

Hi pells
change this line of code

Set FoundMatch = rng.Find(What:=TextBox1.Text, _
                After:=rng.Cells(1, 1), _
                LookAt:=xlWhole, _
                LookIn:=xlValues, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, _
                MatchCase:=False)

Hi pells
If you comment out this line of code (actually I'd move it outside the For...Next loop)

If Not FoundMatch Is Nothing Then
            FirstAddx = FoundMatch.Address
'            ListView1.ListItems.Clear

and change xlWhole back to xlPart you'll get the results you expect searching on "SM" BUT you'll get unwanted results when you search on "1"

Hi pells...perhaps I assumed too much. Searching on "1" also pulls in the "10"s but that may well be what you were looking for.

Hi pells...here's an image of what I get searching on "1"
screen shot.jpg

Hi pells
Your searching on xlPart so, the procedure finds the "1"s in column 1 and also finds the "1"s in column 2 (the digit 1 in job 1 and the digit 1 in job 10) xlPart...understand?

Hi pells

You're welcome...glad I could help.