Add "Round" to each formula in selection

[VegasNath]

In a similar fashion to this post, would it be possible convert all formulas in a selection to include the 'Round' function to n decimal places. Ideally, I would like maybe an input box so the user can dictate the amount of decimals required.

[HansV]

This version will work OK if you
- Select more than one cell.
- At least one cell within the selection has a formula.
- You don't have multiple cells with a single array formula (single cells with array formulas are OK).

Code: Select all

Sub AddRound()
  Dim rng As Range
  Dim strFormula As String
  Dim n As Integer
  n = InputBox("To how many decimals do you want to round?")
  ' Loop through cells with formulas
  For Each rng In Selection.SpecialCells(xlCellTypeFormulas)
    ' Formula without =
    strFormula = Mid(rng.Formula, 2)
    ' New formula
    strFormula = "=ROUND(" & strFormula & "," & n & ")"
    ' Check for array formula
    If rng.HasArray Then
      ' Apply new formula to all cells that have this array formula
      rng.CurrentArray.FormulaArray = strFormula
    Else
      ' Apply new formula
      rng.Formula = strFormula
    End If
  Next rng
End Sub

[VegasNath]

Thanks a lot Hans, much appreciated.