Summing data

  • Thread starter Thread starter Perry Kew
  • Start date Start date
P

Perry Kew

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
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
Hi Tom
you're right, I made the (eventually wrong) assumption that this
formula is placed in a different column

Frank

Tom said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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


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
Click to expand...


.
Click to expand...
 
Back
Top