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