Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rounding Subtotals to 2 places
I have a small macro that is in part from a recorded macro and in part a
small macro that I found on a post here, but it doesn't quite do what I want. Sometimes my subtotals need to be rounded to 2 places in order to get the accounting format. I have been doing this by hand, but with some 100 tax types with total for both current period and year to date it is a chore. If someone could help me to correct this I would appreciate it. [code]Sub Subtotals() 'Create subtotals Range("A2").Select Selection.Subtotal GroupBy:=3, Function:=xlSum,_ TotalList:=Array(6, 7), _Replace:=True, PageBreaks:=False, SummaryBelowData:=True 'Round off the subtotals so that zeros will be in accounting format Dim myC As Range For Each myC In Cells.SpecialCells(xlCellTypeFormulas, 23) If InStr(1, myC.Formula, "SUBTOTAL") 0 Then myC.Font.Bold = True myC.NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)" End If Next myC End Sub [/Code} |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rounding Subtotals to 2 places
On Feb 16, 5:27*pm, marcia2026
wrote: I have a small macro that is in part from a recorded macro and in part a small macro that I found on a post here, but it doesn't quite do what I want. *Sometimes my subtotals need to be rounded to 2 places in order to get the accounting format. *I have been doing this by hand, but with some 100 tax types with total for both current period and year to date it is a chore. If someone could help me to correct this I would appreciate it. * [code]Sub Subtotals() 'Create subtotals * *Range("A2").Select * *Selection.Subtotal GroupBy:=3, Function:=xlSum,_ *TotalList:=Array(6, 7), _Replace:=True, PageBreaks:=False, SummaryBelowData:=True 'Round off the subtotals so that zeros will be in accounting format Dim myC As Range For Each myC In Cells.SpecialCells(xlCellTypeFormulas, 23) If InStr(1, myC.Formula, "SUBTOTAL") 0 Then myC.Font.Bold = True myC.NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)" End If Next myC End Sub [/Code} I got this from the macro recorder - myC.Style = "Comma" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
rounding decimal places | Excel Discussion (Misc queries) | |||
Set fixed Decimal places with no rounding | Excel Worksheet Functions | |||
Rounding to two even decimal places | Excel Worksheet Functions | |||
Rounding off decimal places | Excel Discussion (Misc queries) | |||
Decimal places and rounding up (or down) | New Users to Excel |