vba 如何通过VBA引用单元格中提到的另一个工作表的单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41949897/
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
How to refer a cell of another sheet mentioned in a cell through VBA
提问by Jignesh Bavishi
I am having a excel file named "Book1" with "Sheet1" and "Sheet2". In Sheet1, Cell Value of A1 is "'[Book1.xlsb]Sheet2'!$A$14", which is derived as some formula result. I want cell reference mentioned in cell A1 to be selected through VBA.
我有一个名为“Book1”的 Excel 文件，其中包含“Sheet1”和“Sheet2”。在Sheet1中，A1的单元格值为“'[Book1.xlsb]Sheet2'!$A$14”，这是由某个公式得出的结果。我希望通过 VBA 选择单元格 A1 中提到的单元格引用。
I have put VB Code as
我已经把VB代码作为
Range(Range("A1")).Select or Range([Indirect("A1")]).Select
This code works only when cell referred in A1 is in same sheet, but it doesn't work if cell referred is in different sheet
此代码仅在 A1 中引用的单元格在同一工作表中时有效，但如果引用的单元格在不同工作表中则不起作用
Can someone help to solve this please
有人可以帮忙解决这个问题吗
采纳答案by user3598756
you can do either way of following ones:
您可以执行以下任一方式：
With Worksheets("Sheet2")
.Activate
.Range([Indirect("Sheet1!A1")]).Select
End With
or
或者
With Worksheets("Sheet2")
.Activate
.Range(Worksheets("Sheet1").Range("A1")).Select
End With
回答by Shai Rado
Since both worksheets ("Sheet1" and "Sheet2") are in the same workbook, the value in "Sheet1" Cell A1 should be "Sheet2!$A$14".
由于两个工作表（“Sheet1”和“Sheet2”）都在同一个工作簿中，“Sheet1”单元格 A1 中的值应该是“Sheet2!$A$14”。
The code below is a little longer then it needs to be, but I wanted to go through all the steps to explain better.
下面的代码比它需要的要长一点，但我想通过所有步骤来更好地解释。
I am using the Splitfunction to seperate the Worksheetname and the Range.Address, and put the result in 2 array elements.
我正在使用该Split函数将Worksheet名称和分开Range.Address，并将结果放入 2 个数组元素中。
Afterwards, you retrieve the first array RngStringelement as the worksheet's name, and then second array element is the range.address.
之后，您检索第一个数组RngString元素作为工作表的名称，然后第二个数组元素是 range.address。
Code
代码
Option Explicit
Sub SelectRange()
Dim Rng As Range
Dim RngString As Variant
Dim ShtName As String
Dim RngAddress As String
RngString = Split(Worksheets("Sheet1").Range("A1").Value, "!")
' sheet name is the first array element
ShtName = RngString(0)
' Range address is the second array element
RngAddress = RngString(1)
' setting the Rng object
Set Rng = Worksheets(ShtName).Range(RngAddress)
' since your range is in another worksheet, you need to activate it first
Worksheets(ShtName).Activate
' select the range
Rng.Select
End Sub
