macro to do subtotal by eachqtr in calendar year

[yagna]

Hi MVPs,

I have written a macro to calculate the budget each month which inserts row,
the lines in the excel based on the date range given by month wise. I need
now to calculate the quarterly subtotal for the months given in teh date
range.

for eg date range 01-jan-09 to 31-mar-09
Jan-09 1000
Feb-09 1000
Mar-09 1000
Qtr 1 09 3000

Thanks for your replies.

 

[Stefi]

One way:

Create a helper column for quarters with this formula (A2 being the date):
=LOOKUP(MONTH(A2),{1,2,3,4,5,6,7,8,9,10,11,12},{1,1,1,2,2,2,3,3,3,4,4,4})
Create subtotals by this helper column as usual!

Regards,
Stefi

„yagna†ezt írta:

 

[yagna]

Hi Stefi,

thanks, I am looking for VBA code in excel 2003.

 

[Stefi]

Something like this?

Sub quartersubtot()
lastdate = Range("A" & Rows.Count).End(xlUp).Row
Range("D2").FormulaR1C1 = _

"=LOOKUP(MONTH(RC[-3]),{1,2,3,4,5,6,7,8,9,10,11,12},{1,1,1,2,2,2,3,3,3,4,4,4})"
Selection.AutoFill Destination:=Range("D2" & lastdate),
Type:=xlFillDefault
Range("A1").Select
Selection.Subtotal GroupBy:=4, Function:=xlSum, TotalList:=Array(3), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End Sub

Column A for dates,
column D for quarters.

Stefi


„yagna†ezt írta: