My VBA Macro Sometimes Works and Sometimes Doesn't

[chsaav]

Hello Guys.
First of all I apologize for my English.
I believe a little background is needed: I have a hush workbook with an inspection template; one of the functionalities is that the user can search employees’ names to assign actions and send notifications emails. I have a worksheet filled with all employees data from GAL and basically I’m interested in two columns: (B) with emails and (D) with names. I have to state that I know very little about VBA but with a lot of help from this forum I came up with a code that search for the employee’s name and place it in a userform listbox for user selection. I recently found out that I have duplicates on names column and that causes me a problem when a subsequent macro has to find the email address associated with this name. After some frustrating attempts to populate a two columns listbox I tried a way around by populating a range in a sheet with the data retrieved by the LOCATE macro but it works sometimes and most of the times don’t. It is a weird thing. I need some help please. Here is the first LOCATE macro and the one I being tried.

Sub Locate(name As String, data As Range)

    Dim rngFind As Range
    Dim strFirstFind As String
    
    With data
        Set rngFind = .Find(name, LookIn:=xlValues, lookat:=xlPart)
        If Not rngFind Is Nothing Then
            strFirstFind = rngFind.Address
            Do
                If rngFind.Row > 1 Then
                ListBox1.AddItem rngFind.Value
                End If
                Set rngFind = .FindNext(rngFind)
            Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstFind
        End If
    End With
    Set rngFind = Nothing
End Sub

Sub Locate(name As String, data As Range)

    Dim rngFind As Range
    Dim strFirstFind As String
    
    With data
        Set rngFind = .Find(name, LookIn:=xlValues, lookat:=xlPart)
        If Not rngFind Is Nothing Then
            strFirstFind = rngFind.Address
            Do
                If rngFind.Row > 1 Then
                ThisWorkbook.Sheets("sheet1").Range("AK1").End(xlDown).Offset(1, 0).Value = rngFind.Value

                End If
                Set rngFind = .FindNext(rngFind)
            Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstFind
        End If
    End With
    Set rngFind = Nothing
End Sub

The Variable "name" come from a textbox used by user to input string to search.

Sorry. I forgot the error when macro does not work (most of the time) is:
run-time error:'1004'
application-difined or object-difined error