Cell Reference for Formula in Loop in Excel VBA

Question

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:

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.

Kamal G Kamal G · Accepted Answer · 2013-10-16T03:00:10

Try something like this:

            Cells(x, y).FormulaR1C1 = "=STDEVP(" & "R" & firstCell & "C" & y & ": R" & lastCell & "C" & y & ")"

Cell Reference for Formula in Loop in Excel VBA

1 Answers