error 2042 with vlookup
 

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

error 2042 with vlookup
 

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)


On Mon, 16 Feb 2009 10:01:45 -0500, "Bert"
wrote:

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

error 2042 with vlookup
 

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)



Bert wrote:

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

--

Dave Peterson

error 2042 with vlookup
 

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 Peterson wrote:

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)

Bert wrote:

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

--

Dave Peterson

--

Dave Peterson

error 2042 with vlookup
 

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

"Chip Pearson" wrote in message
...

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)


On Mon, 16 Feb 2009 10:01:45 -0500, "Bert"
wrote:

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

error 2042 with vlookup
 

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.

"Dave Peterson" wrote in message
...
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)



Bert wrote:

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

--

Dave Peterson

error 2042 with vlookup
 

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.




Bert wrote:

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.

"Dave Peterson" wrote in message
...
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)



Bert wrote:

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

--

Dave Peterson

--

Dave Peterson