Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA vlookup error depending on text string & quotation marks
The code causing me grief is as follows:
Dim acct as string, subacct as string acct = CStr(Trim(Cells(i, clm_acct).Value)) subacct = CStr(Evaluate("=VLOOKUP(" & acct & ",'" & ThisWorkbook.Name & "'!mapping,2,FALSE)")) The referenced account numbers (variable acct) appear as text in the first column of a table. Most of the account numbers are all numeric values, e.g. 345999, but some are a combination of numbers and letters, e.g. 345ABC. When I call the procedure as is, I get an error, Error 2015, whenever acct equals a combination of numbers and letters (345ABC). If I add quotation marks to the function, e.g. Evaluate("=VLOOKUP(""" & acct & """,'" & ThisWorkbook.Name & "'!mapping,2,FALSE)")), I no longer get an error with the alphanumeric account numbers, but all the numeric only values (345999) now result in an error. While these two lines of code are the first instance of this problem in my procedure, I use the Evaluate("=VLOOKUP()) in a number of places. I'm looking for a solution that will save me a bunch of extra lines of code. One workaround would be to add IF(ISERROR(() to the Evaluate(), but then I will have to add that in every instance and it could really start to slow down the procedure, plus make the code itself much larger. I thought if the cell values were stored in text format and the acct variable was a string variable, the vlookup would work for alpha and numeric values alike, but it seems I was wrong. Does anyone know another solution, either with code or by possibly changing the set up of the referenced worksheet? Thanks, Ben |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA vlookup error depending on text string & quotation marks
I would think storing all the account numbers as text in the lookup table
would work. Just formatting the cells as text after the numbers are already stored would not change them to text. You would need to edit each one and store it as text or precede it with a single quote for each cell in selection if isnumber(cell.value) then cell.value = "'" & cell.value end if Next -- Regards, Tom Ogilvy "xlcharlie" wrote: The code causing me grief is as follows: Dim acct as string, subacct as string acct = CStr(Trim(Cells(i, clm_acct).Value)) subacct = CStr(Evaluate("=VLOOKUP(" & acct & ",'" & ThisWorkbook.Name & "'!mapping,2,FALSE)")) The referenced account numbers (variable acct) appear as text in the first column of a table. Most of the account numbers are all numeric values, e.g. 345999, but some are a combination of numbers and letters, e.g. 345ABC. When I call the procedure as is, I get an error, Error 2015, whenever acct equals a combination of numbers and letters (345ABC). If I add quotation marks to the function, e.g. Evaluate("=VLOOKUP(""" & acct & """,'" & ThisWorkbook.Name & "'!mapping,2,FALSE)")), I no longer get an error with the alphanumeric account numbers, but all the numeric only values (345999) now result in an error. While these two lines of code are the first instance of this problem in my procedure, I use the Evaluate("=VLOOKUP()) in a number of places. I'm looking for a solution that will save me a bunch of extra lines of code. One workaround would be to add IF(ISERROR(() to the Evaluate(), but then I will have to add that in every instance and it could really start to slow down the procedure, plus make the code itself much larger. I thought if the cell values were stored in text format and the acct variable was a string variable, the vlookup would work for alpha and numeric values alike, but it seems I was wrong. Does anyone know another solution, either with code or by possibly changing the set up of the referenced worksheet? Thanks, Ben |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA vlookup error depending on text string & quotation marks
Thanks Tom. Adding the apostrophe did the trick.
"Tom Ogilvy" wrote: I would think storing all the account numbers as text in the lookup table would work. Just formatting the cells as text after the numbers are already stored would not change them to text. You would need to edit each one and store it as text or precede it with a single quote for each cell in selection if isnumber(cell.value) then cell.value = "'" & cell.value end if Next -- Regards, Tom Ogilvy "xlcharlie" wrote: The code causing me grief is as follows: Dim acct as string, subacct as string acct = CStr(Trim(Cells(i, clm_acct).Value)) subacct = CStr(Evaluate("=VLOOKUP(" & acct & ",'" & ThisWorkbook.Name & "'!mapping,2,FALSE)")) The referenced account numbers (variable acct) appear as text in the first column of a table. Most of the account numbers are all numeric values, e.g. 345999, but some are a combination of numbers and letters, e.g. 345ABC. When I call the procedure as is, I get an error, Error 2015, whenever acct equals a combination of numbers and letters (345ABC). If I add quotation marks to the function, e.g. Evaluate("=VLOOKUP(""" & acct & """,'" & ThisWorkbook.Name & "'!mapping,2,FALSE)")), I no longer get an error with the alphanumeric account numbers, but all the numeric only values (345999) now result in an error. While these two lines of code are the first instance of this problem in my procedure, I use the Evaluate("=VLOOKUP()) in a number of places. I'm looking for a solution that will save me a bunch of extra lines of code. One workaround would be to add IF(ISERROR(() to the Evaluate(), but then I will have to add that in every instance and it could really start to slow down the procedure, plus make the code itself much larger. I thought if the cell values were stored in text format and the acct variable was a string variable, the vlookup would work for alpha and numeric values alike, but it seems I was wrong. Does anyone know another solution, either with code or by possibly changing the set up of the referenced worksheet? Thanks, Ben |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Quotation Marks as Text | Excel Discussion (Misc queries) | |||
Generating text surrounded by quotation marks | Excel Discussion (Misc queries) | |||
After exporting how do I remove quotation marks from text cells | Excel Discussion (Misc queries) | |||
Passing string arguments that have quotation marks in them | Excel Programming | |||
How do I concatenate text that actually contains quotation marks? | Excel Worksheet Functions |