Excel VBA 中循环公式的单元格引用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19394336/
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
Cell Reference for Formula in Loop in Excel VBA
提问by H0ckeyfr33k99
I am trying to get my macro to place a formula into a specific cell whose cell references are defined by a loop and the range of the formula's data (for STDEV.P) is based off of the progress of the loop. The code that I have made so far is able to insert a row where I would like the STDEV added, input the text "StdDev" in cell A(x), and then select each cell in that new row (incrementing the column). The code for that is shown below:
我试图让我的宏将一个公式放入一个特定的单元格中，该单元格的单元格引用由循环定义，并且公式数据的范围（对于 STDEV.P）基于循环的进度。到目前为止，我编写的代码能够在我想要添加 STDEV 的位置插入一行，在单元格 A(x) 中输入文本“StdDev”，然后选择该新行中的每个单元格（增加列）。代码如下所示：
firstCell = 2
lastCell = 2
initialValue = 84
stepSize = 5
lastRow = Range("A1").End(xlDown).Row
lastColumn = Range("A1").End(xlToRight).Column
For x = 2 To lastRow
cellDifference = Range("B" & (x)).Value - initialValue
If Abs(cellDifference) > 1 Then
lastCell = x - 1
Range("B" & (x)).EntireRow.Insert
Range("A" & (x)) = "StdDev"
For y = 2 To lastColumn
Cells(x, y).Select
Next y
x = x + 1
firstCell = x
initialValue = initialValue + stepSize
End If
Next x
I was trying to use the code below:
我试图使用下面的代码：
Cells(x, y) = "=STDEV.P(" & Cells(firstCell, y) & ":" & Cells(lastCell, y) & ")"
But this gave the error, "Application-defined or object-defined error". I need to know how to input the cell reference value into the formula but have it be a dynamic range that is dependent upon the loop.
但这给出了错误，“应用程序定义或对象定义的错误”。我需要知道如何将单元格引用值输入到公式中，但让它成为依赖于循环的动态范围。
Any help with this would be gladly appreciated. Thank you.
对此的任何帮助将不胜感激。谢谢你。
回答by Kamal G
Try something like this:
尝试这样的事情：
Cells(x, y).FormulaR1C1 = "=STDEVP(" & "R" & firstCell & "C" & y & ": R" & lastCell & "C" & y & ")"
