Vba: Application.VLookup doesn't work with numeric values (error 2042)

[Nelson78]

Hello everybody.

I have:

For Each cell In sheets(1).Range("E2:E" & len)
                    result1 = Application.VLookup(cell, rng1, 3, False)
                    result2 = Application.VLookup(cell, rng1, 17, False)
                    result3 = Application.VLookup(cell, rng1, 18, False)
Next cell

The value of cell can be:
1) Alphanumeric: in this case no problem;
2) Numeric: in this case result1, result2 and result3 are not calculated (error 2042).

Could you help me?

Thank's.

 

[Joe4]

VLOOKUP will work for either alphanumeric or numeric values.
The restriction is the values you are matching on MUST be the exact same format/data type.
Match only works when matching on:
- numeric entries matching to other numeric entries
- string/text entries matching to other string/text entries

If you try to match a numeric entry to a "number entered as text" (usually evident by the entry being left-justified in the cell), it will NOT work.
This is the single biggest issue people have with VLOOKUP, trying to compare different data types. You need to standardize the data first.