Previous Topic Next Topic

Column totals using macro

Closed
exhale
Posts
5
Registration date
Friday January 3, 2014
Status
Member
Last seen
January 3, 2014
 - Jan 3, 2014 at 02:22 AM
exhale
Posts
5
Registration date
Friday January 3, 2014
Status
Member
Last seen
January 3, 2014
 - Jan 3, 2014 at 11:51 PM
need help , how do I add sum totals at the bottom of columns of varying lengths using macros. eg I have data in columns a to f, I want columns c to f to be summed up and the sum to be at the bottom of the columns in one row as per below example. remember the columns vary in length but column A to C are always the longest in length.

A B C D E F
405104680 201304 527.07 75.484
405104680 201305 527.07 73.468
405104680 201306 527.07 345 74.12 5544
405104680 201307 0 0
405104680 201308 0 0
405104680 201309 0 78 0
405104680 201312 527.07 3527.156
405104680 201401 527.07

sum 2,635.35 423.00 3,750.23 5,544.00

9 replies

Answer 1 / 9
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
 808
Jan 3, 2014 at 03:02 AM
first row is headings srow

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
0
Answer 2 / 9
exhale
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
0
Answer 3 / 9
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
 808
Jan 3, 2014 at 06:43 AM
try this 

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
0
Answer 4 / 9
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
 808
Jan 3, 2014 at 06:49 AM
that macro is wrong. wait for some time
0

Didn't find the answer you are looking for?

Ask a question
Answer 5 / 9
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
 808
Jan 3, 2014 at 07:10 AM
DISCARD PREVIOUS MACRO

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
0
Answer 6 / 9
exhale
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.
0
Answer 7 / 9
exhale
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
0
Answer 8 / 9
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
 808
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.
0
Answer 9 / 9
exhale
Posts
5
Registration date
Friday January 3, 2014
Status
Member
Last seen
January 3, 2014
Jan 3, 2014 at 11:51 PM
Ok thanks
0