Match function to find the cell entered today's date-CodePudding

I am trying to use the match function the locate the cell entered the date of today. Today's date is 22 April 2022, I need the Macro to return the value as 4 in below excel sheet. Below are my codes, and it shows the error message "Run-time error '1004': Unable to get the Match property of the WorksheetFunction class." Can you please help me to fix the codes? Many thanks

Sub FindDate()

Dim FindDate As Long

FindDate = WorksheetFunction.Match(Date, Sheet1.Rows(1), 0)

Debug.Print FindDate


End Sub

CodePudding user response：

WorksheetFunction.Match will throw an error if it doesn't find a match. And that is what is happening here:

If you search for Date it doesn't find the value because dates in Excel are stored in the cell as number of days since 1900-01-01 and the value is of type Long (or Double if it contains a time). Therefore you need to search for CDbl(Date) if you look for dates.

Sub FindDate()
    Dim FindDate As Long
    
    On Error Resume Next  'hide error message if match doesn't find anything
    FindDate = WorksheetFunction.Match(CDbl(Date), Sheet1.Rows(1), 0)
    On Error Goto 0  're-activate error reporting !!!

    If FindDate > 0 Then
        Debug.Print FindDate
    Else
        MsgBox "Date was not found!"
    End If
End Sub

CodePudding user response：

Match is a member of the WorksheetFunction class, but you need to call it with an object. Use

FindDate = Application.Match(Date, Sheet1.Rows(1), 0)