Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
adding a formula around a cell reference
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
adding a formula around a cell reference
you could use find and replace, but you would still have to enter each cell
reference "Greek77" wrote: 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
adding a formula around a cell reference
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. -- Gary''s Student - gsnu200722 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
adding a formula around a cell reference
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 In article , Greek77 wrote: 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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
adding a formula around a cell reference
GREAT! This simple macro saved me hours of tedious work!!
Thanks "Gary''s Student" wrote: 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. -- Gary''s Student - gsnu200722 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
adding cell reference on a header | Excel Worksheet Functions | |||
Using a cell reference within a cell reference in a formula | Excel Worksheet Functions | |||
Adding number to a cell reference not to the contents | Excel Worksheet Functions | |||
What is the shortcut key for adding $ to a cell reference eg $c$3 | Excel Worksheet Functions | |||
Adding Smart Tags to an unrecognized cell reference | Excel Discussion (Misc queries) |