If I have a column of numbers, and I want to put =round() function on all of
them, is there a way to do it so that I am not creating another column with
reference to the existing column of numbers?

=ROUND(A1:A10,1) entered with CTRL+SHIFT+ENTER, but this applies only a
single value in a single cell. Not sure that's what you want.
--
Brevity is the soul of wit.


"J@Y" wrote:

If I have a column of numbers, and I want to put =round() function on all of
them, is there a way to do it so that I am not creating another column with
reference to the existing column of numbers?

Thanks for the reply. That way you have there requires me to create another
column to input the =round() function. I want to have the =Round() function
added to existing numbers or equations in a specific column. For example: if
I have equations or numbers in column A1:A3 =

Colummn A
= 2*1.5
= 3.5*2.5
= 2.2*3.5

I want it so that it will become:

Colummn A
= round(2*1.5,1)
= round(3.5*2.5,1)
= round(2.2*3.5,1)

without having to go through everyline and adding it through.



"Dave F" wrote:

=ROUND(A1:A10,1) entered with CTRL+SHIFT+ENTER, but this applies only a
single value in a single cell. Not sure that's what you want.
--
Brevity is the soul of wit.


"J@Y" wrote:

If I have a column of numbers, and I want to put =round() function on all of
them, is there a way to do it so that I am not creating another column with
reference to the existing column of numbers?

JY

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 & "," & "2" & ")"
End If
End If
Next
End Sub

Change the "2" to whatever suits you before running the macro.

If 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 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 Mon, 22 Jan 2007 13:29:02 -0800, J@Y wrote:

If I have a column of numbers, and I want to put =round() function on all of
them, is there a way to do it so that I am not creating another column with
reference to the existing column of numbers?

Great thanks, works like a charm.

"Gord Dibben" wrote:

JY

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 & "," & "2" & ")"
End If
End If
Next
End Sub

Change the "2" to whatever suits you before running the macro.

If 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 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 Mon, 22 Jan 2007 13:29:02 -0800, J@Y wrote:

If I have a column of numbers, and I want to put =round() function on all of
them, is there a way to do it so that I am not creating another column with
reference to the existing column of numbers?

Happy to hear that.

Thanks for the feedback.



On Tue, 23 Jan 2007 11:50:01 -0800, J@Y wrote:

Great thanks, works like a charm.

"Gord Dibben" wrote:

JY

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 & "," & "2" & ")"
End If
End If
Next
End Sub

Change the "2" to whatever suits you before running the macro.

If 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 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 Mon, 22 Jan 2007 13:29:02 -0800, J@Y wrote:

If I have a column of numbers, and I want to put =round() function on all of
them, is there a way to do it so that I am not creating another column with
reference to the existing column of numbers?