Look up an item# in different workbook and copy price

[instanceoftime]

Hi Guys, after pouring through many examples, I am not understanding how to fulfill this task.
NEWLOAD.xls is incoming inventory. Column A contains item numbers (example...123456).
PRICELIST.xls contains prices for this inventory. Column A also contains the item number and column E the price.

In NEWLOAD I would like to run a macro that will:
- start in A1 and search PRICELIST for that item number. If found, return the value in PRICELIST E1 .. to NEWLOAD B1 down to last row
- column length for both files change often.

example: A1 in NEWLOAD contains item number 123456. The macro will open PRICELIST search column for 123456.
when found it will take the corresponding price in E and place that value in B1 ... and continue this until the last row in NEWLOAD and everything is priced

I am imagining an array? .find? NEWLOAD will most likely be under 200 rows but PRICELIST has 35000+

to many years out of school and almost no understanding of an array. Any help guys?

 

[Fluff]

How about

Sub dic()
   Dim cl As Range
   Dim dic As Object
   Dim ws1 As Worksheet, ws2 As Worksheet

Application.ScreenUpdating = False
   Set ws1 = Workbooks("PRICELIST").Sheets("[COLOR=#ff0000]Sheet1[/COLOR]")
   Set ws2 = Workbooks("NEWLOAD").Sheets("[COLOR=#ff0000]Sheet1[/COLOR]")
   Set dic = CreateObject("scripting.dictionary")
   For Each cl In ws1.Range("A2", ws1.Range("A" & Rows.Count).End(xlUp))
      dic(cl.Value) = cl.Offset(, 4).Value
   Next cl
   For Each cl In ws2.Range("A2", ws2.Range("A" & Rows.Count).End(xlUp))
      cl.Offset(, 1).Value = dic(cl.Value)
   Next cl
End Sub

Both workbooks need to be open, & change the values in red to suit.
I've assumed you have a header row in row1

 

[instanceoftime]

Run-time error: '1004'
method 'range' of object '_worksheet' failed

 

[instanceoftime]

Run-time error: '1004'
method 'range' of object '_worksheet' failed

kk I got it fixed. pricelist was an earlier excel version file xls

 

[instanceoftime]

Works great .... rather than have "NEWLOAD" how can I just have it do current active sheet?

 

[Fluff]

Change ws2 to

   Set ws2 = ActiveSheet

 

[instanceoftime]

Set dic = CreateObject("scripting.dictionary")
For Each cl In ws1.Range("A1", ws1.Range("A" & Rows.Count).End(xlUp))
dic(cl.Value) = cl.Offset(, 7).Value
Next cl
For Each cl In ws2.Range("A1", ws2.Range("A" & Rows.Count).End(xlUp))
cl.Offset(, 7).Value = dic(cl.Value)
Next cl


If PRICELIST column 7 contains a blank then nothing at all copies over (not even from one that isn't blank)?????

 

[instanceoftime]

Change ws2 to

   Set ws2 = ActiveSheet

Thanks Fluff ..

 

[instanceoftime]

It works correctly if I erase All the rows with blank cells in column 7. however if I put a 1.00 just in the blank cells in column 7 then it returns 1 in ALL the cells regardless if it says 5.99 for example. I don't understand this is so weird. I can upload both files (if you can tell me how) .... actually column 8 ( was referring to offset 7)

 

[Fluff]

Are the values in col A unique?