Code places sum at bottom, require it on top of range

[Andy]

I have a =sum that needs to be inserted one cell above the range (number of
rows will vary but always in column "E"). The code below inserts the code
one cell below the range but I'm not having any luck figuring out how to
change it so that it inserts it one cell above. Any ideas?

1
2
3
4
-10-

but I require

-10-
1
2
3
4

Worksheets("SecondSheet").Cells(Cells(Rows.Count, 1).End(xlUp).Row,
5).Select
x = Range(ActiveCell, ActiveCell.End(xlDown)).Count
ActiveCell.End(xlDown).Offset(1, 0).FormulaR1C1 = "=sum(R[-" & x &
"]C:R[-1]C)"

 

[Bob Phillips]

Dim iLastRow

With Worksheets("SecondSheet")
iLastRow = .Cells(Rows.Count, 1).End(xlUp).Row
With ActiveCell
.Offset(-1, 0).FormulaR1C1 = _
"=sum(R" & .Row & "C:R" & iLastRow & "C)"
End With
End With


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Andy]

Thanks for your reply Bill. I ended up using a fixed area to do the filtering
and adding of the formula. It simplified things for me enough to modify what I
had to make it work. Just to complete the thread, below is the code I ended up
using:

Range("M2").Select
x = Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(1, 0).End(xlDown)).Count
ActiveCell.FormulaR1C1 = "=sum(R[" & x & "]C:R[1]C)"

Dim iLastRow

With Worksheets("SecondSheet")
iLastRow = .Cells(Rows.Count, 1).End(xlUp).Row
With ActiveCell
.Offset(-1, 0).FormulaR1C1 = _
"=sum(R" & .Row & "C:R" & iLastRow & "C)"
End With
End With
--snip-

Worksheets("SecondSheet").Cells(Cells(Rows.Count, 1).End(xlUp).Row,
5).Select
x = Range(ActiveCell, ActiveCell.End(xlDown)).Count
ActiveCell.End(xlDown).Offset(1, 0).FormulaR1C1 = "=sum(R[-" & x &
"]C:R[-1]C)"

 

[Andy]

Umm, that would be "Thanks Bob" O.o