Vlookup table array reference
Posted by strawberry on August 22, 2001 2:17 AM
Hi! I am trying to write a macro that uses the Vlookup function in excel. My problem is that the reference table array's number of rows is always increasing. So I wrote a code to the count the number of rows in the reference table and set a variable for the no of rows. However, now I do not know how to set the range for the table array to make the macro work.
Posted by Rob Jackson on August 22, 2001 3:22 AM
Are you trying to populate a single cell with an answer each time you run the code or are you trying to populate a cell or group of cells with a VLOOKUP formula and get the code to set the VLOOKUP parameters?
Posted by strawberry on August 22, 2001 7:07 PM
I am trying to get the code to set the VLOOKUP parameters. The number of lines in the lookup table will increase. My concern is if I fix a table range, I will have to go back to change the code every time i increase a row in the lookup table. so I want the program to be a bit more intelligent in that the range defined for the table array will change according to the change in the number of rows. Do you have any idea how i should do this?
Posted by Rob Jackson on August 28, 2001 4:37 AM
Have4 you tried using range names, no code required?
Posted by strawberry on August 31, 2001 1:48 AM
I tried. I set a range name such that it contains a variable but apparently it doesn't work.
I did something like this:
Set tablerange = range (cells(1 , 1), cells(A,2))
where A is the variable for the no of rows.
