Compare Data From 3 Workbooks Against 2 Files

For the past several days I have been attempting to write a macro, which in theory would check the values within a one- sheet workbook (we’ll call it run_list.xls) against the values in two different workbooks (we’ll call these production.xls, development.xls). However after several days I am nowhere closer to solving this problem then I was when I started. I’ve tried different scripts and variations of vlookup, but I have been unsuccessful in tailoring what I have found to meet my needs. So I feel it is time to lie down and scream for a medic.

Here’s some background on the workbooks. The sheets in all three workbooks are set up in the same manner. Cell “A1” contains time/ date, cell “A2” contains a lot#, cells “A3:A99” contain positioning data, and cells “B3:B99” contain serial numbers associated with tubes in the specific positions. The only differences between the workbooks are the sheets in production.xls and development.xls are labeled according to their specific lot number.

What I have been trying to do is to take a value from cell "B3"in run_list.xls and find where that value occurs in column B either of workbooks. When that value was found I wanted to copy cell "A2" from its sheet and paste that value in cell "D3" of run_list.xls. I had also wanted to repeat that those steps for every cell in column B containing a serial #. If a serial number was not found I wanted it to report “Not Found”. There are also times when instead of a serial number a phrase “No Trakmate” is listed. In these cases I had intended to skip these lines.

Also, since production.xls, development.xls are updated frequently their names are also updated with new version numbers ie. Production_v10.xls I had intended to allow the user to choose what files to search in but was unable to figure out how to add a second location.