Match code is not returning the desired field

[raabouza]

Greetings,

I am trying to determine why the code "code/file attached within" is not returning the values that I need, no luck so far.

My goal is to take the value in A from sheet 1, match it in B from sheet 2 to determine the row number then copy E & F values from the same line "sheet 2" and return it to the corresponding row/column in sheet 1.

I am kindly seeking your assistance to point me in the right direction.

Thank you.

RA

Sub HR_Lookup()




' Look for HR# and copy a specific field


Dim iRow As Long
Dim wb1 As Workbook
Dim c As Range
Dim rngSearch As Range




  Set wb1 = Workbooks("Data.xlsm")
  
  
  Set rngSearch = wb1.Worksheets("Sheet2").Range("B2:B10")
  
  For Each c In wb1.Worksheets("Sheet1").Range("A2:A11")
    
    iRow = WorksheetFunction.Match(c.Value, rngSearch, 0)
    
    c.Offset(1, 4).Value = rngSearch.Offset(iRow, 3).Resize(1, 1).Value
    
  Next c




End Sub

[大灰狼1976]

Hi raabouza!
Modify your original code and created new one.

Sub HR_Lookup()

' Look for HR# and copy a specific field
Dim iRow As Long
Dim wb1 As Workbook
Dim c As Range
Dim rngSearch As Range

  Set wb1 = Workbooks("Data.xlsm")
  
  
  Set rngSearch = wb1.Worksheets("Sheet2").Range("B2:B11")
  
  For Each c In wb1.Worksheets("Sheet1").Range("A2:A11")
    
    iRow = WorksheetFunction.Match(c.Value, rngSearch, 0)
    
    c.Offset(0, 3).Resize(, 2).Value = rngSearch.Cells(iRow, 4).Resize(1, 2).Value
    
  Next c

End Sub


Sub HR_Lookup_1()
Dim i&, rng As Range
With Sheets(1)
  For i = 2 To .Cells(Rows.Count, 1).End(3).Row
    Set rng = Sheets(2).Columns(2).Find(.Cells(i, 1), lookat:=xlWhole)
    If Not rng Is Nothing Then
      .Cells(i, 4).Resize(, 2) = rng.Offset(, 3).Resize(, 2).Value
    End If
  Next i
End With
End Sub

[raabouza]

Hi,

Works like a magic, truly appreciated.

Thanks for your time,

RA

[大灰狼1976]

Hi raabouza!
No thanks