I have a template sheet with data that has to be summed
up. The current range is C2:c314. However, the next time I
get the data the data range might be c2: c228.

The data will be contiguous (there won't be any gaps). Row
C1 is a header. I tried to record a macro by finding the
last active cell in Col C, going one cell down, typing =sum
(, hitting the up arrow key, CTRL + Shift + up arrow key,
and then hitting the down arrow key to get my data range.
This is what the code reads:

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

Can any one help by correcting this so that from the
activecell in Col C it will add all the numbers except for
the topmost cell, C1?

I hope the question is clear.

Thanks for your help.

--Perry

Hi Perry
an non VBA solution:
=SUM(OFFSET($C$2,0,0,COUNTA($C:$C)-1)
if there are no blank rows in between

Frank


Perry Kew wrote:
I have a template sheet with data that has to be summed
up. The current range is C2:c314. However, the next time I
get the data the data range might be c2: c228.

The data will be contiguous (there won't be any gaps). Row
C1 is a header. I tried to record a macro by finding the
last active cell in Col C, going one cell down, typing =sum
(, hitting the up arrow key, CTRL + Shift + up arrow key,
and then hitting the down arrow key to get my data range.
This is what the code reads:

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

Can any one help by correcting this so that from the
activecell in Col C it will add all the numbers except for
the topmost cell, C1?

I hope the question is clear.

Thanks for your help.

--Perry

set rng = Range("C1").End(xldown)(2)
rng.FormulaR1C1 = "=SUM(R2C:R[-1]C)"

--
Regards,
Tom Ogilvy


"Perry Kew" wrote in message
...
I have a template sheet with data that has to be summed
up. The current range is C2:c314. However, the next time I
get the data the data range might be c2: c228.

The data will be contiguous (there won't be any gaps). Row
C1 is a header. I tried to record a macro by finding the
last active cell in Col C, going one cell down, typing =sum
(, hitting the up arrow key, CTRL + Shift + up arrow key,
and then hitting the down arrow key to get my data range.
This is what the code reads:

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

Can any one help by correcting this so that from the
activecell in Col C it will add all the numbers except for
the topmost cell, C1?

I hope the question is clear.

Thanks for your help.

--Perry

Since the sum formula is being placed below the data, this formula gives me
a circular reference error.

also, it is missing a final right parenthesis.
--
Regards,
Tom Ogilvy
"Frank Kabel" wrote in message
...
Hi Perry
an non VBA solution:
=SUM(OFFSET($C$2,0,0,COUNTA($C:$C)-1)
if there are no blank rows in between

Frank


Perry Kew wrote:
I have a template sheet with data that has to be summed
up. The current range is C2:c314. However, the next time I
get the data the data range might be c2: c228.

The data will be contiguous (there won't be any gaps). Row
C1 is a header. I tried to record a macro by finding the
last active cell in Col C, going one cell down, typing =sum
(, hitting the up arrow key, CTRL + Shift + up arrow key,
and then hitting the down arrow key to get my data range.
This is what the code reads:

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

Can any one help by correcting this so that from the
activecell in Col C it will add all the numbers except for
the topmost cell, C1?

I hope the question is clear.

Thanks for your help.

--Perry

Hi Tom
you're right, I made the (eventually wrong) assumption that this
formula is placed in a different column

Frank

Tom Ogilvy wrote:
Since the sum formula is being placed below the data, this formula
gives me a circular reference error.

also, it is missing a final right parenthesis.
Hi Perry
an non VBA solution:
=SUM(OFFSET($C$2,0,0,COUNTA($C:$C)-1)
if there are no blank rows in between

Frank


Perry Kew wrote:
I have a template sheet with data that has to be summed
up. The current range is C2:c314. However, the next time I
get the data the data range might be c2: c228.

The data will be contiguous (there won't be any gaps). Row
C1 is a header. I tried to record a macro by finding the
last active cell in Col C, going one cell down, typing =sum
(, hitting the up arrow key, CTRL + Shift + up arrow key,
and then hitting the down arrow key to get my data range.
This is what the code reads:

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

Can any one help by correcting this so that from the
activecell in Col C it will add all the numbers except for
the topmost cell, C1?

I hope the question is clear.

Thanks for your help.

--Perry

Thank you for your help.

set rng = Range("C1").End(xldown)(2)
Can you please tell me what the (2) means?

--Perry


-----Original Message-----
set rng = Range("C1").End(xldown)(2)
rng.FormulaR1C1 = "=SUM(R2C:R[-1]C)"

--
Regards,
Tom Ogilvy


"Perry Kew" wrote
in message
...
I have a template sheet with data that has to be summed
up. The current range is C2:c314. However, the next
time I
get the data the data range might be c2: c228.

The data will be contiguous (there won't be any gaps).
Row
C1 is a header. I tried to record a macro by finding the
last active cell in Col C, going one cell down, typing
=sum
(, hitting the up arrow key, CTRL + Shift + up arrow
key,
and then hitting the down arrow key to get my data
range.
This is what the code reads:

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

Can any one help by correcting this so that from the
activecell in Col C it will add all the numbers except
for
the topmost cell, C1?

I hope the question is clear.

Thanks for your help.

--Perry


.