Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error 2042 | Excel Programming | |||
VBA code to erase an array element returning Error 2042 ??? | Excel Programming | |||
vlookup error 2042 | Excel Programming | |||
Excel xlodbc error 2042 with SQLRetrieve | Excel Programming | |||
Error 2042 | Excel Programming |