using variable inside activeCell reference call
 

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

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

using variable inside activeCell reference call
 

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)

"dabith " wrote in message
...
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/

using variable inside activeCell reference call
 

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