Search sheet by inputbox entry from userform, multiple results

Hi everyone,

I was wondering whether anyone could please help me refine this code?

I'm probably on the wrong track, as I have no training, but I've managed to cobble together something that works for me - so far... And of course, predictably, I've hit a snag...

What i'm trying to do is to a sort of vlookup via an input box which launches from a userform. There is a worksheet entitled "JobsList" which has the "Matter Number" in Column A, and the "Client's Name" in Column B". I want the user to be able to click the "Find" button, enter a name, and have the matter number returned in a message box.

So far i've got this - which, as long as I enter the "Client's Name" in exactly the same format, returns the "Matter Number" - yay!

Private Sub Find_Click()

Dim xlSheet As Worksheet
Dim lastRow As Integer
Dim i As Long
Dim fName As String
    
    Set xlSheet = ActiveWorkbook.Sheets("JobsList")
    
    fName = Application.InputBox("Please enter a name to search", Title:="Find Matter Number")
        
    Select Case WorksheetFunction.CountIf(xlSheet.Range("B:B"), fName)
            Case 0
               fName = 0
               MsgBox ("There are no files with this name")
            Case 1
                lastRow = xlSheet.Range("B" & xlSheet.Rows.Count).End(-4162).Row
                For i = 1 To lastRow
                    If InStr(1, xlSheet.Range("B" & i), fName) > 0 Then
                        MsgBox (xlSheet.Range("A" & i))
                        Exit For
                    End If
                Next i

    End Select

End Sub

But, the user might not know the exact format to search (e.g. at present, if they search "Smith, John" it will return the matter number). This is not particularly useful. I hope to find a way so that if they typed "Smith" they will get all rows which contain "Smith" in column B. I realise that my code as set out above has no hope of being able to do this - but I have no idea how to get it to do so!

Similarly, there might be more than one "Smith" in column B. I hope to be able to find a way to return a MsgBox with all applicable results listed so that they can chose the correct one. Is it even possible to return a MsgBox with multiple results? If not, the loop should work, as they could just "Ok" through the "Smith" entries until they find the one they are looking for. The latter is not ideal, but if that's the way we have to do it, that's fine.

My googling has not produced any results - but as you can probably tell from the above, i'm pretty confused and have probably not been searching the right terms. Any tips or pointers would be gratefully appreciated.

Thank you in advance.

Thank you for your quick reply!

This feature is an afterthought - the form is built around the user entering a matter number into a textbox on the userform, which then populates results in captions on the userform (much as you suggested) - and that works well. The code I posted comes from me trying to reverse engineer that process...

Users have suggested to me that they don't always have the matter number to hand, so they'd like to be able to search via the client's name instead. Hence my inputbox/message box idea. At present, they are simply exiting out of the userform, Ctrl+F on the JobsList worksheet and then reloading the userform when they've found the matter number (so it made sense to me that I should give them the option of doing that from within the userform).

I've been working on this project for 12+ months now, and I am constantly refining it. I'm struggling to remember, but I think that I tried the ComboBox idea earlier but i couldn't get it to show multiple columns so that the user could identify the details in order to chose the correct entry. Perhaps i gave up too soon, but then again, there are other functions in the project which draw on the information it draws from entering the matter number and I think it would mean a rather large rewrite if i went back now (I'd rather just tell them they have to search manually!).

Hi skbreen,

See the attachment to my post. I quickly mocked up a spreadsheet with a structure that I believe mimics yours. Click on the "Show Form" button to populate the userform, then search for a person's name. It should list out all occurrences of the name. Afterwards, if you click on a single name, then click the button with the double arrows >>, it will select that person in the spreadsheet.

Let me know if this is, essentially, what you are looking for. Also, let me know if you can work this into your existing spreadsheet or if you need assistance.

@BigBas - Yes, Yes, Yes! That's it!

Thank you!!!!!!

I'll try and adapt it into my project and let you know how I go!

Ok, let me know what you come up with. I will say, I threw it together relatively quickly, so it may seem somewhat rudimentary. I didn't put much thought into design (lining things up, tab order, etc), nor did I do much error checking. Try to design it better to make it somewhat presentable. Also, throw some "curveballs" at it while testing to see if there are any situations that cause it to error out or not return the appropriate results.

Thank you to BigBas for his great response!

I managed to get it to work for my project with some very minor changes. I'm running it from a second userform that loads from the first, and then plugs the details I need back into the first userform (so i had to hide rather than unload and return .text rather than the .value).

I added a third column to the Results.List and I added a Close button so the user can back out without selecting anything. I've also added another pop up to advise the user if no results were found.

I've put it through a bit of testing and it seems to stand up (I've had it return up to 37 matches from over 600 possible results without obvious error). This is my final code:

Private Sub SearchBTN_Click()
    Dim ws As Worksheet
    Dim rng As Range
    Dim tSrch As String
    Dim cnt As Long
    Dim cel As Range
    
    Set ws = Worksheets("JobsList")
    Set rng = Intersect(ws.Range("A1").CurrentRegion, ws.Columns(2))
    
    Me.Results.Clear
    tSrch = CStr(Me.Search.Value)
    
    cnt = WorksheetFunction.CountIf(rng, "*" & tSrch & "*")
    
    If cnt > 0 Then
        Me.Results.Visible = True
    Else
        MsgBox ("No results found.")
    End If
    
    t = 0
    
    For Each cel In rng
        If UCase(cel.Value) Like "*" & UCase(tSrch) & "*" Then
            Me.Results.AddItem
            Me.Results.List(t, 1) = cel.Offset(, -1).Value
            Me.Results.List(t, 2) = cel.Value
            Me.Results.List(t, 3) = cel.Offset(, 1).Value
            Me.Results.List(t, 0) = cel.Address
            t = t + 1
        End If
    Next cel
        
End Sub

Private Sub SelectBTN_Click()

    UserForm1.MatterNo.Text = Me.Results.Text
    UserForm6.Hide
    UserForm1.Solicitor.SetFocus
    
End Sub

Private Sub CloseBTN_Click()

UserForm6.Hide

End Sub

Thanks again

Spectacular! Glad it all worked out.