Microsoft Office Forums - View Single Post - [Solved] Comparing between 2 worksheets and retreiving data if a match

NoSparks · 02-16-2017, 08:11 AM

based on post #1, and assuming the first name, initial and last name combination only appears once on the 'List' sheet, you could perhaps use the range.find and .findnext methods based on the last name.

Code:

Sub Fugman_02_16_2017()

Dim Lname As String, Initial As String, Fname As String
Dim fndLastName As Range, firstAddress As String
    
With Sheets("Input Data")
    Lname = .Range("G1").Value
    Initial = .Range("F1").Value
    Fname = .Range("E1").Value
End With

With Sheets("List").Range("G:G")
    Set fndLastName = .Find(What:=Lname, After:=.Cells(.Cells.Count), _
                        LookIn:=xlValues, LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                        MatchCase:=False)
    
    If Not fndLastName Is Nothing Then
        firstAddress = fndLastName.Address
        Do
            If fndLastName.Offset(0, -2).Value = Fname And fndLastName.Offset(0, -1).Value = Initial Then
                fndLastName.Offset(0, 1).Resize(1, 19).Copy Sheets("Input Data").Range("H1")
                Exit Do
            End If
            Set fndLastName = .FindNext(fndLastName)
        Loop While Not fndLastName Is Nothing And fndLastName.Address <> firstAddress
    End If
End With

End Sub