Column totals using macro
Closed
exhale
exhale
- Posts
- 5
- Registration date
- Friday January 3, 2014
- Status
- Member
- Last seen
- January 3, 2014
exhale
- Posts
- 5
- Registration date
- Friday January 3, 2014
- Status
- Member
- Last seen
- January 3, 2014
Related:
- Column totals using macro
- Find Value Between 2 column Range Excel Macro ✓ - Forum - Excel
- Excel macro to group by column and sum value ✓ - Forum - Excel
- Excel macro to delete columns based on header ✓ - Forum - Excel
- Macro to sort data with respect to the column - How-To - Excel
- Excel macro search column for value - Guide
9 replies
venkat1926
Jan 3, 2014 at 03:02 AM
- Posts
- 1864
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- August 7, 2021
Jan 3, 2014 at 03:02 AM
first row is headings srow
macro can be
macro can be
Sub test()
Dim r As Range, lastrow As Integer, c As Range
lastrow = Range("A1").CurrentRegion.SpecialCells(xlCellTypeLastCell).Row
Set r = Range("C1:F1")
For Each c In r
Cells(lastrow + 1, c.Column) = WorksheetFunction.Sum(Range(Cells(2, c.Column), Cells(lastrow, c.Column)))
Next c
End Sub
exhale
Jan 3, 2014 at 04:45 AM
- Posts
- 5
- Registration date
- Friday January 3, 2014
- Status
- Member
- Last seen
- January 3, 2014
Jan 3, 2014 at 04:45 AM
thanks alot, the code works but 1st row is not a headings so i'd like the sum to include 1st row. I also have a few requests in addtions;
1. may I see the actual formula in the totals row and not just the number?
2. i'd like the total's row to be formatted, number to be comma separated eg 22,539.45 and
3. totals(sum) row C to F to have borders, 1 line on top 2 lines at bottom.
i used code below but it only sums one column & formats one cell in which there's a total. I'm new to macros, your help is much appreciated
Range("C1048576").End(xlUp).Offset(2, 0).Select
Selection.FormulaR1C1 = "=SUM(R1c3:r[-2]C)"
Selection.Style = "comma"
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThick
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
1. may I see the actual formula in the totals row and not just the number?
2. i'd like the total's row to be formatted, number to be comma separated eg 22,539.45 and
3. totals(sum) row C to F to have borders, 1 line on top 2 lines at bottom.
i used code below but it only sums one column & formats one cell in which there's a total. I'm new to macros, your help is much appreciated
Range("C1048576").End(xlUp).Offset(2, 0).Select
Selection.FormulaR1C1 = "=SUM(R1c3:r[-2]C)"
Selection.Style = "comma"
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThick
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
venkat1926
Jan 3, 2014 at 06:43 AM
- Posts
- 1864
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- August 7, 2021
Jan 3, 2014 at 06:43 AM
try this
AFTER understanding the macro remove comment statement which start with single apostrophe
Sub testone()
Dim r As Range
'Range("C1048576").End(xlUp).Offset(2, 0).Select
Set r = Cells(Rows.Count, "C").End(xlUp).Offset(2, 0)
'Selection.FormulaR1C1 = "=SUM(R1c3:r[-2]C)"
r.FormulaR1C1 = "=SUM(R1c3:r[-2]C)"
'Selection.Style = "comma"
'Selection.Borders(xlDiagonalDown).LineStyle = xlNone
'Selection.Borders(xlDiagonalUp).LineStyle = xlNone
'Selection.Borders(xlEdgeLeft).LineStyle = xlNone
'With Selection.Borders(xlEdgeTop)
'.LineStyle = xlContinuous
'.ColorIndex = 0
'.TintAndShade = 0
'.Weight = xlThin
'End With
'With Selection.Borders(xlEdgeBottom)
'.LineStyle = xlDouble
'.ColorIndex = 0
'.TintAndShade = 0
'.Weight = xlThick
r.NumberFormat = "0,000.00"
r.Copy Range(r.Offset(0, 1), r.Offset(0, 3))
End Sub
AFTER understanding the macro remove comment statement which start with single apostrophe
venkat1926
Jan 3, 2014 at 06:49 AM
- Posts
- 1864
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- August 7, 2021
Jan 3, 2014 at 06:49 AM
that macro is wrong. wait for some time
Didn't find the answer you are looking for?
Ask a question
venkat1926
Jan 3, 2014 at 07:10 AM
- Posts
- 1864
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- August 7, 2021
Jan 3, 2014 at 07:10 AM
DISCARD PREVIOUS MACRO
try this
try this
Sub testTWO()
Dim r As Range, lastrow As Integer, c As Range, myformula
lastrow = Range("A1").End(xlDown).Row
Set r = Cells(lastrow + 2, "C")
Do
r.Formula = "=sum(" & r.Offset(-2, 0).Address & ":" & Cells(1, r.Column).Address & ")"
r.NumberFormat = "#,##0.00"
Set r = r.Offset(0, 1)
If r.Column > Range("F1").Column Then Exit Do
Loop
End Sub
exhale
Jan 3, 2014 at 07:23 AM
- Posts
- 5
- Registration date
- Friday January 3, 2014
- Status
- Member
- Last seen
- January 3, 2014
Jan 3, 2014 at 07:23 AM
Works like a charm, thanks a lot. just one more thing, how do I add borders to the results? preferably top and double bottom borders. you've been a great help.
exhale
Jan 3, 2014 at 08:53 AM
- Posts
- 5
- Registration date
- Friday January 3, 2014
- Status
- Member
- Last seen
- January 3, 2014
Jan 3, 2014 at 08:53 AM
Will you pls put apostrophes in your code to explain the steps? it works but I don't understand it fully
venkat1926
Jan 3, 2014 at 09:58 PM
- Posts
- 1864
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- August 7, 2021
Jan 3, 2014 at 09:58 PM
"add borders"
best way are you having version excel2007 orhigher
then click DEVeLOPER ribbon and click RECORD MACRO on the extreme left
a macro name is suggested accept and note that name
now do whatever you want to do regarding border
use that macro. Perhaps macro has to be tweaked but this may not be necessary. if there is problem revert with copy of the recorded macro.
regarding code
r is a variable which signifies the 2 rows below last row and cell C in that row.
then the formula
r is now changed to result row col D
formula is redone
etc till you reach end of the columns
that numberformat is formatting number with comma at thousand points and two decimal points.
best way are you having version excel2007 orhigher
then click DEVeLOPER ribbon and click RECORD MACRO on the extreme left
a macro name is suggested accept and note that name
now do whatever you want to do regarding border
use that macro. Perhaps macro has to be tweaked but this may not be necessary. if there is problem revert with copy of the recorded macro.
regarding code
r is a variable which signifies the 2 rows below last row and cell C in that row.
then the formula
r is now changed to result row col D
formula is redone
etc till you reach end of the columns
that numberformat is formatting number with comma at thousand points and two decimal points.
exhale
Jan 3, 2014 at 11:51 PM
- Posts
- 5
- Registration date
- Friday January 3, 2014
- Status
- Member
- Last seen
- January 3, 2014
Jan 3, 2014 at 11:51 PM
Ok thanks