Use Excel VBA to loop items in one sheet, match to another and copy value

Question

I have this 85% completed but am stuck with the last part. I have 2 sheets. One has line items with prices, descriptions, etc. The other sheet has a master list of the items with the unit price that is associated with it. My code below finds the match in from sheet1 to sheet2 correctly. I am trying to get the unit price from sheet2 which is in column B. So basically the items from sheet1 Column C match to a list in sheet2 Column A. When the match is found, I need the value that is in sheet2 Column B for the matching row. Please let me know if you can help or need any further explanation. This is driving me crazy. Kind of a newbie with VBA. Thanks!

Sub test2()
Dim r1 As Range
Dim r2 As Range
Dim cell As Range
Dim lastrow As Long



With ThisWorkbook.Worksheets("Sheet1")
    lastrow = .Cells(.Rows.Count, "C").End(xlUp).Row
    Set r1 = .Range("C2:C" & lastrow)
End With




With ThisWorkbook.Worksheets("Sheet2")
    lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
    Set r2 = .Range("A1:A" & lastrow)
End With


For Each cell In r1
    If IsError(Application.Match(cell, r2, 0)) Then
        cell.Offset(, 15) = "NotFound"
    Else
        cell.Offset(, 15) = "Found"
        ' If found I need the value from Sheet2 that is in Col B of the matching row.
    End If
Next cell




End Sub

Sheet 1
Col A
Item 1
Item 2
Item 3

Sheet 2
Col A Col B
Item 1 1.99
Item 2 2.99
Item 3 3.99

I am trying to loop Col A in sheet one and match to the corresponding value in sheet2 and copy the price (ColB of sheet2) into sheet1

eugene eugene · Accepted Answer · 2018-01-03T03:22:27

Is it what you are trying to get?:

    ' If found I need the value from Sheet2 that is in Col B of the matching 
With ThisWorkbook
.Sheets("Sheet1").Cells(cell.Row, 2).Value =.Sheets("Sheet2").Cells(Application.Match(cell, r2, 0), 2).Value
End With

Use Excel VBA to loop items in one sheet, match to another and copy value

1 Answers