Why won't VLOOKUP work?


Posted by Jim on April 20, 2001 2:07 PM

Why won't this formula work? I'm sure it has to do with using Pool as the lookup value. Pool is a numeric variable. I've also tried a string variable with the same results.

=VLOOKUP(Pool,'long path[file name.xls]summary'!$D$6:$M$200,10,FALSE)

Posted by Aladin Akyurek on April 20, 2001 2:15 PM

Jim

If pool is a name of a cell containing your lookup value, other things being equal, there shouldn't be a problem.

What does it return?

Aladin

Posted by Jim on April 20, 2001 2:24 PM

Pool is a variable name that contains 6 digits but when the macro passes this formula to another cell, "Pool" (without the quotes) comes through instead of the 6 digits. I can go into the cell and replace the word "Pool" with the 6 digits and everything is rosy.

Here is the line from the macro:

.Range("C" & CompRow) = "=VLOOKUP(Pool,'N:\shared\Servicing\CountryWide Sub-Serv\First File\[summaryx.xls]summary'!$D$6:$M$200,10,false)"

Posted by Dave Hawley on April 20, 2001 2:27 PM


Hi Jim

As you are using a variable you have to use the Amperstand to join it with your other Text.

.Range("C" & CompRow) = "=VLOOKUP(" & Pool & ",'N:\shared\Servicing\CountryWide Sub-Serv\First File\[summaryx.xls]summary'!$D$6:$M$200,10,false)"

Dave


OzGrid Business Applications

Posted by Aladin Akyurek on April 20, 2001 2:28 PM

Wouldn't INDIRECT(Pool) work?

====================== Pool is a variable name that contains 6 digits but when the macro passes this formula to another cell, "Pool" (without the quotes) comes through instead of the 6 digits. I can go into the cell and replace the word "Pool" with the 6 digits and everything is rosy.



Posted by Jim on April 20, 2001 2:37 PM

Thanks Aladin and Dave

Thanks to all for the assistance!!!