vba VBA设置单元格的公式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18744537/
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 setting the formula for a cell
提问by Ryuu
I'm trying to set the formula for a cell using a (dynamically created) sheet name and a fixed cell address. I'm using the following line but can't seem to get it working:
我正在尝试使用（动态创建的）工作表名称和固定单元格地址为单元格设置公式。我正在使用以下行，但似乎无法使其正常工作：
"=" & strProjectName & "!" & Cells(2, 7).Address
Any advice on why this isn't working or a prod in the right direction would be greatly appreciated.
任何关于为什么这不起作用的建议或正确方向的建议将不胜感激。
回答by Stewbob
Not sure what isn't working in your case, but the following code will put a formula into cell A1 that will retrieve the value in the cell G2.
不确定在您的情况下什么不起作用，但以下代码会将公式放入单元格 A1 中，该公式将检索单元格 G2 中的值。
strProjectName = "Sheet1"
Cells(1, 1).Formula = "=" & strProjectName & "!" & Cells(2, 7).Address
The workbook and worksheet that strProjectNamereferences must exist at the time that this formula is placed. Excel will immediately try to evaluate the formula. You might be able to stop that from happening by turning off automatic recalculation until the workbook does exist.
strProjectName放置此公式时，引用的工作簿和工作表必须存在。Excel 将立即尝试计算公式。您可以通过关闭自动重新计算来阻止这种情况的发生，直到工作簿确实存在。
回答by David Zemens
Try:
尝试：
.Formula = "='" & strProjectName & "'!" & Cells(2, 7).Address
If your worksheet name (strProjectName) has spaces, you need to include the single quotes in the formula string.
如果您的工作表名称 ( strProjectName) 有空格，则需要在公式字符串中包含单引号。
If this does not resolve it, please provide more information about the specific error or failure.
如果这不能解决问题，请提供有关特定错误或失败的更多信息。
Update
更新
In comments you indicate you're replacing spaces with underscores. Perhaps you are doing something like:
在评论中，您表示您正在用下划线替换空格。也许你正在做这样的事情：
strProjectName = Replace(strProjectName," ", "_")
But if you're not also pushing that change to the Worksheet.Nameproperty, you can expect these to happen:
但是，如果您还没有将该更改推送到该Worksheet.Name属性，则可以预期会发生以下情况：
- The file browse dialog appears
- The formula returns
#REFerror
- 出现文件浏览对话框
- 公式返回
#REF错误
The reason for both is that you are passing a reference to a worksheet that doesn't exist, which is why you get the #REF error. The file dialog is an attempt to let you correct that reference, by pointing to a file wherein that sheet name doesexist. When you cancel out, the #REF error is expected.
两者的原因是您传递了对不存在的工作表的引用，这就是您收到 #REF 错误的原因。文件对话框试图让您通过指向其中确实存在该工作表名称的文件来更正该引用。当您取消时，预计会出现 #REF 错误。
So you need to do:
所以你需要这样做：
Worksheets(strProjectName).Name = Replace(strProjectName," ", "_")
strProjectName = Replace(strProjectName," ", "_")
Then, your formula should work.
然后，您的公式应该有效。
回答by Eneas Gesing
If Cells(1, 1).Formula gives a 1004 error, like in my case, changes it to:
如果 Cells(1, 1).Formula 给出 1004 错误，就像我的情况一样，将其更改为：
Cells(1, 1).FormulaLocal
回答by Carnifex
If you want to make address directly, the worksheet must exist.
如果你想直接制作地址，工作表必须存在。
Turning off automatic recalculation want help you :)
关闭自动重新计算需要帮助你:)
But... you can get value indirectly...
但是......你可以间接获得价值......
.FormulaR1C1 = "=INDIRECT(ADDRESS(2,7,1,0,""" & strProjectName & """),FALSE)"
At the time formula is inserted it will return #REF error, because strProjectName sheet does not exist.
在插入公式时，它将返回 #REF 错误，因为 strProjectName 表不存在。
But after this worksheet appear Excel will calculate formula again and proper value will be shown.
Disadvantage: there will be no tracking, so if you move the cell or change worksheet name, the formula will not adjust to the changes as in the direct addressing.
但是在此工作表出现后，Excel 将再次计算公式并显示正确的值。
缺点：将没有跟踪，因此如果您移动单元格或更改工作表名称，则公式不会像直接寻址那样根据更改进行调整。
