I have a spreadsheet of 6000+ inventory parts (inventory.xlsx). I have one section of the warehouse on another spreadsheet (warehouse4.xlsx).

My boss wants to know the cost of this section in particular, so he wants me to find these 800 parts in the 6000 list, and copy the quantity on hand and unit cost from the long list onto the short list so we can sum only that.

My long list (inventory.xlsx) is like this:
A...................B.........................C...............D
Part# | Part Description | Qty On Hand | Unit Cost

My short list is only Part# on Column A (warehouse4.xlsx).

I tried to run some VBA to copy the value on warehouse4.xlsx (A2), find it in inventory.xlsx, select the cell, move (offset) and copy the values from qty on hand and unit cost into warehouse4.xlsx and then loop this, but I cannot get it to work. If I set a variable to store the copied value as string, it returns error 91, if I set it as integer it works, but finds something completely different since it uses the value rather than the string.

Any help? Thanks!