vba 在excel中从另一个单元格中查找单元格的单元格引用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9055754/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
find cell reference of a cell from another cell in excel
提问by Jonathan Lyon
I have a worksheet that returns a value from a different sheet in the sameworkbook (=data!C68) which returns a value.
我有一个工作表，它从同一个工作簿 (=data!C68) 中的不同工作表返回一个值，该工作表返回一个值。
Ina another cell I want to reference the reference in the original cell but increment the row by 1 - (=data!C69). The cells are not next to each other.
在另一个单元格中，我想引用原始单元格中的引用，但将行增加 1 - (=data!C69)。单元格彼此不相邻。
Is this possible either with a function or a fromula?
这是否可以通过函数或 fromula 实现？
Thanks
谢谢
Jonathan
乔纳森
回答by Siddharth Rout
There are many ways to achieve this. Here is one way. I have extended the function by incorporating the rows and columns that you want to Offset. Paste this code in a module
有很多方法可以实现这一目标。这是一种方法。我通过合并您想要偏移的行和列来扩展该功能。将此代码粘贴到模块中
Syntax
句法
=GetOffset(Range,Row,Col)
=GetOffset（范围，行，列）
Remarks
评论
Range(Required) is the cell which has the formula
范围（必填）是具有公式的单元格
Row(Optional) Row(s) you want to offset
行（可选）要偏移的行
Col(Optional) Column(s) you want to offset
Col（可选）要偏移的列
Sample Usage
示例用法
=GetOffset(A1) : This will pickup the value from the same cell which A1 is referring to. If A1 is referring to =data!C69then GetOffsetwill get value from data!C69
=GetOffset(A1) ：这将从 A1 所指的同一单元格中获取值。如果 A1 是指，=data!C69则GetOffset将从数据中获取值！C69
=GetOffset(A1,1) : This will pickup the value from the next cell (1 row down) which A1 is referring to. If A1 is referring to =data!C69then GetOffsetwill get value from data!C70
=GetOffset(A1,1) ：这将从 A1 所指的下一个单元格（向下 1 行）中提取值。如果 A1 是指，=data!C69则GetOffset将从数据中获取值！C70
=GetOffset(A1,1,1) : This will pickup the value from the next cell (1 row down across 1 Col) which A1 is referring to. If A1 is referring to =data!C69then GetOffsetwill get value from data!D70
=GetOffset(A1,1,1) ：这将从 A1 所指的下一个单元格（跨 1 列向下 1 行）中提取值。如果 A1 是指，=data!C69则GetOffset将从数据中获取值！D70
=GetOffset(A1,0,1) : This will pickup the value from the next cell (Same row across 1 Col) which A1 is referring to. If A1 is referring to =data!C69then GetOffsetwill get value from data!D69
=GetOffset(A1,0,1) ：这将从 A1 所指的下一个单元格（跨 1 Col 的同一行）中获取值。如果 A1 是指，=data!C69则GetOffset将从数据中获取值！D69
CODE
代码
Public Function GetOffset(Rng As Range, Rw As Long, Col As Long) As Variant
Dim MyArray() As String, strTemp As String
Dim TempRow As Long, TempCol As Long
On Error GoTo Whoa
Application.Volatile
MyArray = Split(Rng.Formula, "!")
TempRow = Range(MyArray(UBound(MyArray))).Row
TempCol = Range(MyArray(UBound(MyArray))).Column
TempRow = TempRow + Rw
TempCol = TempCol + Col
strTemp = MyArray(0) & "!" & Cells(TempRow, TempCol).Address
GetOffset = Application.Evaluate(Trim(strTemp))
Exit Function
Whoa:
End Function
回答by shahkalpesh
Option Explicit
Function GetCellReferenceUsedInFormula(ByVal src As Range) As Range
If src.HasFormula Then
Set GetCellReferenceUsedInFormula = Evaluate(Mid(src.Formula, 2))
End If
End Function
And use the above function in formula with OFFSET
并在公式中使用上述函数 OFFSET
=OFFSET(GetCellReferenceUsedInFormula(A1),1,0)
This is assuming that cell A1 contains the formula =data!C68.
Please note that the VBA code assumes that the formula is simple (i.e. =somecellreference).
这是假设单元格 A1 包含公式=data!C68。
请注意，VBA 代码假定公式很简单（即=somecellreference）。
