Adjusting formulas by copying across cells w/o changing the refere

[Jamie]

Hello,

I was wondering if the below situation could be made easier:

I have cells that already have a formula in them. The formulas have no
specific pattern. Therefore I can't just adjust one formula and copy that
across because the referenced cells will change. I need to adjust that
formula for all the cells but the reference need to stay the same.

For example: cell A1: =g25*f2 A2: = z1*aa5 A3: =b2/b3

I need to round the answers so I want the cell formulas to look like this:
A1:=round(g25*f2,-2) A2: =round(z1*aa5,-2) etc...

Is there anyway to add the "round" formula, or any formula for that matter,
to one cell and then copy it to the remaining cells so that just the "round"
formula is added and all the references stay exactly the same.

Thanks,

 

[Sean Timmons]

In a blank cell, type:

=ROUND(INDIRECT("A1"),-2)

and paste across.

 

[Jamie]

That didn't work. Maybe because the actual cells are different? the actual
cells are C30:G30 instead of the A1:A3 like I posted before.

Jamie

 

[Bassman62]

Jamie,

The following code will add the ROUND function to any formula resulting with
a number within a contiguous range.
Tested but I recommend saving the file before running.
Insert the code into a standard module: press Alt-[F11] to display the VB
editor.
In the left pane, right-click on the file name and select Insert > Module
Paste the code into the new module
Modify these two lines to fit your needs:
Set rng = Worksheets("Sheet1").Range("A1:B3")
d = 2 'set number of decimals to round to.
Change the sheet name, range and decimal number as needed. (Leave the
"quotes" in place)
To run the macro from the worksheet, press Alt-[F8] and select "addround"
then click Run.
Again, Save the file before running for there is no undo for this.
To make sure that the code is not run again, delete it from the module or
remove the module. From VB editor right-click on the module and select
"Remove..."

Regards,
Dave
--------------------------------------------------------

Option Explicit

Sub addround()

Dim rng As Range
Dim d As Double
Dim rLoopCell As Range

' Set Your sheet name and contiguous range here
Set rng = Worksheets("Sheet1").Range("A1:B3")
d = 2 'set number of decimals to round to.

On Error Resume Next
For Each rLoopCell In rng
If rLoopCell.HasFormula And IsNumeric(rLoopCell.Value) Then
rLoopCell = "=" & "ROUND(" & Mid(rLoopCell.Formula, 2, 1000) & "," & d &
")"
End If
Next rLoopCell

End Sub

 

[Shane Devenshire]

Hi,

Here is a shorter version of Bassman's post

Sub addround()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula And IsNumeric(cell) Then
cell = "=ROUND(" & Mid(cell.Formula, 2, 1000) & ",2)"
End If
Next cell
End Sub

 

[Jamie]

This didn't work. I have zero experience with VB so my guess is that I'm
doing something wrong. Is there another way? If not could you try and explain
it in more detail. For example when you say past the code I am unsure what
code you are referring to.

Thanks

Jamie,

The following code will add the ROUND function to any formula resulting with
a number within a contiguous range.
Tested but I recommend saving the file before running.
Insert the code into a standard module: press Alt-[F11] to display the VB
editor.
In the left pane, right-click on the file name and select Insert > Module
Paste the code into the new module
Modify these two lines to fit your needs:
Set rng = Worksheets("Sheet1").Range("A1:B3")
d = 2 'set number of decimals to round to.
Change the sheet name, range and decimal number as needed. (Leave the
"quotes" in place)
To run the macro from the worksheet, press Alt-[F8] and select "addround"
then click Run.
Again, Save the file before running for there is no undo for this.
To make sure that the code is not run again, delete it from the module or
remove the module. From VB editor right-click on the module and select
"Remove..."

Regards,
Dave
--------------------------------------------------------

Option Explicit

Sub addround()

Dim rng As Range
Dim d As Double
Dim rLoopCell As Range

' Set Your sheet name and contiguous range here
Set rng = Worksheets("Sheet1").Range("A1:B3")
d = 2 'set number of decimals to round to.

On Error Resume Next
For Each rLoopCell In rng
If rLoopCell.HasFormula And IsNumeric(rLoopCell.Value) Then
rLoopCell = "=" & "ROUND(" & Mid(rLoopCell.Formula, 2, 1000) & "," & d &
")"
End If
Next rLoopCell

End Sub


------------------------------------------------------

Hello,

I was wondering if the below situation could be made easier:

I have cells that already have a formula in them. The formulas have no
specific pattern. Therefore I can't just adjust one formula and copy that
across because the referenced cells will change. I need to adjust that
formula for all the cells but the reference need to stay the same.

For example: cell A1: =g25*f2 A2: = z1*aa5 A3: =b2/b3

I need to round the answers so I want the cell formulas to look like this:
A1:=round(g25*f2,-2) A2: =round(z1*aa5,-2) etc...

Is there anyway to add the "round" formula, or any formula for that
matter,
to one cell and then copy it to the remaining cells so that just the
"round"
formula is added and all the references stay exactly the same.

Thanks,

 

[Jamie]

that did the trick. Thanks. If there is a shorter/easier way then please let
me know

Thanks