带变量的 VBA FormulaR1C1 语法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19258494/
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
VBA FormulaR1C1 syntax with variable
提问by mauek unak
i'm trying to write formula with 2 variables to cell. formula in cell should be:
我正在尝试将带有 2 个变量的公式写入单元格。单元格中的公式应为：
=(SUM('C:\Users\[Excel.xlsm]Sheet1'!H:H)-SUM('C:\Users\[Sheet1.xlsm]Sheet1'!I:I))
i want use path to file as variable, as well sheet name.
我想使用文件路径作为变量，以及工作表名称。
path = C:\Users\Excel.xlsm 'from msofiledialog
sheetname = Sheet1
what am i missing ?
我错过了什么？
Cells(1, 1).FormulaR1C1 = "=(SUM('[" & Path & "] " & sheetname & " '!C8) _
-SUM('[" & Path & "] " & sheetname & " '!C9))
采纳答案by mauek unak
thanks, this worked for me :
谢谢，这对我有用：
Sub main
Dim LastRow as String
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Dim path as String
Path = "C:\users\username\Desktop\"
Dim filename as String
Filename = "Excel.xlsm"
Dim sheetname as String
sheetnameCR = "CR_" & supname
Dim myrangeH as String
Dim myrangeI as String
myrangeH = ("H5:H" & LastRow)
myrangeI = ("I5:I" & LastRow)
Cells(1, 1).Formula = "=SUM('" & Path & "[" & Filename & "]" & sheetnameCR & "'!" & myrangeH & ")" & "-SUM('" & Path & "[" & Filename & "]" & sheetnameCR & "'!" & myrangeI & ")"
End Sub
i had to add "RangeH" variable, because otherwise excel took cell C8, instead of column H, which i wanted. that's great, but even after i give him full path to desired cell, excel is still asking me for path to excel with FileDialogOpen. any idea why ?
我不得不添加“RangeH”变量，因为否则excel采用单元格C8，而不是我想要的H列。太好了，但即使在我给他提供了所需单元格的完整路径之后，excel 仍然要求我使用 FileDialogOpen 获取 excel 的路径。知道为什么吗？
