error 2042 with vlookup

  • Thread starter Thread starter Bert
  • Start date Start date
B

Bert

I have a named range ("LC_Chars"), and am trying to use the following code:
sz = Application.VLookup(x, LC_Chars, 2, False)
It is comparing a single character (x; in this case x="7", though the error
is generated no matter what the value of x.). The named range contains two
columns. The search column has been formatted as text and does contain a
"7".
The fix has to be simple, but I'm not seeing it.
Bert
 
Is LC_Chars a defined name on the worksheet? Or is it a Range type
variable? If it is a defined name on the worksheet, use

sz = Application.VLookup(x, Range("LC_Chars"), 2, False)

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 
Is the value in the cell with the 7 in it a real number or text?

Use
=isnumber(a1)
(change a1 to the correct cell address)

I'm not sure where you're getting X, but maybe you want to coerce it to number
before the =vlookup():
sz = Application.VLookup(clng(x), LC_Chars, 2, False)
or
sz = Application.VLookup(cdbl(x), LC_Chars, 2, False)
 
ps.

You could test it by using:

sz = Application.VLookup(7, LC_Chars, 2, False)
and
sz = Application.VLookup("7", LC_Chars, 2, False)

If both work, then you've got both a real number 7 and a text number 7 in that
first column of LC_chars.

If neither work, then you don't have either match.

If one works and the other fails, then you have a number or text in that column.

If the text version works:

sz = Application.VLookup(x & "", LC_Chars, 2, False)

will coerce the lookup value to a string.
 
Dave:
Thanks for your suggestions. The value of the variable X will always be a
text value because I'm taking it from a text string. As it turned out, I'd
named the range on the spreadsheet, and Chip caught that, so it's working
okay now.
 
I use this kind of code:

Dim LC_Chars as range
....
set lc_Chars = worksheets("SomeSheetname").range("LC_Chars")

....

Then I can use the =vlookup() formula that you originally posted.
 
Back
Top