宏以激活其地址在其他地方引用的单元格 [英] Macro to activate cell whose address is referenced elsewhere

问题描述

我在一行中有一系列的月份和年份（格式为Jan-16，Mar-17等）。我有一个公式可以找到当前月份（根据我在下拉菜单中选择的月份和年份）并吐出给定月份和年份的地址：

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.

例如，如果它发出的地址是Z3，则宏将转到并激活单元格Z3。它必须是动态的，因为每个月我都会引用一个新的月份，因此也要引用一个新的单元格。

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.

推荐答案

down链接到单元格 A1 ，则可以在该单元格上的更改上触发事件，从而将焦点移至公式中指定的单元格地址（我假设此处 B1 ）。您将在 Workbook_SheetChange 事件中放置以下代码：

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

这篇关于宏以激活其地址在其他地方引用的单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！