The macro below will round up all numeric values to 0 decimal places. You can either change the
values to formulas that use the original value (what it does now), or change the value to the
rounded-up value. To do that change
myC.FormulaR1C1 = "=ROUNDUP(" & myC.Value & ",0)"
'myC.Value = Application.WorksheetFunction.RoundUp(myC.Value, 0)
to
'myC.FormulaR1C1 = "=ROUNDUP(" & myC.Value & ",0)"
myC.Value = Application.WorksheetFunction.RoundUp(myC.Value, 0)
HTH,
Bernie
MS Excel MVP
Sub RoundUpSheet()
Dim myC As Range
On Error GoTo NoFormulas
For Each myC In Cells.SpecialCells(xlCellTypeFormulas, 1)
If InStr(1, myC.FormulaR1C1, "ROUNDUP") = 0 Then _
myC.FormulaR1C1 = "=ROUNDUP(" & Mid(myC.FormulaR1C1, 2, Len(myC.FormulaR1C1)) & ",0)"
Next myC
NoFormulas:
Resume DoValues
DoValues:
On Error GoTo NoConstants
For Each myC In Cells.SpecialCells(xlCellTypeConstants, 1)
myC.FormulaR1C1 = "=ROUNDUP(" & myC.Value & ",0)"
'myC.Value = Application.WorksheetFunction.RoundUp(myC.Value, 0)
Next myC
NoConstants:
End Sub
