Defining a Row Number

[Guest]

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

 

[Bob Phillips]

Dim CalcRow As Long

CalcRow = 3 - ActiveCell.Row

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


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Thanks a lot Bob. Much appreciated.

Phil

Dim CalcRow As Long

CalcRow = 3 - ActiveCell.Row

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


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

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