VBA WorksheetFunction to match date

[antaeusguy]

I'm using Excel 2010.

I've created a Table where the first column consist of dates in dd.mm.yyyy format from 01.01.2011 to 31.12.2011.

I'm using VBA to match today's date with the table to obtain the row where today's date contains:

Sub HighlightTodayOverview()
    Dim Today As Date
    Dim TodayRow As Integer
    
    Today = Date
    TodayRow = Application.WorksheetFunction.Match(Today, Range("Table1[Date]"), 0)
    
    Debug.Print TodayRow
End Sub

However, I got this error message:

Run-time error '1004'
Unable to get the Match propertyof the WorksheetFunction class

Does anyone know what went wrong with the code?

 

[GTO]

Try:

Range("Table1!Date")

 

[Peter_SSs]

I wouldn't use a worksheet function for this. Try either of these.

Sub HighlightTodayOverview()
    Dim Today As Date
    Dim TodayRow As Integer
    
    Today = Date
    TodayRow = Range("Table1[Date]").Find(What:=Today).Row

    Debug.Print TodayRow
End Sub

Sub HighlightTodayOverview2()
    Dim TodayRow As Integer
    
    With Range("Table1[Date]")
        TodayRow = .Row + DateDiff("d", .Cells(1).Value, Date)
    End With
    
    Debug.Print TodayRow
End Sub