How do you create a macro in Excel that rounds up or down a number without removing a cell's formula?

Question

We have an Excel sheet that we use as a template for additional sheets and on it we previously had a macro that took a number and either rounded it up or down to the nearest whole number but kept any formulas tied to that cell intact. That macro somehow disappeared and we tried to replicate it with macro code that we found with a Google search:

This performs the rounding just fine but whenever we use it, it strips the cell of any formulas that we had added to it like this one:

I'm not super familiar with visual basic, but is there a way to either tweak this macro or create a new one where it rounds the number up or down and keeps any formulas assigned to the cell intact?

Answer 1

Try this:

Sub RountIt()

    Dim c As Range

    Application.ScreenUpdating = False

    For Each c In Selection

        If IsNumeric(c.Value) And c.Value <> "" Then

            If c.HasFormula Then

                c.Formula = "=ROUND(" & Mid(c.Formula, 2) & ",0)"

            Else

                c.Value = Application.Round(c.Value, 0)

            End If

        End If

    Next c

    Application.ScreenUpdating = True

End Sub

Alternatively, simply set the number format of the cells to Number with 0 decimal places.

Answer 2

Hi Brian,

Did you check the above reply from MVP Hans V? If you still need help, please update us.

Regards,

Neha

Answer 3

This may be what we're looking for, but I need to run it by my boss who would be the one that's actually using it and he has been out so I haven't received a response from him on this, but I'll make sure to add an update as soon as I do.