Forum Discussion

Chan_Tze_Leong's avatar
Chan_Tze_Leong
Brass Contributor
May 17, 2021

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
excel
Formulas and Functions
Macros and VBA

6 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    May 17, 2021

    Chan_Tze_Leong 

    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.

    • Chan_Tze_Leong's avatar
      Chan_Tze_Leong
      Brass Contributor
      May 17, 2021

      HansVogelaar 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?

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        May 17, 2021

        Chan_Tze_Leong 

        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

Resources