Rounding Subtotals to 2 places


M

marcia2026

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}
 
Ad

Advertisements

M

mike.thompson

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}[/QUOTE]


I got this from the macro recorder -

myC.Style = "Comma"
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Subtotal Formatting 2
Subtotal by VBA 5
Subtotal function 2
Issue with nested data subtotals 3
Excel Subtotals 2
Subtotal Macro Help 2
Subtotals 2
Subtotal macro in each worksheet 13

Top