VLookup does not work with "("

[Guest]

I am trying to use a macro to search for data containing "(". Sample code
below:
***************
Sub test()
Return_Value = WorksheetFunction.VLookup("(555)555-5555",
Worksheets(1).Range("B3:C15"), 2, True)
End Sub

For whatever reason I get error 1004, VLookup cannot read the lookup_value.

Thanks,

Drgn_Btl

 

[Guest]

This works fine on my PC. Seems OK to me.
The only way I can generate that error is if
(555)555-5555
is not in any of the cells b3 to b15.
Double check it is really there. Copy paste it from your program text.
Check that what you have got is not some kind of number being reformatted.

 

[PCLIVE]

Maybe on your worksheet you have a space between the area code parenthesis
and the phone number prefix. In your code, there is no space.

 

[Guest]

Thanks for the reply. That is the issue, and unfortunately I do not know in
advance if the lookup_value is in the list or not. The function should
return the next closest match if the value is not in the list. For what ever
reason it does not seem to work with the "(".

 

[Harlan Grove]

Drgn_Btl wrote...

I am trying to use a macro to search for data containing "(". Sample code
below: ....
Sub test()
Return_Value = WorksheetFunction.VLookup("(555)555-5555", _
Worksheets(1).Range("B3:C15"), 2, True)
End Sub

For whatever reason I get error 1004, VLookup cannot read the lookup_value.

This would happen when the phone numbers in the leftmost column of your
table were all NUMBERS just formatted as phone numbers. If you select
the B3:B15 range and set the AutoSum function in the Status Bar to
Count Nums, does the AutoSum show a result > 0? If so, at least some of
your phone numbers are formatted numbers. The lookup value you're using
as the 1st argument to VLOOKUP is TEXT. In Excel, text and numbers that
may look the same are nevertheless different. Try making your 1st
argument a number.

Return_Value = WorksheetFunction.VLookup(5555555555#, _
Worksheets(1).Range("B3:C15"), 2, True)

The # at the end of the number marks it as type Double since it exceeds
the long integer bounds but looks like an integer.