Since you know you're starting in row 1, couldn't you just use that and the row
above:

ActiveCell.FormulaR1C1 = "=SUM(R1C:R[-1]C)"



"dabith <" wrote:

hey everybody

hoping someone can help with VBA syntax:

I want to use the variable x as a row number reference,

'Insert Total calculation
x = 0
Range("H1").Select
'go the end of the column
Do While IsEmpty(ActiveCell.Offset(0, 0)) = False
x = x + 1
ActiveCell.Offset(1, 0).Select
Loop

I have tried the two statements below but they seem to be illegal.

ActiveCell.FormulaR1C1 =
"=SUM(R[ActiveCell.Offset(0,0)]C:R[ActiveCell.Offset(-&x,0)]C)"

ActiveCell.FormulaR1C1 = "=SUM(R[-&x]C:R[-1]C)"

Any help appreciated
thanks

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson