VBA: Range.Find returning multiple results

[Bostockm]

Hi,

I'm working on a project where I need to lookup a name from a list, and then navigate to a worksheet with the same name.

I've got this working fine, however when there's multiple matches to my search, Range.Find just finds the first instance and keeps going.

So for example, I could have a list of names:

Jack Smith
Rob Jones
Harry Clark
Steve Smith

And when searching 'Smith', the code just finds the first person with Smith in the name, and loads their worksheet.

Here's what I'm using so far:

Dim SearchQuery As Range
Set SearchQuery = Range("B7")
[COLOR=#000000][FONT=Verdana]Dim FindString As String
Dim Rng As Range
FindString = SearchQuery
If Trim(FindString) <> "" Then
With Sheets("Users").Range("A:G")
Set Rng = .Find(What:=FindString, _
After:=.Cells(.Cells.count), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
Application.Goto Rng, True

Dim UserID As Range
Set UserID = Range("A" & (ActiveCell.Row))

Dim UserSheet As String
UserSheet = UserID

If UserSheet <> vbNullString Then
On Error Resume Next
Sheets(UserSheet).Activate
If Err.Number <> 0 Then MsgBox "Failed"
On Error GoTo 0
End If

Else 'Does this if the user can't be found
MsgBox "User Not Found"
Exit Sub
End If[/FONT][/COLOR]

What would be great is if a pop-up box could appear, listing all of the options that are found, and then the user can click on one to indicate that's who they want to open.

As always, any help greatly appreciated.

Many thanks.

 

[NoSparks]

This should help assemble all found names into a string variable called allfinds that you can work with.
If you create a user form with a listbox, you can display the user form and populate the listbox something like:

in a standard module

Option Explicit

    Public allFinds As String

Sub testing()
    Dim FindString As String
    Dim fndRng As Range
    Dim firstAddress As String
    
FindString = Range("B7").Value

With Sheets("Users").Range("A:G")
    Set fndRng = .Find(What:=FindString, _
                       After:=.Cells(.Cells.Count), _
                       LookIn:=xlValues, _
                       LookAt:=xlPart, _
                       SearchOrder:=xlByRows, _
                       SearchDirection:=xlNext, _
                       MatchCase:=False)
    If Not fndRng Is Nothing Then
        firstAddress = fndRng.Address
        Do
            allFinds = allFinds & "|" & fndRng.Value
            Set fndRng = .FindNext(fndRng)
        Loop While Not fndRng Is Nothing And fndRng.Address <> firstAddress
    End If
End With

UserForm1.Show

End Sub

this in Userform_Initialize

Private Sub UserForm_Initialize()
Me.ListBox1.List = WorksheetFunction.Transpose(Split(Mid(allFinds, 2), "|"))
End Sub

Hope that helps.

 

[Bostockm]

Many thanks, that's great. I've managed to adapt this code to do the trick