Excel, VBA, VLookup text value with and without apostrophe

0
votes

After hours of trial and error, I've determined that doing an Application.Vlookup for a text string in a range returns:

  1. Error 2042 if the text in the search range isn't preceded by an apostrophe
  2. Returns a value in the search range if the text is preceded by an apostrophe

Both search variables are type 8, string, but only the range value with the apostrophe is considered equal, even though the string being searched for, does not have an apostrophe in it.

Is there a way to have Vlookup consider text without an apostrophe equal? And no, I'd rather not have to add an apostrophe to everything.

Thanks in advance.

PS Made a small program, using a different version of Excel but still getting undesired results...

Excel Program and VLookup results enter image description here

Excel Macro and it's results enter image description here

In the spreadsheet, all the number values were entered by keyboard. Then used the format cells option to change them to text. Then added an apostrophe to the second text value '478901' as denoted by the green triangle, top left-hand corner of the cell. Vlookup in C2 does not recognize A2 as text, even though it has been formatted as such. Vlookup in C3 does recognize A3 as text and provides the returned value in column 2. In the macro, the output pretty much says it all.
myData range is confirmed by address call. VarType calls for values in A2 & A3 confirms excel is treating them as Double-precision floating-point number (5) and String (8), even though both have been formatted to text, although I suspect this is just for display purposes now. The Application.Vlookup calls for the values in A2 and A3 return same as spreadsheet Vlookup calls. In my earlier post, I said that both vartypes returned string (8) but that only the one with the apostrophe worked. This was with the work version of excel, which is earlier than this one. So it looks like someone tried to fix this issue or it just ended up different without attention. Either way, something is broken. "444" doesn't equal "'444" except in excel and "444" equals "444" everywhere else but in excel.
Looks like I'll need to test for both.

1
excelvbavlookup
Always helps to show the actual data and formulas you used when testing. I've never experienced what you're describing. – Tim Williams
You appear to try using VLOOKUP in VBA. Bear in mind that the leading apostrophe merely converts the cell contents to Text. You wouldn't have the same problem when testing on the worksheet without VBA. Therefore you are right to focus on the lookup value . But you are wrong not to show us your code. Try converting your lookup value to string with Cstr(). – Variatus

1 Answers

1
votes

You've formatted the worksheet cells as "Text", but if you do that after the value has been entered it does not "take": cell contents will be by default left-aligned, but the values are still numeric for the purposes of formulas etc.

Your value with the ' prefix works in the lookup because it's actually text: if you re-enter the values in the "text format" cells (no need to add the apostrophe) then those will also become non-numeric and will be matched by the vlookup.

Eg see below - first 4 rows are "General" format for "Value"; next 4 rows are formatted as "Text" (after the values had been entered).

501 and 506 are both not recognized as numbers because they have a ' prefix.

504 was re-entered after the Text cell format was applied, and so is seen as non-numeric.

505 and 507 are both still seen by Excel as numbers.

enter image description here

Related: https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_winother-mso_2010/a-number-formatted-as-text-is-really-a-number/085bb189-342b-4eed-bc33-2f149c6db244