I am trying to automate a simple SUM calculation that is based upon a varying
cell range. For instance, Rows 3 to 6 inclusive have figures in that I want
to SUM in Row 7.

I currently have the following code but suspect that I have made a really
simple error with either the defining of CalcRow as Integer (String does not
work either), or the ActiveCell.Formula construction. Any ideas please?

Dim CalcRow As Integer

CalcRow = 3 - ActiveCell.Row

Range("E2").Select
ActiveCell.FormulaR1C1 = "=SUM(R[CalcRow]C:R[-1]C)"

Many Thanks