VBA Round To 2 Decimal Places

[hobbiton73]

Hi, I wonder whether someone may be able to help me please.

I'm using the code below to apply a 'Totals' row


Sub InsertTotals()
Dim colm As Long, StartRow As Long
Dim EndCell As Range
Dim ws As Worksheet
StartRow = 5
For Each ws In Worksheets(Array("Direct Activities", "Enhancements", "Indirect Activities", "Overheads"))
Set EndCell = ws.Cells(Rows.Count, "B").End(xlUp).Offset(1, 1)
If EndCell.Row > StartRow Then EndCell.Resize(, 12).Formula = "=SUM(R" & StartRow & "C:R[-1]C)/7.4"
EndCell.NumberFormat "0.00"

EndCell.HorizontalAlignment = xlCenter
ws.Columns("B:N").EntireColumn.AutoFit
Next ws
End Sub


I'm now trying to change this so that the 'Total' figure is rounded to 2 decimal places.

I've tried using both the 'Number Format' and 'Round' functions without any success.

I just wondered whether someone may be able to look at this and offer some guidance on how I may go about achieiving this.

Many thanks and kind regards

[hobbiton73]

Hi, I recieved help from elsewhere and was fortunate to be shown how to produce the working solution below.

[CODE]Sub InsertTotals()
Dim colm As Long, StartRow As Long
Dim EndCell As Range
Dim EndCell2 As Range
Dim ws As Worksheet
StartRow = 5
For Each ws In Worksheets(Array("Direct Activities", "Enhancements", "Indirect Activities", "Overheads", "Projects"))
Set EndCell = ws.Cells(Rows.Count, "B").End(xlUp).Offset(1, 1)
If EndCell.Row > StartRow Then
EndCell.Resize(, 12).Formula = "=SUM(R" & StartRow & "C:R[-1]C)/7.4"
EndCell.Offset(1).Resize(, 12).FormulaR1C1 = "=R[-1]C*R3C"
EndCell.Resize(2, 12).NumberFormat = "0.00"
EndCell.Resize(2, 12).HorizontalAlignment = xlCenter

End If
ws.Columns("B:N").EntireColumn.AutoFit
Next ws[/CODE

Kind regards

[mancubus]

thanks for the feed back. - PS: closing square bracket is missing: [/CODE]