In sheet have in column B a year in column C a month in column D and in column E a values.
Column B and C are already ordered by Year and Month similar:
B C D E
2006 1 1 2
2006 2 2 3
2006 3 1 1
...
2006 12 1 1
2007 1 1 2
2007 2 2 1
2007 3 1 1
...
2007 12 0 1
I need to insert a new line to the end of each sequence Year and Month a Total of value D and E
How to via vba code?
insert Total in cells
-
- 5StarLounger
- Posts: 818
- Joined: 24 Jan 2010, 15:56
Re: insert Total in cells
Do you want totals for the year, or for year and month? (Your data only shows one entry per month)
Regards,
Rory
Rory
-
- PlatinumLounger
- Posts: 4374
- Joined: 26 Apr 2010, 17:36
Re: insert Total in cells
see the attache file.rory wrote:Do you want totals for the year, or for year and month? (Your data only shows one entry per month)
OLD sheet is wath i have now, and NEW sheet is wath i want:-) via vba code...
In effect loop into column year and month and when the sequence yar and month change add the line with a total.
You do not have the required permissions to view the files attached to this post.
-
- 5StarLounger
- Posts: 818
- Joined: 24 Jan 2010, 15:56
Re: insert Total in cells
You could use subtotals:
Code: Select all
Range("A1").CurrentRegion.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(4, 5, 6), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Regards,
Rory
Rory
-
- PlatinumLounger
- Posts: 4374
- Joined: 26 Apr 2010, 17:36
Re: insert Total in cells
Dont understand how to use this function in a vba coderory wrote:You could use subtotals:Code: Select all
Range("A1").CurrentRegion.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(4, 5, 6), _ Replace:=True, PageBreaks:=False, SummaryBelowData:=True
-
- 5StarLounger
- Posts: 818
- Joined: 24 Jan 2010, 15:56
Re: insert Total in cells
That is VBA code. You just put it in whatever routine you are trying to do this in.
Regards,
Rory
Rory
-
- PlatinumLounger
- Posts: 4374
- Joined: 26 Apr 2010, 17:36
Re: insert Total in cells
Nice, but i dont want the left cursor slide ...rory wrote:That is VBA code. You just put it in whatever routine you are trying to do this in.
-
- 5StarLounger
- Posts: 818
- Joined: 24 Jan 2010, 15:56
Re: insert Total in cells
How about this:
Code: Select all
Dim vardata
Range("A1").CurrentRegion.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(4, 5, 6), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
vardata = Range("A1").CurrentRegion.Value
Range("A1").CurrentRegion.RemoveSubtotal
Range("A1").Resize(UBound(vardata, 1), UBound(vardata, 2)).Value = vardata
Regards,
Rory
Rory