Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to do subtotal by eachqtr in calendar year
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to do subtotal by eachqtr in calendar year
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: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to do subtotal by eachqtr in calendar year
Hi Stefi,
thanks, I am looking for VBA code in excel 2003. "Stefi" wrote: 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: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to do subtotal by eachqtr in calendar year
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:D" & 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: Hi Stefi, thanks, I am looking for VBA code in excel 2003. "Stefi" wrote: 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: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calendar year versus fiscal year | Excel Discussion (Misc queries) | |||
Help dealing with a fiscal year rather than a calendar year | Excel Worksheet Functions | |||
change the year in a calendar template to different year | Excel Discussion (Misc queries) | |||
Subtotal by Year, Large Spreadsheet? | Excel Programming | |||
Subtotal by Year, Large Spreadsheet? | Excel Programming |