vba 获取Excel INDEX函数找到的值的单元格引用

Get the cell reference of the value found by Excel INDEX function

提问by antonio

The Problem

问题

Assume that the active cellcontains a formula based on the INDEXfunction:

假设活动单元格包含一个基于INDEX函数的公式：

=INDEX(myrange, x,y)   

I would like to build a macro that locates the value found value by INDEXand moves the focus there, that is a macro changing the active cellto:

我想构建一个宏来定位找到的值INDEX并将焦点移动到那里，这是一个将活动单元格更改为：

Range("myrange").Cells(x,y)

Doing the job without macros (slow but it works)

在没有宏的情况下完成工作（缓慢但有效）

Apart from trivially moving the selection to myrangeand manually counting xrows yand columns, one can:

除了简单地将选择移动到行和列myrange并手动计算x行y和列之外，还可以：

1. Copy and paste the formula in another cell as follows:

   =CELL("address", INDEX(myrange, x,y))
   

   (that shows the address of the cell matched by INDEX).

2. Copy the result of the formula above.

3. Hit F5, Ctrl-V, Enter(paste the copied address in the GoTodialog).

1. 将公式复制并粘贴到另一个单元格中，如下所示：

   =CELL("address", INDEX(myrange, x,y))
   

   （显示与 INDEX 匹配的单元格的地址）。

2. 复制上面公式的结果。

3. 点击F5, Ctrl-V, Enter（在GoTo对话框中粘贴复制的地址）。

You are now located on the very cell found by the INDEXfunction.

您现在位于INDEX函数找到的单元格上。

Now the challenge is to automate these steps (or similar ones) with a macro.

现在的挑战是使用宏自动化这些步骤（或类似步骤）。

Tentative macros (not working)

暂定宏（不工作）

Tentative 1

暂定1

WorksheetFunction.CELL("address", ActiveCell.Formula)

It doesn't work since CELLfor some reason is not part of the members of WorksheetFunction.

它不起作用，因为由于CELL某种原因它不是WorksheetFunction.

Tentative 2

暂定2

This method involves parsing the INDEX-formula.

此方法涉及解析INDEX-formula。

Sub GoToIndex()
Dim form As String, rng As String, row As String, col As String

    form = ActiveCell.Formula
    form = Split(form, "(")(1)
    rng = Split(form, ",")(0)
    row = Split(form, ",")(1)
    col = Split(Split(form, ",")(2), ")")(0)
    Range(rng).Cells(row, CInt(col)).Select

End Sub

This method actually works, but only for a simple case, where the main INDEX-formula has no nested subformulas.

这种方法实际上有效，但仅适用于主INDEX公式没有嵌套子公式的简单情况。

Note

笔记

Obviously in a real case myrange, xand ycan be both simple values, such as =INDEX(A1:D10, 1,1), or values returned from complex expressions. Typically x, yare the results of a MATCHfunction.

显然，在真实情况下myrange，x和y既可以是简单的值，例如，也可以是 =INDEX(A1:D10, 1,1)从复杂表达式返回的值。通常x，y是MATCH函数的结果。

EDIT

编辑

It was discovered that some solutions do not work when myrangeis located on a sheet different from that hosting =INDEX(myrange ...).

发现当myrange位于与托管不同的工作表上时，某些解决方案不起作用=INDEX(myrange ...)。

They are common practice in financial reporting, where some sheets have the main statements whose entries are recalled from others via an INDEX+MATCHformula.

它们是财务报告中的常见做法，其中一些表具有主要报表，其条目通过INDEX+MATCH公式从其他表中调出。

Unfortunately it is just when the found value is located on a "far" report out of sight that you need more the jump-to-the-cell function.

不幸的是，只有当找到的值位于看不见的“远”报告上时，您才需要更多的跳转到单元格功能。

采纳答案by V.B.

The task could be done in one line much simpler than any other method:

该任务可以在一行中完成，比任何其他方法都简单得多：

Sub GoToIndex()
    Application.Evaluate(ActiveCell.Formula).Select
End Sub

Application.Evaluate(ActiveCell.Formula)returns a range object from which the CELL function gets properties when called from sheets.

Application.Evaluate(ActiveCell.Formula)返回一个范围对象，当从工作表调用时，CELL 函数从中获取属性。

EDITFor navigating from another sheet you should first activate the target sheet:

编辑要从另一个工作表导航，您应该首先激活目标工作表：

Option Explicit
Sub GoToIndex()
    Dim r As Range
    Set r = Application.Evaluate(ActiveCell.Formula)
    r.Worksheet.Activate
    r.Select
End Sub

Add error handling for a general case:

为一般情况添加错误处理：

Option Explicit
Sub GoToIndex()
    Dim r As Range
    On Error Resume Next ' errors off
    Set r = Application.Evaluate(ActiveCell.Formula) ' will work only if the result is a range
    On Error GoTo 0 ' errors on
    If Not (r Is Nothing) Then
        r.Worksheet.Activate
        r.Select
    End If
End Sub

回答by lori_m

There are several approaches to select the cell that a formula refers to...

有几种方法可以选择公式引用的单元格...

Assume the active cell contains: =INDEX(myrange,x,y).

假设活动单元格包含：=INDEX(myrange,x,y)。

From the Worksheet, you could try any of these:

从工作表中，您可以尝试以下任何一种：

• Copy the formula from the formula bar and paste into the name box (to the left of the formula bar)
• Define the formula as a name, say A. Then type Ainto the Goto box or (name box)
• Insert hyperlink > Existing File or Web page > Address: #INDEX(myrange,x,y)
• Adapt the formula to make it a hyperlink: =HYPERLINK("#INDEX(myrange,x,y)")

• 从公式栏中复制公式并粘贴到名称框中（在公式栏的左侧）
• 将公式定义为名称，例如A。然后A在转到框或（名称框）中输入
• 插入超链接 > 现有文件或网页 > 地址： #INDEX(myrange,x,y)
• 调整公式以使其成为超链接： =HYPERLINK("#INDEX(myrange,x,y)")

Or from the VBA editor, either of these should do the trick:

或者从 VBA 编辑器中，其中任何一个都可以解决问题：

• Application.Goto Activecell.FormulaR1C1
• Range(Activecell.Formula).Select

• Application.Goto Activecell.FormulaR1C1
• Range(Activecell.Formula).Select

Additional Note:

附加说明：

If the cell contains a formula that refers to relative references such as =INDEX(A:A,ROW(),1)the last of these would need some tweaking. (Also see: Excel Evaluate formula error). To allow for this you could try:

如果单元格包含一个引用相对引用的公式，例如=INDEX(A:A,ROW(),1)最后一个，则需要进行一些调整。（另请参阅：Excel 评估公式错误）。为此，您可以尝试：

Range(Evaluate("cell(""address""," & Mid(ActiveCell.Formula, 2) & ")")).Select

This problem doesn't seem to occur with R1C1 references used in Application.Gotoor:

此问题似乎不会发生在Application.Goto或中使用的 R1C1 引用中：

ThisWorkbook.FollowHyperlink "#" & mid(ActiveCell.FormulaR1C1,2)

回答by antonio

Both @lori_m and @V.B. gave brilliant solutions in their own way almost in parallel. Very difficult for me to choose the closing answer, but V.B. even created Dropbox test file, so...

@lori_m 和@VB 几乎同时以自己的方式提供了出色的解决方案。我很难选择结束答案，但 VB 甚至创建了 Dropbox 测试文件，所以......

Here I just steal the best from parts from them.

在这里，我只是从他们那里窃取最好的部分。

'Move to cell found by Index()
Sub GoToIndex()
    On Error GoTo ErrorHandler
    Application.Goto ActiveCell.FormulaR1C1 ' will work only if the result is a range
    Exit Sub
ErrorHandler:
    MsgBox ("Active cell does not evaluate to a range")
End Sub

I associated this "jump" macro with CTRL-j and it works like a charm.

我将这个“跳转”宏与 CTRL-j 关联起来，它的作用就像一个魅力。

If you use balance sheet like worksheets (where INDEX-formulas, selecting entries from other sheets, are very common), I really suggest you to try it.

如果您使用资产负债表之类的工作表（其中INDEX-formulas，从其他表中选择条目非常常见），我真的建议您尝试一下。

回答by Gary's Student

You could use the MATCH() worksheet function or the VBA FIND() method.

您可以使用 MATCH() 工作表函数或 VBA FIND() 方法。

EDIT#1

编辑#1

As you correctly pointed out, INDEXwill return a value that may appear many times within the range, but INDEXwill always return a value from some fixed spot, say

正如您正确指出的那样，INDEX将返回一个可能在该范围内出现多次的值，但INDEX将始终从某个固定位置返回一个值，例如

=INDEX(A1:K100,3,7)

will always give the value in cell G3so the address is "builtin" to the formula

将始终给出单元格G3 中的值， 因此该地址是“内置”到公式中的

If, however, we have something like:

但是，如果我们有类似的东西：

=INDEX(A1:K100,Z100,Z101)

Then we would require a macro to parse the formula and evaluate the arguments.

然后我们需要一个宏来解析公式并评估参数。