Auto Sum cell above a relevant reference cell
 

How can I sum all cells above a cell. For instance I have spreadsheets with
varying numbers of rows, and I want to add a sum total at the bottom of each
of those columns. Since the number of rows is unknown, I am unsure of what to
put after the word "sum"....... Here is the code I have:

ActiveCell.SpecialCells(xlLastCell).Select
ActiveCell.Offset(1, -2).Select
ActiveCell.FormulaR1C1 = "=SUM(?????????)"

Auto Sum cell above a relevant reference cell
 

cLastRow = Cells(Rows.Count,activecell.column).End(xlUp).Row
Cells(cLastRow+1,Activecell.column).FormulaR1C1="= SUM(R1C:R" & clastrow
& "C)"

--

HTH

RP
(remove nothere from the email address if mailing direct)


"JJ" wrote in message
...
How can I sum all cells above a cell. For instance I have spreadsheets
with
varying numbers of rows, and I want to add a sum total at the bottom of
each
of those columns. Since the number of rows is unknown, I am unsure of what
to
put after the word "sum"....... Here is the code I have:

ActiveCell.SpecialCells(xlLastCell).Select
ActiveCell.Offset(1, -2).Select
ActiveCell.FormulaR1C1 = "=SUM(?????????)"

Auto Sum cell above a relevant reference cell
 

Hi Bob, Your solution works quite well but what if the first row contains
headings and you don't want that row included in the equation? This is how
the AutoSum icon works. Can a solution do that too?

TIA

JohnL

"Bob Phillips" wrote:

cLastRow = Cells(Rows.Count,activecell.column).End(xlUp).Row
Cells(cLastRow+1,Activecell.column).FormulaR1C1="= SUM(R1C:R" & clastrow
& "C)"

--

HTH

RP
(remove nothere from the email address if mailing direct)


"JJ" wrote in message
...
How can I sum all cells above a cell. For instance I have spreadsheets
with
varying numbers of rows, and I want to add a sum total at the bottom of
each
of those columns. Since the number of rows is unknown, I am unsure of what
to
put after the word "sum"....... Here is the code I have:

ActiveCell.SpecialCells(xlLastCell).Select
ActiveCell.Offset(1, -2).Select
ActiveCell.FormulaR1C1 = "=SUM(?????????)"

Auto Sum cell above a relevant reference cell
 

Sum ignores the header if it is text.

or you could do

cLastRow = Cells(Rows.Count,activecell.column).End(xlUp).Row
Cells(cLastRow+1,Activecell.column).FormulaR1C1="= SUM(R2C:R" & clastrow
& "C)"

--
Regards,
Tom Ogilvy

"JohnL" wrote in message
...

Hi Bob, Your solution works quite well but what if the first row contains
headings and you don't want that row included in the equation? This is
how
the AutoSum icon works. Can a solution do that too?

TIA

JohnL

"Bob Phillips" wrote:

cLastRow = Cells(Rows.Count,activecell.column).End(xlUp).Row
Cells(cLastRow+1,Activecell.column).FormulaR1C1="= SUM(R1C:R" &
clastrow
& "C)"

--

HTH

RP
(remove nothere from the email address if mailing direct)


"JJ" wrote in message
...
How can I sum all cells above a cell. For instance I have spreadsheets
with
varying numbers of rows, and I want to add a sum total at the bottom
of
each
of those columns. Since the number of rows is unknown, I am unsure of
what
to
put after the word "sum"....... Here is the code I have:

ActiveCell.SpecialCells(xlLastCell).Select
ActiveCell.Offset(1, -2).Select
ActiveCell.FormulaR1C1 = "=SUM(?????????)"

Auto Sum cell above a relevant reference cell
 

Thanks Tom, works great!

JohnL

"Tom Ogilvy" wrote:

Sum ignores the header if it is text.

or you could do

cLastRow = Cells(Rows.Count,activecell.column).End(xlUp).Row
Cells(cLastRow+1,Activecell.column).FormulaR1C1="= SUM(R2C:R" & clastrow
& "C)"

--
Regards,
Tom Ogilvy

"JohnL" wrote in message
...

Hi Bob, Your solution works quite well but what if the first row contains
headings and you don't want that row included in the equation? This is
how
the AutoSum icon works. Can a solution do that too?

TIA

JohnL

"Bob Phillips" wrote:

cLastRow = Cells(Rows.Count,activecell.column).End(xlUp).Row
Cells(cLastRow+1,Activecell.column).FormulaR1C1="= SUM(R1C:R" &
clastrow
& "C)"

--

HTH

RP
(remove nothere from the email address if mailing direct)


"JJ" wrote in message
...
How can I sum all cells above a cell. For instance I have spreadsheets
with
varying numbers of rows, and I want to add a sum total at the bottom
of
each
of those columns. Since the number of rows is unknown, I am unsure of
what
to
put after the word "sum"....... Here is the code I have:

ActiveCell.SpecialCells(xlLastCell).Select
ActiveCell.Offset(1, -2).Select
ActiveCell.FormulaR1C1 = "=SUM(?????????)"