508 when using Cell.Formula and r1c1 notation.

asked 2018-08-06 15:06:54 +0100

Aesc gravatar image

Hi,

I am trying to add the formula for standard deviation to a column via macro. Something like "= STDEV(A1:A2)"

I use R1C1.

If I use something like this: (Inside for loop)

oCell = Sheet.getCellByPosition(i,lastRow+1)

temp =  "=STDEV(INDIRECT(" & Chr(34) & "R7C" & CStr(i)  & Chr(34) &  ",0):INDIRECT(" & Chr(34) & "R" & CStr(lastRow) & "C" & CStr(i) & Chr(34) & ",0)"

oCell.Formula = temp

The resulting formula in the cell is

=STDEV(INDIRECT("R7C4",0):INDIRECT("R59C4",0))

However the result is 508 (Pair Missing). If I delete a bracket in the cell that's just been modified by the macro and hit enter, well it corrects it and works. the only thing is the formula is the same.

=STDEV(INDIRECT("R7C4",0):INDIRECT("R59C4",0))

What's going on?

edit retag flag offensive close merge delete