How To Copy the ROUND Function

Question

I want the formula I have in the cell to stay the same, but I want to add the ROUND function to it.  Is there a way to avoid typing the ROUND function into each cell?  Can I just copy it somehow without altering the formula I have in the cell?

Answer 1

Select the cells and run this - change the 0 to the rounding level you want.

Bernie

Sub AddRounding()

Dim myCell As Range

Dim strForm As String

Application.EnableEvents = False

For Each myCell In Selection

   strForm = Right(myCell.Formula, Len(myCell.Formula) - 1)

   myCell.Formula = "=Round(" & strForm & ",0)"

Next myCell

Application.EnableEvents = True

End Sub

You could also select all the cells, and then apply the rounding to cells with formulas:

Sub AddRounding2)

Dim myCell As Range

Dim strForm As String

Application.EnableEvents = False

For Each myCell In Selection.SpecialCells(xlCellTypeFormulas)

   strForm = Right(myCell.Formula, Len(myCell.Formula) - 1)

   myCell.Formula = "=Round(" & strForm & ",0)"

Next myCell

Application.EnableEvents = True

End Sub

Answer 2

I have tried the rounding action, and it works, when coupled with the formatting to add the "1:". Now i need to work with it a bit to develop my thinking

Thank you