Unable to find match using Match

[Ark68]

What could be possible reasons why this code is failing to find a match? I get an error (Unable to get the Match property of the WorksheetFunction class) with the line in red.

The value for date_inq = 2024-02-01
In the worksheet (ws_cupe), cell B39 has a value of 2024-02-01 (formatted as 02-Feb-24)

Sub Button1_Click()
    Dim date_today As Date
    Dim date_inq As Date
    Dim r_dateinq As Double
    
    date_today = Now
    date_inq = Date + 1
    
    Debug.Print date_today
    Debug.Print date_inq
    
    With ws_cupe
        r_dateinq = Application.WorksheetFunction.Match(date_inq, .Columns(2), 0)
    End With
    
End Sub

 

[dmt32]

Hi,
untested but see if this update to your code resolves your issue

Sub Button1_Click()
    Dim date_today  As Date
    Dim date_inq    As Long
    Dim r_dateinq   As Variant
    
    date_today = Now
    date_inq = Date + 1
    
    Debug.Print date_today
    Debug.Print date_inq
    
    r_dateinq = Application.Match(date_inq, ws_cupe.Columns(2), 0)
    
    If Not IsError(r_dateinq) Then
        
        'do stuff
        
    Else
        MsgBox date_inq & Chr(10) & "Record Not Found", 48, "Not Found"
        
    End If
    
End Sub

Dave

 

[Ark68]

Hi Dave ... that appears to have worked. Thank you.
Not quite sure why, I'm assuming the data types were not what I thought?

 

[dreid1011]

Hi Dave ... that appears to have worked. Thank you.
Not quite sure why, I'm assuming the data types were not what I thought?

This may answer that question: Application vs. Application.WorksheetFunction

 

[dmt32]

Hi Dave ... that appears to have worked. Thank you.
Not quite sure why, I'm assuming the data types were not what I thought?

Searching for dates can prove tricky but a date is a number & passing it to a variable declared as long data type works most of the time
You will note that I also changed the data type for Variable r_dateinq to variant. This is so the error value can be handled if search value not found.

Appreciate feedback

Dave

 

[Ark68]

Thanks folks.