Searching Master List For Matches

Question

I am trying to modify some code I had put together and having a bit of a difficult time converting it. My previous code looked through files in a folder, pulled the name from the file, and used that to determine if it was the right file. I am now trying to run through a master list (one file) where the names are in cells instead of on a file name.

The first userform asks for a first and last name and presents a button, search.

Private Sub search_Click() ' In userform1

' Declare and set variables
Dim fname As String, lname As String
Dim Path As String, fCell As Range, fAdd As String
Path = "C:\Master List.xlsx"
fname = userform1.firstname_Search.Text
lname = userform1.lastname_Search.Text
' Store the name searched for
With Worksheets("Sheet1")
    .Range("A1") = fname
    .Range("A2") = lname
End With

Workbooks.Open (Path)

' Ensure the name searched for exists in the master list
With Workbooks("Master List").Worksheets("Master List").Range("A:A")
    Set fCell = .Find(fname)
    If Not fCell Is Nothing And fCell = fname Then
        ' Column A is first name, B is middle initial, C is last name, D is suffix, F is date of birth
        If fCell.Offset(0, 2) = lname Then
            userform2.firstname_Text.Text = fCell
            userform2.middlename_Text.Text = fCell.Offset(0, 1)
            userform2.lastname_Text.Text = Trim(fCell.Offset(0, 2) & " " & fCell.Offset(0, 3))
            userform2.dob_Text.Text = fCell.Offset(0, 5)
            Unload Me
            userform2.Show vbModeless
            userform3.Label1.Caption = "Now that we have the information from " & fCell & "'s file, what would you like to do?"
        Else
            MsgBox ("I could not find a client by that name.")
            Workbooks("Master List").Close False
        End If
    Else
        MsgBox ("I could not find a client by that name.")
        Workbooks("Master List").Close False
    End If
End With

End Sub

This section appears to run fine and will pull up the first entry that matches the first name and last name entered. The issue is occurring when the second userform, userform2 is pulled up, because it displays the relevant information to determine if the right person has been pulled up. It presents first, middle, last names and the date of birth along with a Yes and No button. Clicking Yes pulls the information (which I have not written yet), while clicking No should cycle through the remaining matches (for example, if there are 3 William Jackson s listed, clicking No should cycle to the second; a second No should cycle to the third; it should then present the MsgBox because no other entries exist for that name).

The issue is that I cannot find a way to cycle past the first No; if No was clicked a second time, it wouldn't go past the second entry found. I know this is because of the Set fCell = .Find(fname) and Set fCell = .FindNext(fCell) at the beginning, but short of making a cell dedicated to how many times No has been clicked, is there a better way to do this?

Private Sub no_Click() ' In userform2

' Declare and set variables
Dim fname As String, lname As String
Dim Path As String, fCell As Range, fAdd As String
Path = "C:\Master List.xlsx"
With Workbooks("FirstWorkbook").Worksheets("Sheet1")
    fname = .Range("A1")
    lname = .Range("A2")
End With

' Ensure a client exists
With Workbooks("Master List").Worksheets("Master List").Range("A:A")
    Set fCell = .Find(fname)
    Set fCell = .FindNext(fCell)
    If Not fCell Is Nothing And fCell = fname Then
        If fCell.Offset(0, 2) = lname Then
            firstname_Text.Text = fCell
            middlename_Text.Text = fCell.Offset(0, 1)
            lastname_Text.Text = Trim(fCell.Offset(0, 2) & " " & fCell.Offset(0, 3))
            dob_Text.Text = fCell.Offset(0, 5)
            userform3.Label1.Caption = "Now that we have the information from " & fCell & "'s file, what would you like to do?"
            With Workbooks("FirstWorkbook").Worksheets("Sheet1")
                .Range("A1") = fCell
                .Range("A2") = fCell.Offset(0, 2)
            End With
        Else
            MsgBox ("I could not find a client by that name.")
            Workbooks("Master List").Close False
        End If
    Else
        MsgBox ("I could not find a client by that name.")
        Workbooks("Master List").Close False
    End If
End With

End Sub

Maybe there's a better way to use one userform, or a better way to search through the master list; either a solution that helps to solve this problem or a point in the right direction so I can look at a different way to do it would help me greatly.

Tim Williams · Accepted Answer

I would suggest factoring out the Find into a standalone function, and have it return all matches to the search value (in the example below it returns a collection object). You'd then store that return value in a global field in your form.

It's much easier to cycle through the return value of a function like this than to re-run the search (starting at a different location) every time the use clicks No.

Public Function FindAll(rng As Range, val As String) As Collection
    Dim rv As New Collection, f As Range
    Dim addr As String

    Set f = rng.Find(what:=val, after:=rng.Cells(rng.Cells.Count), _
        LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False)

    If Not f Is Nothing Then addr = f.Address()

    Do Until f Is Nothing
        rv.Add f
        Set f = rng.FindNext(after:=f)
        If f.Address() = addr Then Exit Do
    Loop

    Set FindAll = rv
End Function

Searching Master List For Matches

Answers (2)

Related Questions