Problem using vLookup in Excel VBA

**georgeu2011** · 05-14-2011, 08:13 PM

I have a spreadsheet into which I place a number in one column and it needs to get the matching location info from another spreadsheet (which is usually closed). I can do this using a vLookup in the cells, but this is clumsy and as the first spreadsheet already has some 4500 rows (and growing), it is slow and causes the file size to be very large.

Therefore I have decided to do it in VBA, but I am having a problem with it that I cannot seem to solve. Any help would be appreciated......

The VBA code I have in the first spreadsheet is as follows:

(This is located in Sheet1 code so that it executes automatically when the value of the cell in column AP is changed in the current row)

Please Login or Register  to view this content.

The second vLookup line works flawlessly, but the first does not. I need the vLookup to work with which ever row is currently active, so that this will work no matter how many rows there are. The other problem is that the value in column AP is not always SCRAPPED, it varies from row to row. So the value it does the vLookup on has to be a variable which is why the (working) second vLookup line is no good as it is.

I can see what is happening with the first vLookup, it places the vLookup formula into the cell in column AO but intBUILDnum is a parameter that the spreadsheet cannot resolve, so it gives me a #NAME? error. I just don't know enough vba to be able to see the solution, though I suspect the code needs to be changed quite a lot to be able to do what I am asking.

As I said before your help will be greatly appreciated......

**TMS** · 05-14-2011, 09:24 PM

Try

Please Login or Register  to view this content.

Regards

**georgeu2011** · 05-15-2011, 12:52 AM

Originally Posted by TMShucks

Try

Please Login or Register  to view this content.

Regards

Thankyou for the very much appreciated "amazingly quick" reponse.
However, it seems there is still a bug. When implemeted as suggested, it places the following FORMULA into cell AO::
=VLOOKUP(0, '\\ODFSLFS1\im\5.Records\Assets\Locations\[OD Locations Master List.xls]Building Master List'!$A$2:$I$335,2,FALSE)
As you can see, it is not the cell reference, instead it has placed a 0 into the value to do a vLookup on. Consequently it gives me #N/A as the displayed value for the cell. Is there a minor bug in the adjusted code, or have I done something else wrong here?

**georgeu2011** · 05-15-2011, 01:08 AM

TMShucks,

Thankyou very very much. I figured it out.....I had to change the intBUILDnum variable from an INTEGER to a STRING. It now works perfectly. Yipee!
Thanks again.