VBA vlookup error depending on text string & quotation marks

[Guest]

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

 

[Guest]

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

 

[Guest]

Thanks Tom. Adding the apostrophe did the trick.