Using a Macro to perform a VLookup on varying number of rows

Hi

I am using a VLookup to find a show a match in column A from a list contained in another file. The lookup works fine, however, I want to automate the task by using a Macro. I can copy and paste the lookup formula down the remaining cells in column A but my problem is that this file is loaded on a regular basis and does not necessarily have the same number of rows in it the next time it is loaded. I need to copy the lookup into the last active cell in column A. Any suggestions would be much appreciated.


Shirley

A number of get-arounds:

1 - use a named range for the vlookup. Redefine the named range from a macro then go
2 - use the range right down to the bottom of the spreadsheet (this is the easiest workaround). ie to row 65536. You don't need to worry how many rows there are now.
3 - use code to look it up. Depends on what else you're doing.
4 - get a macro to update your vlookup formula depending on the number of rows by using string manipulations to make the C3:F2000 bit.


I'm sure there's more but I use 2 unless there are other things happening making the other things worth doing.