Match today's week with a week in a range of dates-CodePudding

How do I match the cell with todays week in the week of a range of Dates? In row 5 are the Dates, this row will be hidden (FIND is not possible to use) Today is the 13th august. It is in the same week as the 15th august. (see picture) D5 would be the answer.

I tried:

Dim rngFound As Range
Dim TodaysWeek As Integer

    TodaysWeek = Application.WorksheetFunction.WeekNum(Date, vbMonday)
    
    Set rngFound = .Cells(Application.WorksheetFunction.Match(TodaysWeek, Application.worksheetfunktion.WeekNum(DateRange, vbMonday), 0))

I prefer not to use a helper row with the week number. But if there is no easy way with VBA to find it, I can use it. In this case I know how to do it.

I found a solution. See my answer below.

CodePudding user response：

Simplified to avoid filter Requirement

This formula will always return the Monday of the current week:

=TODAY() (2-WEEKDAY(TODAY()))

Use a