Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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(?????????)" |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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(?????????)" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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(?????????)" |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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(?????????)" |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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(?????????)" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto Update Text Box Cell Reference | Excel Discussion (Misc queries) | |||
Reference a cell on another sheet and have the information auto up | Excel Worksheet Functions | |||
Cell Reference: won't auto-calculate, among other things | Excel Worksheet Functions | |||
Auto fill cell reference | Excel Discussion (Misc queries) | |||
Sum of 3 nos. into 1 cell in another sheet relevant to specific da | Excel Discussion (Misc queries) |