Late Rounding of Formula
 

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? It could be the "Replace" function if it worked with the
possibility to change the formula on both ends at the same time such as
(example from above):

Replace: =
With: =round( [####=Joker] ,0)

Late Rounding of Formula
 

Possibly a little cludgy but this code snippet should work

for each cell in range(whatever)
cell.Formula = "=round(" & Right(cell.Formula, Len(cell.Formula) -
1) & ",2)"
next

Late Rounding of Formula
 

Building on the suggestion already posted, you could loop through each cell
with a formula that evaluates to a number in each worksheet and change the
formula as suggested. Or, if you want to select the formulas that are
modified (which gives you more control over what cells get changed), change
Aidan's suggestion to

For each cell in selection
.....

Just be sure to back up your work.

Sub Macro2()
Dim rngFormulas As Range
Dim rngCell As Range
Dim wksSheet As Worksheet

On Error Resume Next
For Each wksSheet In Worksheets
Set rngFormulas = Nothing
Set rngFormulas = wksSheet.Cells.SpecialCells(xlCellTypeFormulas, 1)
If Not rngFormulas Is Nothing Then
For Each rngCell In rngFormulas
If LCase(Left(rngCell.Formula, 6)) < "=round" Then
rngCell.Formula = "=round(" & Right(rngCell.Formula, _
Len(rngCell.Formula) - 1) & ",2)"
End If
Next rngCell
End If
Next wksSheet

"fak119" wrote:

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? It could be the "Replace" function if it worked with the
possibility to change the formula on both ends at the same time such as
(example from above):

Replace: =
With: =round( [####=Joker] ,0)