Application.WorksheetFunction.VLookup

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

Hi jmac1947,

Thank you kindly for your reply. I'm relatively new at this but if I understand it correctly, I think your code would still result in a series of (macro-generated) VLookup formulas in my inventory taking worksheet which would then have the same resource issues as if I entered the formulas directly into cells. If I've misunderstood and it actually returns the cell values, then it might be perfect.

Another key thing to note is that the Master List worksheets are not open on my computer, only my inventory taking worksheet.

I need to find a way to use a VBA function to, using the serial # I've entered, identify the matching row in the 60,000 line worksheet then bring back the values in the 13 columns of that row. I'd also like to do this in a way that doesn't open then close the Master List worksheets in order to get the data.

Apologies for the lengthy initial post! Hopefully this clarifies somewhat...

Thanks again,

David

Hello David,
thanks for the reply, and yes my original code suggestion would do as you suggest.

OK, so onto Plan B then (which is possibly outside my experience in handling multiple workbooks) in the event that no-one else chimes in with a solution I think something like the following is what we need to do.

first get the tag you are interested in, easy enough to achieve with the msgbox statement (or use the contents of a cell you have just entered the tag into perhaps).
I will assume for the time being that the master files are in a static path with a static name. I will also assume that the "tag" value in each of the masters is in Col A and that the value is unique within each workbook.

Second, we need to (for each master list) filter the master worksheet to show only the row that matches the tag you entered. (no matches would be an error to trap, multiple which you say should not occur can be handled by either treating as an error or by simply taking the first row returned).

After the filter has been applied you simply copy the row (which will have the 13 cols you are interested in) back to the original workbook where you do your usual "magic happens here stuff"

Easy enough for me to say, and if it was different sheets in a single workbook it would be easy to actually do.

I will have to spend a bit of research and playing time to do the multiple workbook aspects, particularly the filter bit.

Will continue to play over the weekend and hopefully work out something for you, any other readers who have better "multiple workbook processing skills" (and that will not take much) feel free to chime in

Jmac

Hi jmac19,

You're right, it would be easier if all the data was in the same workbook on different sheets. It's unfortunately not possible to do that. I'm also hoping to not actually open the external workbooks to retrieve the data as staff go in and out of it all day long adding in and tagging our production as it happens. This is why I was hoping to do it without opening the file. I'm hearing that VLookup in VBA only works with open workbooks...

Thank you so much for helping me. I really appreciate it. Hopefully we can find a method that will work!

With kind regards,

David