Hi all,

I am using Excel 2003...

We are a small manufacturing company.
All our raw materials have inventory tags with a serial number generated by our "Q:\RM Inventory\RM Inventory Tag.xls]" worksheet. It has approx. 5000 rows. The serial numbers are sequential and are in column A.
All our work in Process and Finished Goods have an inventory tag with a serial number generated by our "Q:\WIP & FG Inventory\WIP & FG Inventory Tag.xls]" worksheet. It has approx. 55000 rows. The serial numbers are sequential and are in column A. (side note: I recognize I will soon need to start a new tab in the WIP & Finished Goods worksheet so I don't run out of rows.)

I am building an inventory taking worksheet where I would enter a serial number in a cell (named SearchTagNumber) and it would bring back the 13 columns of data for the specific tag number entered. The columns hold either text, numbers or dates. Normally, I would use VLookup formulas like this to return the data based on the tag serial number:

Raw Material
=VLOOKUP(SearchTagNumber,Q:\RM Inventory\[RM Inventory Tag.xls]RM Master List'!$A$5:$M$5000,1,FALSE)
=VLOOKUP(SearchTagNumber,Q:\RM Inventory\[RM Inventory Tag.xls]RM Master List'!$A$5:$M$5000,2,FALSE)
etc.

WIP & FG
=VLOOKUP(SearchTagNumber,Q:\WIP & FG Inventory\[WIP & FG Inventory Tag.xls]WIP & FG Master List'!$A$5:$M$60000,1,FALSE)
=VLOOKUP(SearchTagNumber,Q:\WIP & FG Inventory\[WIP & FG Inventory Tag.xls]WIP & FG Master List'!$A$5:$M$60000,2,FALSE)
etc.
The problem is with the WIP & FG formulas. I need 13 VLookup formulas looking at 60,000 rows in the WIP & FG Inventory Tag worksheet. I get the error message "Excel cannot complete this task with available resources".

To resolve this, I would like to use the VBA VLookup function to lookup and return the data from the closed worksheets without the load of all the formulas. I would bring the data back to my inventory taking worksheet to A10:M10 where I would review the data, correct it if necessary, then append it to the next row in the inventory listing on the next tab. For the life of me, I just can not get the Application.WorksheetFunction.VLookup working. Once I get the data back to my inventory taking worksheet, I'm probably fine to figure out how to append the 13 cells to the next open row on either the RM or WIP & FG tabs I would be populating as I take inventory.

Any help and guidance would be most appreciated.

With kind regards,

David