using variable inside activeCell reference call

[dabith]

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
thank

 

[Bob Phillips]

Is this what you want?

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

--

HTH

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

 

[Dave Peterson]

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)"

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