Unable to lookup a date using vba vlookup

4
votes

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.

1
excelvlookupvba
I am pretty sure that message is happening because the VLookup function isn't finding anything. – nemmy

1 Answers

13
votes

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)