Contributor

# Excel formulas with round function

I would like to insert round() to a range of highlighted cells that includes existing formulas.  Any help?

A reference :

I found a macro that round Here is a macro that will wrap a ROUND() function around all of your formulas within the active data area that have not already been ROUND()'ed.

Sub mcrRound_5_Formulas()
For Each Cell In Range(Range("A1"), ActiveCell.SpecialCells(xlLastCell))
If Cell.HasFormula Then
If Left(Cell.Formula, 7) <> "=ROUND(" Then
Cell.Formula = "=ROUND(" & Mid(Cell.Formula, 2, 1024) & ",5)"
Cell.NumberFormat = "0.00000"
End If
End If
Next Cell
End Sub
6 Replies

# Re: Excel formulas with round function

Please explain in more detail how and where you want ROUND to be inserted in a formula, preferably with some relevant examples. Perhaps you could attach a small sample workbook with "before" and "after" formulas.

# Re: Excel formulas with round function

@Hans Vogelaar In the attached file, rating is based on achievement from 90% to above 120% (from rating 1 to 5). For example rating 1 is from <90% and rating 2 is from 90% to 95%. If the achievement is 95.6%, it should be rounded to 96%. If the achievement is 91.3%, it should be rounded to 91%. . How should I do?

In addition, I have inserted the FileName =GET.DOCUMENT(88) and the MTD Apr 2021 and YTD Apr 2021 is based on the FileName. However, when I change Excel name to KPI CY2021 CSRM_Apr to KPI CY2021 CSRM_May, the MTD and YTD name still refers to Apr instead of May. How do I fix this problem?

# Re: Excel formulas with round function

Here is a macro:

``````Sub AddRound()
Dim rng As Range
Dim s As String
Dim p1 As Long
Dim p2 As Long
Dim p3 As Long
Application.ScreenUpdating = False
For Each rng In Range("AH11:AH32,AN11:AN32").SpecialCells(xlCellTypeFormulas)
s = rng.Formula
p2 = InStr(s, "/")
If p2 > 0 Then
If InStr(s, "ROUND") = 0 Then
p1 = InStrRev(s, ",", p2 - 1)
If p1 = 0 Then
p1 = InStrRev(s, "(", p2 - 1)
If p1 = 0 Then
p1 = InStrRev(s, "=", p2 - 1)
End If
End If
If p1 = 0 Then
MsgBox "Houston, we have a problem with cell " & rng.Address & "!", vbExclamation
Stop
Else
p3 = InStr(p2 + 1, s, ")")
If p3 = 0 Then
p3 = Len(s) + 1
End If
s = Left(s, p1) & "ROUND(" & Mid(s, p1 + 1, p3 - p1 - 1) & ",2)" & Mid(s, p3)
rng.Formula = s
End If
End If
End If
Next rng
Application.ScreenUpdating = True
End Sub``````

# Re: Excel formulas with round function

Dear Hans, I will have 35 Excel files that have differing length rows for achievement. Could I define these cells under Name Manager as Ach and replace "AH11:AH32,AN11:AN32" with "Ach"? Would the macro still run with no problems?

# Re: Excel formulas with round function

Probably - just try it.