CODEWITHSUNDEEP
vbaexceldatevlookup
Vietson
Vietson

Reputation: 23

Error 2042 When Using Vlookup To Find Date in VBA

I am trying to write code that looks up a batch ID based on the date entered in the "TSDate" field.

I keep getting error 2042 when trying to use the Application.VLookup Function in VBA:

'Timesheet Date

Private Sub TSdate_KeyDown(ByVal KeyCode As _
MSForms.ReturnInteger, ByVal shift As Integer)

Dim TimesheetDate As Date
Dim batch As Variant
Dim DRange As Range
Set DRange = Range("Table_PayPeriods")

If KeyCode = 13 Or KeyCode = 9 Then
    TSDate.Value = Format(TSDate.Value, "dd-mmm-yy")
    TimesheetDate = TSDate.Value
    batch = Application.VLookup(TSDate.Value, DRange, 2, 0)
    MsgBox (DRange(2, 2))
    BatchID.Text = batch
End If
End Sub

The messagebox proves that the data being looked up is being pulled properly, the problem is I am getting the error in the "batch" variable.

Any help would be appreciated. Thanks!

Upvotes: 2

Views: 3729

Answers (1)

David Zemens
David Zemens

Reputation: 53623

Application.VLookup will return Error 2042 when the lookup value is not found.

You need to test for error, and handle appropriately:

If KeyCode = 13 Or KeyCode = 9 Then
    TSDate.Value = Format(TSDate.Value, "dd-mmm-yy")
    TimesheetDate = TSDate.Value
    batch = Application.VLookup(TSDate.Value, DRange, 2, 0)
    If IsError(batch) Then
        'Do something
    Else
        MsgBox (DRange(2, 2))
        BatchID.Text = batch
    End If

End If

As for why the value is not found, it's not possible to answer without more detail from you regarding the input data and respective formats -- perhaps the value really doesn't exist, or perhaps it appears to exist but really does not: (generally I would expect a string will not match a date type and vice-versa).

If the cells contain Date type values (even if they are formatted to look like strings, an Error is expected. In this case, convert the string input (TSDate.Value) to a Date type, and convert that to a Long type and do the Vlookup with its long numeric equivalent:

batch = Application.VLookup(CLng(CDate(TSDate.Value)), DRange, 2, 0)

You'll still need Error handling in the event that the date value literally doesn't exist in the table.

Upvotes: 4

Related Questions