Unable to lookup a date using vba vlookup
excel, vba, vlookup
Solution
VLookup is sensitive to data types. And your VBA routine is looking for a DATE data type, but such does not exist on an Excel worksheet. So you need to convert your string lookup_value to a data type compatible with what is on the worksheet. This could be a Long (for whole dates) or Double (if you are including times.
Try:
the_day = WorksheetFunction.VLookup(CLng(CDate(lookup_value)), lookup_table, 2, False)
Problem
I have an excel worksheet with column A containing dates and column B containing the respective days. Eg. A2=01/01/14 and B2=Wed and so on. Im trying to retrieve the days in B2 with a sample vba code, but I keep getting an error that "Run-time error '1004' unable to get the Vlookup property of the worksheetFunction class" Pls see my code and advise what is wrong. Thanks. ``` Sub Button1_Click() Dim lookup_value As String Dim lookup_table As Range Let lookup_value = "1/1/2014" Set lookup_table = Range("A:B") the_day = WorksheetFunction.VLookup(CDate(lookup_value), lookup_table, 2, False) Range("D7") = the_day End Sub ``` Pls note that I want to pass lookup_value manually rather than looking up cell A2.