Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup does not work with "("
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup does not work with "("
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. -- Allllen "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. Thanks, Drgn_Btl |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup does not work with "("
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. "Drgn_Btl" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup does not work with "("
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 "(". "Allllen" wrote: 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. -- Allllen "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. Thanks, Drgn_Btl |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup does not work with "("
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
will vlookup work for me? | Excel Worksheet Functions | |||
vlookup does not work between 71% to 79% refernceing roundup numb | Excel Worksheet Functions | |||
Need Vlookup to work with formula in reference cell | Excel Discussion (Misc queries) | |||
IF / VLOOKUP formula won't work until saved | Excel Worksheet Functions | |||
VLOOKUP won't work | Excel Worksheet Functions |