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

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
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"rcc" wrote in message
...
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

You might consider using

ToolsOptionsCalculation tab, and check Precision as displayed.

Do read HELP first so you understand the implications.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"rcc" wrote in message ...
| 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

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

"rcc" wrote in message
...
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

Thanks, Bernard.

"Bernard Liengme" wrote:

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

"rcc" wrote in message
...
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

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 InsertModule. 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 ToolMacroMacros.

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


Gord Dibben MS Excel MVP

On Wed, 17 Oct 2007 11:34:01 -0700, rcc wrote:

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

Change -3 to -2 as Bernard pointed out.

Gord

On Wed, 17 Oct 2007 12:04:53 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

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 InsertModule. 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 ToolMacroMacros.

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


Gord Dibben MS Excel MVP

On Wed, 17 Oct 2007 11:34:01 -0700, rcc wrote:

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

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


Gord

On Wed, 17 Oct 2007 15:56:47 -0300, "Bernard Liengme"
wrote:

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