error 2042 with vlookup

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
 
C

Chip Pearson

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

Dave Peterson

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

Dave Peterson

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

Bert

Chip:
The range IS a worksheet-defined name. You change worked perfectly!
Thanks.
Bert
 
B

Bert

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

Dave Peterson

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