adding the same formula to several cells without changing cell lin

[Guest]

Hi,

I have a column of cells that are linked to several different worksheets.
I'd like the number in each cell to be rounded to the nearest 100, but I
don't want to enter =round(##,-3) for every single cell, and I can't just
enter it for one and drag because that would change my links. Is there a way
to round a column of numbers without dragging and without entering the
formula in for each cell?

Thanks,
RCC

 

[Bernard Liengme]

1) Might be possible with some VBA code to take the existing formula and
wrap it in the ROUND. If interested post to the Excel programming newsgroup
asking for this.
2) As a first attempt I would be tempted to add a new column to the right of
the existing one (lets say it is B) and add =ROUND(B2,-2) Then hide column
B. Note it is -2 to round to 100 (a typo, I expect)
best wishes

 

[Niek Otten]

You might consider using

Tools>Options>Calculation tab, and check Precision as displayed.

Do read HELP first so you understand the implications.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi,
|
| I have a column of cells that are linked to several different worksheets.
| I'd like the number in each cell to be rounded to the nearest 100, but I
| don't want to enter =round(##,-3) for every single cell, and I can't just
| enter it for one and drag because that would change my links. Is there a way
| to round a column of numbers without dragging and without entering the
| formula in for each cell?
|
| Thanks,
| RCC

 

[Bernard Liengme]

This will do it. Copy this subroutine to a general module. Select the cells
to change (remember you can select non-contiguous cell using CTRL); then run
the subroutine (Tools | Macro | Rum Macro)
Need help with VBA? See David McRitchie's site on "getting started" with VBA
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Sub wrapper()
For Each mycell In Selection
holdFormula = mycell.Formula
holdFormula = Mid(holdFormula, 2)
newFormula = "=ROUND(" & holdFormula & ",-2)"
mycell.Formula = newFormula
Next
End Sub

best wishes

 

[Guest]

Thanks, Bernard.

This will do it. Copy this subroutine to a general module. Select the cells
to change (remember you can select non-contiguous cell using CTRL); then run
the subroutine (Tools | Macro | Rum Macro)
Need help with VBA? See David McRitchie's site on "getting started" with VBA
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Sub wrapper()
For Each mycell In Selection
holdFormula = mycell.Formula
holdFormula = Mid(holdFormula, 2)
newFormula = "=ROUND(" & holdFormula & ",-2)"
mycell.Formula = newFormula
Next
End Sub

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

 

[Gord Dibben]

Sub RoundAdd()
Dim myStr As String
Dim cel As Range
For Each cel In Selection
If cel.HasFormula = True Then
If Not cel.Formula Like "=ROUND(*" Then
myStr = Right(cel.Formula, Len(cel.Formula) - 1)
cel.Value = "=ROUND(" & myStr & ",-3)"
End If
End If
Next
End Sub

If you're not familiar with VBA and macros, see David McRitchie's site for
more on "getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run or edit the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Change -3 to -2 as Bernard pointed out.

Gord

Sub RoundAdd()
Dim myStr As String
Dim cel As Range
For Each cel In Selection
If cel.HasFormula = True Then
If Not cel.Formula Like "=ROUND(*" Then
myStr = Right(cel.Formula, Len(cel.Formula) - 1)
cel.Value = "=ROUND(" & myStr & ",-3)"
End If
End If
Next
End Sub

If you're not familiar with VBA and macros, see David McRitchie's site for
more on "getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run or edit the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Just don't run it on any cells that don't have a formula or any that already
have ROUND function.


Gord