Macro to activate cell whose address is referenced

Question

I have an array of Months and Years (formatted Jan-16, Mar-17, etc) in a single row. I have a formula that will find the current month (based on the month and year I select in a drop-down menu) and spit out the address of that given month and year:

=CELL("address",INDEX(E3:BL3,MATCH(D1,E3:BL3,0))).

Works like a charm. Now I need to know how I can program a macro to look at the cell that contains the address and activate the cell whose address I am referencing.

So, for example, if the address that it spits out is Z3, the macro will then go and activate cell Z3. It needs to be dynamic because every month I will be referencing a new month and thus a new cell.

Answer 1

Assuming that your drop down is linked to cell A1 then you can fire an event on a change on that cell that will move focus to cell address indicated in your formula (I'm assuming here B1). You would place on the Workbook_SheetChange event the following code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.Address = "$A$1" Then
        Dim rngAddress As Range
        Set rngAddress = ThisWorkbook.Sheets(1).Range("B1") 'Location of your address formula
        ThisWorkbook.Sheets(1).Range(rngAddress.Value2).Select
    End If
End Sub