VBA excel macro for a button that selects named reference from a named range

I have a named range "last_monday", which dynamically calculates one variable, last monday's date ("d-mmm-yy"). I'd like a macro that I can assign to a button which would select the column where last monday's date resides within a broader range of column-wise dates, called "date_range".

E.g. assuming today's date is Monday, 15-Jan-18, the button would select the cell in position BQ5 in the example below.

enter image description here

1 answer

  • answered 2018-01-14 11:45 Variatus

    The function below selects the cell in Date_Range indicated by the value of Last_Monday. If there is no match it does nothing.

    Sub SelectMonday()
    
        Dim C As Long
    
        On Error Resume Next
        C = Application.Match(Range("Last_Monday").Value2, Range("Date_Range"), 0)
        If C Then Range("Date_Range").Cells(C).Select
    End Sub