adding a formula around a cell reference

[Guest]

I am trying to add a formula in a series of cells that already have a cell
reference.
Example:
(current) =B4
(change to) =round(B4,3)

I have a long series of data that I want to preform a round function on but
do not want to have to do this one by one since there are so many.
Is it possible to add this formula without having to do this procedure one
cell at a time?

 

[Guest]

you could use find and replace, but you would still have to enter each cell
reference

 

[Guest]

Try this simple macro:

Sub transformer()
' more than meets the eye
For Each r In Selection
s1 = r.Formula
s2 = Right(s1, Len(s1) - 1)
r.Formula = "=ROUND(" & s2 & ",3)"
Next
End Sub

Enter the macro, select the cells, run the macro.

 

[JE McGimpsey]

One way:

Public Sub WrapARound()
Const sWRAPPER As String = "=Round(#, 3)"
Dim rFormulae As Range
Dim rCell As Range
On Error Resume Next 'in case no formulae
Set rFormulae = Selection.Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
If Not rFormulae Is Nothing Then
For Each rCell In rFormulae
With rCell
If Not .Formula Like "=ROUND(*" Then _
.Formula = Application.Substitute( _
sWRAPPER, "#", Mid(.Formula, 2))
End With
Next rCell
End If
End Sub

 

[Guest]

GREAT! This simple macro saved me hours of tedious work!!
Thanks