How do you get cell address of a match using Range.Find

[bilten]

I just don't understand the Find method properly. I think. What I am looking for is the Row number at which point the Find method produces a match. The find method is inside a loop setting up a comparison between two ranges on separate sheets, my goal is to capture both the source row and the target row at which a match is found

Sub SeekFindCopy()
    Dim sourceValue As Variant
    Dim resultOfSeek As Range
    Dim targetRange As Range
 
    LastRow = Sheets("SAP").Range("B" & Rows.Count).End(xlUp).Row
    xMlastRow = Sheets("XM").Range("B" & Rows.Count).End(xlUp).Row
    Set targetRange = Sheets("XM").Range("B:B")
    
    For sRow = 4 To LastRow
    Debug.Print ("sRow is " & sRow)
       
    sourceValue = Sheets("SAP").Range("B" & sRow).Value
    
    Set resultOfSeek = targetRange.Find(what:=sourceValue, After:=targetRange(1))
    Operations = Sheets("SAP").Cells(sRow, "A")
    If resultOfSeek Is Nothing Then
        If Operations = "PROCESS" Then

        End If
    Else
        If Operations = "CHANGED" Then

[B]' At this point I want to get the row number of the targetRange in which the match occurred.[/B]

        ElseIf Operations = "PROCESS" Then

        End If
    End If
    Next sRow
End Sub

 

[Norie]

Find will return a Range object if, well, it finds what you are trying to find.

To get the Address from that Range object you would use, in your example code anyway, resultOfSeek.Address.

To get the row similarly it would be resultOfSeek.Row.

 

[bilten]

Thank you very much. That makes sense unfortunately I cannot make it work. I have tried many different wordings but to no avail. The two most likely (In my mind anyway being

Set resultOfSeek = targetRange.Find(what:=sourceValue, After:=targetRange(1))
    xMfoundCell = Sheets("XM").Range(resultOfSeek).Row
            Debug.Print (xMfoundCell)

or

Set resultOfSeek = targetRange.Find(what:=sourceValue, After:=targetRange(1))
    xMfoundCell = resultOfSeek.Row
        Debug.Print (xMfoundCell)

Please explain like I'm 5

Thank you