VLookup in VB returning Error 2042 where VLookup in worksheet returns expected

I'm trying to write a function that will lookup a value in a table based on a matching date. In the event of no match it will lookup up to 7 days prior before returning an error.

Option Explicit
Public Function GetWeekEndBalanceFromStatement(lookupDate As Date, statement As Range, column As Integer) As Variant
    Dim lookupModifier As Integer

    Do While IsError(Application.VLookup(DateAdd("d", lookupModifier, lookupDate), statement, column, False)) And lookupModifier > -7
        lookupModifier = lookupModifier - 1
    Loop

    GetWeekEndBalanceFromStatement = Application.VLookup(DateAdd("d", lookupModifier, lookupDate), statement, column, False)
End Function

The issue I'm having when I break it down for debugging is the VLookups consistently return Error 2042, even in the event an identical VLookup written in the spreadsheet returns a value.

The written function calls for reference, K8 being the first line:

=DATE(2018,1,5)
=VLOOKUP(K8, A:F, 6,FALSE )
=GetWeekEndBalanceFromStatement(K8,A:F,6)

Any clues as to what I may be doing wrong? I've attempted encapsulating dates in CDate, appending .Value to ranges and bringing in date as a variant and accessing .Value2.

1 answer

  • answered 2018-01-13 17:44 Shai Rado

    After running some tests, I recall some issues I faced when using VLookup and Match with Dates, better use their Excel values (change Date to Double).

    Modified Function Code

    Option Explicit
    
    Public Function GetWeekEndBalanceFromStatement(lookupDate As Double, statement As Range, column As Integer) As Variant
        Dim lookupModifier As Integer
    
        Do While IsError(Application.VLookup(lookupDate + lookupModifier, statement, column, False)) And lookupModifier > -7
            lookupModifier = lookupModifier - 1
        Loop
    
        GetWeekEndBalanceFromStatement = Application.VLookup(lookupDate + lookupModifier, statement, column, False)
    End Function