May 17 2021 02:39 AM
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.
May 17 2021 02:45 AM
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.
May 17 2021 07:06 AM
@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?
May 17 2021 07:37 AM
See my reply in Link part of file name to Excel cell for your question about the filename.
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
May 17 2021 04:50 PM
May 18 2021 01:33 AM
Probably - just try it.