Rounding-Formulas

[Guest]

Is there (or can there be) a macro or a function that changes a formula like

=C1*$R4 in =round(C1*$R4;0) etc.

I have many spreadsheets with hundreds of formulas on each sheet that need
to be rounded. I would have to change individually every single formula. Is
there an easier way?

 

[Niek Otten]

Depending on how you formatted the cells, you might consider

Tools>Options>Calculation tab, check Precision as displayed. Look in HELP to read the consequences.

--
Kind regards,

Niek Otten

| Is there (or can there be) a macro or a function that changes a formula like
|
| =C1*$R4 in =round(C1*$R4;0) etc.
|
| I have many spreadsheets with hundreds of formulas on each sheet that need
| to be rounded. I would have to change individually every single formula. Is
| there an easier way?
|

 

[JE McGimpsey]

One way:

Public Sub WrapARound()
Const sWRAPPER As String = "=Round(#; 0)"
Dim ws As Worksheet
Dim rFormulae As Range
Dim rCell As Range
For Each ws In ActiveWorkbook.Worksheets
On Error Resume Next 'in case no formulae
Set rFormulae = ws.Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
If Not rFormulae Is Nothing Then
For Each rCell In rFormulae
With rCell
If Not .Formula Like "=ROUND(*" Then _
.Formula = Replace( _
sWRAPPER, "#", Mid(.Formula, 2))
End With
Next rCell
End If
Set rFormulae = Nothing
Next ws
End Sub