VBA to shift formula reference down one row

[Ahulika]

Hey! First time poster here - still getting used to VBA

I'm currently having an issue shifting formula reference down one row for a more involved formula. Currently, what I am using works great for simpe formulas, but I can't get it to work for more complex formulas.

Ex, I need to update =((C235-C229)/C229)*100 to read =((C236-C230)/C230)*100

This is what I'm running currently:

Private Sub CommandButton1_Click()
With Range("c1065")
.Formula = "=SUM(" & .DirectPrecedents.Offset(1).Address(0, 0) & ")"
End With
End Sub

However, with this formula: =((C235-C229)/C229)*100 it doesn't work.


Any help is much appreciated!

 

[FDibbins]

Cant you just copy it down?

 

[Ahulika]

Cant you just copy it down?

No, (and I probably should have mentioned this earlier) the cell below contains a similar formula. All cells are linked to other worksheets and charts, so I need the cell to stay the same, and just update the formula down one row - when I update the data.