find and retrieve a price from a different sheet index match - vlookup?

[lostinvb]

worksheet "price" has an item# in column A and a price in column G rows count varies as things are deleted or added 50000+

worksheet "invoice" column A has the item# column G needs the price row count varies usually under 200 items


I have a macro that imports the invoice into "invoice" I would then like to run a macro to run down "invoice" retrieving the price from "price" until last row.

vlookup confuses me after the point the I have to loop it until last row. Index Match .... heard its faster but way over my head. can someone help?

also item# may not me found (then leave $ blank and move onto the next item#)

please please help

[Paul_Hossler]

Post a small sample workbook, and make sure to show the desired results

Use [Go Advanced] in lower right, and then the paperclip icon

Meantime, here's a guessed-at approach and sample workbook

Option Explicit
Sub AddPrice()
    Dim wsPrice As Worksheet, wsInvoice As Worksheet
    Dim iInvoice As Long, iLastInvoice As Long
    Dim iPrice As Long
    
    Application.ScreenUpdating = False
    
    Set wsPrice = Worksheets("Price")
    Set wsInvoice = Worksheets("Invoice")
    
    iLastInvoice = wsInvoice.Cells(wsInvoice.Rows.Count, 1).End(xlUp).Row
    
    For iInvoice = 2 To iLastInvoice
        iPrice = 0
        
        On Error Resume Next    '   in case not found
        iPrice = Application.WorksheetFunction.Match(wsInvoice.Cells(iInvoice, 1).Value, wsPrice.Columns(1), 0)
        On Error GoTo 0
        
        wsInvoice.Cells(iInvoice, 7).Value = wsPrice.Cells(iPrice, 7).Value
    Next iInvoice
    Application.ScreenUpdating = True
End Sub

[lostinvb]

Post a small sample workbook, and make sure to show the desired results

Use [Go Advanced] in lower right, and then the paperclip icon

Meantime, here's a guessed-at approach and sample workbook

Option Explicit
Sub AddPrice()
    Dim wsPrice As Worksheet, wsInvoice As Worksheet
    Dim iInvoice As Long, iLastInvoice As Long
    Dim iPrice As Long
    
    Application.ScreenUpdating = False
    
    Set wsPrice = Worksheets("Price")
    Set wsInvoice = Worksheets("Invoice")
    
    iLastInvoice = wsInvoice.Cells(wsInvoice.Rows.Count, 1).End(xlUp).Row
    
    For iInvoice = 2 To iLastInvoice
        iPrice = 0
        
        On Error Resume Next    '   in case not found
        iPrice = Application.WorksheetFunction.Match(wsInvoice.Cells(iInvoice, 1).Value, wsPrice.Columns(1), 0)
        On Error GoTo 0
        
        wsInvoice.Cells(iInvoice, 7).Value = wsPrice.Cells(iPrice, 7).Value
    Next iInvoice
    Application.ScreenUpdating = True
End Sub

[Paul_Hossler]

Actually, I'd make a small change -- I forgot to actually handle the case if the Item number wasn't found (the iPrice = 0 case)

If iPrice > 0 then
sInvoice.Cells(iInvoice, 7).Value = wsPrice.Cells(iPrice, 7).Value
End If