cannot get row through find function-CodePudding

I'm trying to loop the cells of a range. Then, check if every cell has a match in the range rngIng and if there is one, get the row where the match is and later, copy the value of the offset cell in column B and paste its value in column J. But when I'm trying to get the row there is a variable type error. Here is where the issue is row = .rngIng.Find(What:=Name, LookIn:=xlFormulas, LookAt:=xlWhole).row Any idea what I'm doing wrong?

    Set ws = ThisWorkbook.Worksheets(1)

    'Find which is the last row with data in column "C"
    ' Define range with data in column "C"
    Dim rngIng As Range
    Dim lastRowIng As Integer
    With ws.Columns("C")
        lastRowIng = .Cells(.Rows.Count).End(xlUp).row
        Set rngIng = .Range(Cells(2, "C"), Cells(lastRowIng, "C"))
    End With
    
    'Find which is the last row with data in column "A"
    Dim lastRowRef As Integer
    With ws.Columns("A")
        lastRowRef = .Cells(.Rows.Count).End(xlUp).row
    End With
     

    Dim i, j, row As Long
    i = 2
    j = 2
    
    For i = 2 To lastRowRef
            Dim Name As Variant
            Name = ws.Cells(i, "A").Value
            With ws
              row = .rngIng.Find(What:=Name, LookIn:=xlFormulas, LookAt:=xlWhole).row
            End With

        If row <> 0 Then
            ws.Cells(j, "J").Value = ws.Cells(row, "B").Value
            j = j   1
        End If

    Next

End Sub

CodePudding user response：

Try this:

Sub Tester()
    
    Dim ws As Worksheet, j As Long
    Dim rngIng As Range, c As Range, f As Range
    
    Set ws = ThisWorkbook.Worksheets(1)

    ' Define range with data in column "C"
    Set rngIng = ws.Range("C2:C" & ws.Cells(ws.Rows.Count, "C").End(xlUp).row)
    
    j = 2
    For Each c In ws.Range("A2:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).row).Cells 'loop Col A
        Set f = rngIng.Find(What:=c.Value, LookIn:=xlFormulas, LookAt:=xlWhole)       'search col C
        If Not f Is Nothing Then                                                      'any match?
            ws.Cells(j, "J").Value = f.EntireRow.Columns("B").Value
            j = j   1
        End If
    Next
    
End Sub