vba 使用宏将包含变量的公式插入单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16524210/
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
Inserting a formula containing variable into a cell using a macro
提问by user2378040
I have a vba macro that copies the current sheet and renames it with the current date that works fine, now I want it to put a formula in cell B22, the formula that I want is:
我有一个 vba 宏，它复制当前工作表并使用当前工作正常的日期重命名它，现在我希望它在单元格 B22 中放置一个公式，我想要的公式是：
Current Sheet Previous Sheet
='08 May 2013'!B18 - '01-04-2013'!B18
I have been trying to get this code to work, but it is putting it in as a string not a formula i.e. = ('[08 May 2013 !R2C18] - [ 12 May 2013 !R2C18])
我一直试图让这段代码工作，但它把它作为一个字符串而不是一个公式 ie = ('[08 May 2013 !R2C18] - [ 12 May 2013 !R2C18])
Dim sheet_name As String
Dim activeSheet_name As String
Dim shtName As Integer
Dim activeShtName As Integer
shtName = Sheets.Count - 1
activeShtName = Sheets.Count
sheet_name = Sheets(shtName).Name
activeSheet_name = Sheets(activeShtName).Name
Sheets(activeSheet_name).Select
Range("B22").Select
ActiveCell.Formula = " = ('[" & sheet_name & " !R2C18] - [ " & activeSheet_name & " !R2C18])"
Any help would be appreciated
任何帮助，将不胜感激
回答by steveo40
Your formula is wrong. Try it in Excel first, then copy the text of the formula and compare to the text that your last line of code above is producing. Change your VBA code to this:
你的公式错了。首先在 Excel 中尝试，然后复制公式的文本并与上面最后一行代码生成的文本进行比较。将您的 VBA 代码更改为：
ActiveCell.FormulaR1C1 = "='" & sheet_name & "'!R18C2-'" & activeSheet_name & "'!R18C2"
ActiveCell.FormulaR1C1 = "='" & sheet_name & "'!R18C2-'" & activeSheet_name & "'!R18C2"
Note the use of FormulaR1C1. Basically you need to surround the sheet names with single quotes.
注意FormulaR1C1的使用。基本上，您需要用单引号将工作表名称括起来。
