excel vba - 使用变量插入公式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12136812/
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
excel vba - insert a formula using variables
提问by HL8
Hi I want to insert formula into a cell by determining variables for each value.
嗨，我想通过确定每个值的变量来将公式插入到单元格中。
Eg.
例如。
=G5/D5
So in cell
所以在单元格中
Sheets("Sheet1").cells(LastRow + 2, 2).value = "=columnletter1 + (lastrow + 2)/columnletter2 + (lastrow + 2)
I find the columnletter is G by determining if the column name is X and the row is LastRow + 2. How do I write so that I get "=G5/D5" returned into the cell?
通过确定列名是否为 X 并且行是否为 LastRow + 2，我发现 columnletter 为 G。如何编写以便将“=G5/D5”返回到单元格中？
回答by chris neilsen
Not sure if I understand your requirements exactly, I think you mean:
不确定我是否完全理解您的要求，我想您的意思是：
columnletter1is a string, = Gcolumnletter2is a string, = Dlastrowis a long, = 3
columnletter1是一个字符串，=Gcolumnletter2是一个字符串，=Dlastrow是一个长，=3
To set a formula, construct a string and write to the .Formulaproperty
要设置公式，请构造一个字符串并写入.Formula属性
Sheets("Sheet1").cells(LastRow + 2, 2).Formula = _
"=" & columnletter1 & lastrow + 2 & "/" & columnletter2 & lastrow + 2
回答by AHMET NIDA
For your matrix calculations below, the given code including the formula with variables may be helpful.
对于下面的矩阵计算，包含变量公式的给定代码可能会有所帮助。
For ROW= 0 To K - 1
For COL= 0 To J - 1
Range("G5").Offset(ROW, COL).Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS(R2C5:R500C5,R2C4:R500C4,RC[" & -COL- 1 & "],R2C2:R500C2,R[" & -ROW- 3 & "]C,R2C3:R500C3,R[" & -ROW- 2 & "]C)"
Next
COL= COL + 1
Next
ROW= ROW + 1
