Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub Total collumns
Hi!
I have 3 collums of data B C 1 45 200 1 50 333 2 60 500 2 10 500 2 80 90 3 90 30 3 800 20 4 80 10 5 250 5 6 300 3 6 60 80 I would like to run a loop which subtotalled my data twice based upon the first collumn (as below) B C B Sub Totals C Sub Totals 1 45 200 1 50 333 95 533 2 60 500 2 10 500 2 80 90 150 1090 3 90 30 3 800 20 890 50 4 80 10 80 10 5 250 5 250 5 6 300 3 6 60 80 360 83 Thanks in advance Darren -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200603/1 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub Total collumns
Sub ABC()
Dim lastrow As Long, ar As Range Dim cell As Range, i As Long Dim rng As Range lastrow = Cells(Rows.Count, 1).End(xlUp).Row For i = lastrow To 1 Step -1 If Cells(i, 1) < Cells(i + 1, 1) Then Rows(i + 1).Insert End If Next Set rng = Columns(2).SpecialCells(xlConstants, xlNumbers) For Each ar In rng.Areas Set cell = ar(ar.Count).Offset(0, 2) cell.Value = Application.Sum(ar) Set cell = cell.Offset(0, 1) cell.Value = Application.Sum(ar.Offset(0, 1)) Next End Sub This assumes your numbers are numeric constants - not produced by formulas or numbers that are stored as text strings. -- Regards, Tom Ogilvy "Darren via OfficeKB.com" wrote: Hi! I have 3 collums of data B C 1 45 200 1 50 333 2 60 500 2 10 500 2 80 90 3 90 30 3 800 20 4 80 10 5 250 5 6 300 3 6 60 80 I would like to run a loop which subtotalled my data twice based upon the first collumn (as below) B C B Sub Totals C Sub Totals 1 45 200 1 50 333 95 533 2 60 500 2 10 500 2 80 90 150 1090 3 90 30 3 800 20 890 50 4 80 10 80 10 5 250 5 250 5 6 300 3 6 60 80 360 83 Thanks in advance Darren -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200603/1 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub Total collumns
Tom,
Thats just awesome Thanks Darren Tom Ogilvy wrote: Sub ABC() Dim lastrow As Long, ar As Range Dim cell As Range, i As Long Dim rng As Range lastrow = Cells(Rows.Count, 1).End(xlUp).Row For i = lastrow To 1 Step -1 If Cells(i, 1) < Cells(i + 1, 1) Then Rows(i + 1).Insert End If Next Set rng = Columns(2).SpecialCells(xlConstants, xlNumbers) For Each ar In rng.Areas Set cell = ar(ar.Count).Offset(0, 2) cell.Value = Application.Sum(ar) Set cell = cell.Offset(0, 1) cell.Value = Application.Sum(ar.Offset(0, 1)) Next End Sub This assumes your numbers are numeric constants - not produced by formulas or numbers that are stored as text strings. Hi! [quoted text clipped - 42 lines] Darren -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200603/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUM IF sum of two collumns | Excel Discussion (Misc queries) | |||
Collumns | Excel Discussion (Misc queries) | |||
disappearing collumns | Excel Discussion (Misc queries) | |||
Totalling Collumns | Excel Worksheet Functions | |||
Collumns and rows | Excel Discussion (Misc queries) |