VLookup Help needed pretty please :)

[instanceoftime]

I want to create a macro and

I would like to:

be able to open an invoice, hit my macro to lookup the item numbers in column A and search a different workbook called "Final" on my desktop (C:\Users\Glen\Desktop) for the same item number in column A. When found I want to take the corresponding price in column H and place it in E of the invoice. I would like to do this for every row of the invoice.
I've found many references of the formula but not how to run down my list and fill in.


Workbook "Final" continuously changes in size

The cell H in "Final" may be blank (at which point go ahead and continue with the next item number in "invoice")

The cell A (item number) in the invoice may not be found in "Final" (at which point go ahead and continue to with next item number in "Invoice" until last )


So in english: start on "Invoice" A1, take that item number and look for it in workbook "Final" and if found take the corresponding value in "Final" H1 and place that value in "Invoice" C1

go to next item number in "Invoice" column A and continue this until last line. I have read 1000 posts but am not understanding the code enough to know if I can adapt it.

having "Final" open or closed isn't an issue (some posts didn't want to have to have the file open ... that isn't an issue here)

Please help I'm seeing vlookup code in my sleep.

 

[instanceoftime]

The following formula get's what I need from the first row:

=VLOOKUP(A1,[final.xls]Items!$A$1:$H$28897,8,0)


1st issue: I need this in a macro and to run until last line of current workbook

2nd issue: range is columns A through H however then length is always growing

 

[Michael M]

Maybe this...but you state you want it in "E" of the invoice, then further in the post you state column "C"
Change the letters in red to "C" if they are not the desierd ones

Sub MM1()
Dim lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
Range("[color=red]E[/color]1:[color=red]E[/color]" & lr).Formula = "=VLOOKUP(A1,[final.xls]Items!$A$1:$H$28897,8,0)"
End Sub

 

[instanceoftime]

Oops correct.... Column E kk I'll run this and get back

Maybe this...but you state you want it in "E" of the invoice, then further in the post you state column "C"
Change the letters in red to "C" if they are not the desierd ones

Sub MM1()
Dim lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
Range("[COLOR=red]E[/COLOR]1:[COLOR=red]E[/COLOR]" & lr).Formula = "=VLOOKUP(A1,[final.xls]Items!$A$1:$H$28897,8,0)"
End Sub

 

[instanceoftime]

LOL You are a genius that works perfectly ..... many thanks. I was making it way harder.

Now... I have a static range specified for "final" .. that range will vary in length as well. How do I do that row count as well?

=VLOOKUP(A1,[final.xls]Items!$A$1:$H$28897,8,0)"