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}

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"