Using WorksheetFunction.VLookUp To Look Up A Date In VBA

VLOOKUP is exposed in VBA via the WorksheetFunction class and works much the same as it does when you use it in a worksheet, but one thing you have to be careful of is when you have a date as the lookup_value. Let’s use the data from a previous blog post to demonstrate the problem:

The formula in cell I2 clearly works without issue, but when we replicate it in VBA we may get this error:

Run-time error ‘1004’: Unable to get the VLookup property of the WorksheetFunction class.

This error means that it can’t find the lookup_value in the lookup column (equivalent of #N/A) and, in this case, we will get this error whether we take the lookup_value date directly from the H2 cell using Range.Value or if we use a Date variable. You can try running the two procedures below to see this for yourself.

Sub Error_Example1()

    Const blnRANGE_LOOKUP As Boolean = False
    Const lngCOL_INDEX_NUM As Long = 2
    
    Dim varResult As Variant
    Dim rngLookUpValue As Range
    Dim rngTableArray As Range
    
    With Sheet1
        Set rngLookUpValue = .Range("H2")
        Set rngTableArray = .Range("E2:F11")
    End With
    
    'Runtime Error 1004
    varResult = Application.WorksheetFunction.VLookup( _
                                                rngLookUpValue.Value, _
                                                rngTableArray, _
                                                lngCOL_INDEX_NUM, _
                                                blnRANGE_LOOKUP)

End Sub

Sub Error_Example2()

    Const blnRANGE_LOOKUP As Boolean = False
    Const lngCOL_INDEX_NUM As Long = 2
    Const dteLOOKUP_VALUE As Date = #1/3/2012# '3rd Jan 2012
    
    Dim varResult As Variant
    Dim rngTableArray As Range
    
    Set rngTableArray = Sheet1.Range("E2:F11")
    
    'Runtime Error 1004
    varResult = Application.WorksheetFunction.VLookup( _
                                                dteLOOKUP_VALUE, _
                                                rngTableArray, _
                                                lngCOL_INDEX_NUM, _
                                                blnRANGE_LOOKUP)

End Sub

To understand why there is a problem we have to put on both our Excel and VBA hats. In the previous post, Why Does VLOOKUP Return An #N/A Error?, we saw that VLOOKUP is data type sensitive: for example, VLOOKUP considers the string “3” to be different to the number 3. The post also mentioned that Excel worksheets can only contain five data types: numbers (also known as doubles), text (also known as strings), logicals (also known as booleans), error values and arrays. You’ll notice that Excel does not have a Date data type: in Excel, dates and times are numbers which have just been formatted to look like dates. In VBA, however, there is a Date data type and, in this case, when we use the Range.Value property to retrieve the value from the cell H2, it returns a Date type. VLOOKUP is unable to match this Date type to the numbers (formatted as dates) the the lookup column, which is why we get the error.

There are a number of workarounds to the issue. Essentially we have to make sure that the lookup_value data type matches the data types in the lookup column. Since there’s only one lookup_value it makes sense that we concentrate on changing its type rather than anything else.

If you are reading the date lookup_value from a cell then you can either just pass the range reference into VLOOKUP (exclude the .Value), or you can use the Range.Value2 property instead of the Range.Value property. The Range.Value2 property doesn’t use the Date data type so, by using Range.Value2 instead, we get a Double data type holding the value 40911. VLOOKUP will be able to process that quite happily. Another option would be to change the formatting of the cell H2 to that which represents a number but it would not be a very robust solution. Here’s the first error example re-written so that it uses the Range.Value2 property:

Sub Correct_Example1()
    
    Const blnRANGE_LOOKUP As Boolean = False
    Const lngCOL_INDEX_NUM As Long = 2
    
    Dim varResult As Variant
    Dim rngLookUpValue As Range
    Dim rngTableArray As Range
    
    With Sheet1
        Set rngLookUpValue = .Range("H2")
        Set rngTableArray = .Range("E2:F11")
    End With
    
    'correctly returns 0.957583452240653
    varResult = Application.WorksheetFunction.VLookup( _
                                                rngLookUpValue.Value2, _
                                                rngTableArray, _
                                                lngCOL_INDEX_NUM, _
                                                blnRANGE_LOOKUP)
    
End Sub

Yet another option, which will work whether you are picking up the date from a cell or using a Date variable, is to explicitly convert the Date into a Double or Long data type using the CDbl or CLng functions. Note that Long data types would apply for whole dates only. Here’s the second error example re-written with the CDbl function included:

Sub Correct_Example2()
    
    Const blnRANGE_LOOKUP As Boolean = False
    Const lngCOL_INDEX_NUM As Long = 2
    Const dteLOOKUP_VALUE As Date = #1/3/2012#  '3rd Jan 2012
    
    Dim varResult As Variant
    Dim rngTableArray As Range
    
    Set rngTableArray = Sheet1.Range("E2:F11")
    
    'correctly returns 0.957583452240653
    varResult = Application.WorksheetFunction.VLookup( _
                                                CDbl(dteLOOKUP_VALUE), _
                                                rngTableArray, _
                                                lngCOL_INDEX_NUM, _
                                                blnRANGE_LOOKUP)
    
End Sub