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)
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......