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?

 

[instanceoftime]

Are the values in col A unique?

Not always.

 

[Fluff]

In that case will the price be the same for all duplicates?
If not how do you know which price goes to which row?

 

[instanceoftime]

In that case will the price be the same for all duplicates?
If not how do you know which price goes to which row?

spreadsheet is sorted by item# and then price(low to hi). Was hoping that it would take the final value (highest)
(sorry, I had assumed it would find first value, then as it found the second it would overwrite the first and so on)

123456 .59
123456 1.99
123456 2.99



PS: I have never worked with an object. The dictionary is quite interesting

 

[Fluff]

(sorry, I had assumed it would find first value, then as it found the second it would overwrite the first and so on)

That's exactly what it does do.
With your example, every value of 123456 on the active sheet should have a value of 2.99 in col B.
If that is not happening, can you please explain exactly what you are getting?

 

[instanceoftime]

That's exactly what it does do.
With your example, every value of 123456 on the active sheet should have a value of 2.99 in col B.
If that is not happening, can you please explain exactly what you are getting?

I keep changing things to make it work, so I'll start from the beginning sorry without altering spreadsheet.

In PRICELIST.... If I have a number of blanks throughout in the price column... it inserts blank prices in everything in NEWLOAD when running macro

Oddly If I delete all rows with blanks (price) then it works. then If I "create" new blanks it still works...?

could it be the number of rows (38000 when blanks aren't removed)? We are entering prices all the time so removing the blanks is unwanted in the spreadsheet.

 

[Fluff]

Do you mean that you can have an item with no price?