Several days ago I received a very large workbook from a co-worker containing thousands of formulas. Some of the formulas were wrapped in the ROUND function while others did not. All of the formulas were originally returning data rounded to the nearest million. However, in order for the workbook to be loaded to our accounting system all of the data needed to be stated in thousands and then rounded to whole numbers.

To do so, I wrote a macro to first remove the ROUND function from any formula that contained it. After some trial and error, the macro to remove the ROUND formula finally looked like this:

Sub RemoveRound()

    Dim cellRange As Range
    Dim Rng As Range
    Dim cellFormula As String
        
    On Error Resume Next
    
    Set cellRange = Range("A1:C5").SpecialCells(xlCellTypeFormulas)

    For Each Rng In cellRange
        
        cellFormula = Mid(Rng.Formula, 2, 1024)
        If InStr(UCase(cellFormula), UCase("Round")) > 0 Then
        
        cellFormula = Right(cellFormula, (Len(cellFormula)) - 6)

        cellFormula = Left(cellFormula, Len(cellFormula) - 3)
        
        Rng.Formula = "=" & cellFormula
        
        End If
    
    Next Rng

End Sub

The macro works by looping through each cell in the range with a formula. As it loops through each cell, the formula is stored in a the string variable called "cellFormula". The line cellFormula = Right(cellFormula, (Len(cellFormula)) - 6) trims the "ROUND(" out of the left side of the formula and saves the result to the same cellFormula variable. The reference to cellFormula = Left(cellFormula, Len(cellFormula) - 3) trims the ",0)" out of the right side of the formula and then saves the result to the cellFormula variable. Finally, the reference to Rng.Formula = "=" & cellFormula saves the formula without the ROUND function back to the range.

Since all formulas were calculating in millions, I used another macro to add a multiplier to each formula to convert the results to thousands. The following macro adds "*1000" to the end of each formula.

Sub AddThousands()

    Dim cellRange As Range
    Dim Rng As Range
    Dim cellFormula As String

    On Error Resume Next
    
    Set cellRange = Range("A1:C5").SpecialCells(xlCellTypeFormulas)

    For Each Rng In cellRange
        
        cellFormula = Mid(Rng.Formula, 2, 1024)
        If InStr(UCase(cellFormula), UCase("Round")) = 0 Then
            Rng.Formula = "=" & cellFormula & "*1000"
        End If
    
    Next Rng

End Sub

Finally, the last macro rounded all of the formulas in the workbook to the nearest whole number.

Sub AddRound()

    Dim cellRange As Range
    Dim Rng As Range
    Dim cellFormula As String

    On Error Resume Next
    
    Set cellRange = Range("A1:C5").SpecialCells(xlCellTypeFormulas)

    For Each Rng In cellRange
        
        cellFormula = Mid(Rng.Formula, 2, 1024)
        If InStr(UCase(cellFormula), UCase("Round")) = 0 Then
            Rng.Formula = "=round(" & cellFormula & ",0)"
        End If
    
    Next Rng

End Sub

It looks like your code assumes that ROUND is always the outermost function in the cell. I assume that is how your data is formatted. If it wasn’t, the macro would be much more complicated.

Interesting. I’ve only ever needed a macro to insert something, like INT() or IF(ISERROR()).

A couple things to note:

Your first procedure uses these three lines:

  If InStr(UCase(cellFormula), UCase(“Round”)) > 0 Then
     
  cellFormula = Right(cellFormula, (Len(cellFormula)) - 6)

  cellFormula = Left(cellFormula, Len(cellFormula) - 3)

All assume that the formulas begin with ”=ROUND(”. ROUND is less likely than some to be nested within other functions, but even something innocent like

=IF(A1>0,ROUND(A1,0),))

will produce

=>0,ROUND(A1,0)

which will make Excel choke.

By rights, you should parse the formula from outside in at both ends to make sure you remove exactly what needs removing. You should even make sure the typist hasn’t included harmless spaces:

”= ROUND( B6, 0 )”

There are also valid non-mathematical reasons for a formula to contain “ROUND”, e.g.

=IF(A1>0,“ROUND”,“SQUARE”)

I guess the bottom line is that you just have to be certain of what you’re doing, and not rush in like a Barkley’s in a china shop.