vba 引用另一个工作表中的单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22212918/
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
Referencing cell in another worksheet
提问by user3386075
I can write the correct formula in Excel, but because I have so many IFstatements, I have to put it into VBA. The formula in Excel is supposed to return a value from another worksheet ("Sheet2") based on a value (F5) selected on "Sheet1". Here is part of the Excel formula that was created (there are many more IFStatements):
我可以在Excel中写出正确的公式，但是因为我的IF语句太多，所以我不得不把它放到VBA中。Excel 中的公式应该根据在“Sheet1”上选择的值 (F5) 从另一个工作表 (“Sheet2”) 返回一个值。这是创建的 Excel 公式的一部分（还有更多IF语句）：
IF($F="AOM",
OFFSET('Sheet2'!B3,'Sheet2'!$B,1,1,1),
IF($F = "Mid Adj",
OFFSET('Sheet2'!B3,'Sheet2'!$B,6,1,1),
""
)
)
Here is the If Statementpart of the VBA I've created:
这是我创建的 VBA的If 语句部分：
Private Sub Worksheet_Change(ByVal Target As Range)
Dim dummyVar As String
If Target = Range("F5") Then
If Range("F5").Text = "AOM" Then
dummyVar = ProcAOM()
ElseIf Range("F5").Text = "Mid Adj" Then
dummyVar = ProcML()
For the Proc, I'm not sure how to set up the Offset formula that references another worksheet.
对于 Proc，我不确定如何设置引用另一个工作表的偏移公式。
回答by PatricK
From what I can understand you are trying to achieve, this will be just for 1 cell? If so, you don't have to care about Worksheet_Change.
据我所知，您正在努力实现这一目标，这仅适用于 1 个单元格？如果是这样，您就不必关心 Worksheet_Change。
You can create a User Defined Function (UDF) for this purpose. Say BigIFwith assumptions:
您可以为此创建用户定义函数 (UDF)。BigIF用假设说：
- The Reference Cell does not change (B3 of Sheet2)
- Row Offset is defined in a fixed cell (B1 of Sheet2)
- Column Offset is determined in the code
- Returns the value of the range offset from Reference Cell
- 参考单元格不变（Sheet2 的 B3）
- 行偏移在固定单元格中定义（Sheet2 的 B1）
- Column Offset 在代码中确定
- 返回参考单元格的范围偏移值
Paste below code into a module and use it in worksheet just like a formula, but referencing the F5 of Sheet1 (doing this forces Excel to recalculate when F5 changes). ie. =BigIf('Sheet1'!$F$5)
将下面的代码粘贴到一个模块中，并像公式一样在工作表中使用它，但引用 Sheet1 的 F5（这样做会强制 Excel 在 F5 更改时重新计算）。IE。=BigIf('Sheet1'!$F$5)
Function BigIF(oRng As Range) As Variant
Dim oWS As Worksheet, oRngRef As Range
Dim lRowOffset As Long, lColOffset As Long, sID As String
Set oWS = ThisWorkbook.Worksheets("Sheet2")
Set oRngRef = oWS.Range("B3") ' Offset Reference
sID = oRng.Text ' Sheet1!F5 value
' ROW OFFSET: Sheet2!$B value
lRowOffset = CLng(oWS.Range("B1").Value)
' COLUMN OFFSET: based on sID
Select Case sID
Case "AOM": lColOffset = 1
Case "Mid Adj": lColOffset = 6
'... Other Cases
Case Else: lColOffset = 0
End Select
BigIF = oRngRef.Offset(lRowOffset, lColOffset)
Set oRngRef = Nothing
Set oWS = Nothing
End Function
Of cause you can also use the Worksheet_Change event method, but more code.
当然，您也可以使用 Worksheet_Change 事件方法，但需要更多代码。
