Add same formula to every cell in the range

[Eastar]

data shown as below
a b
1 5.2312 6.1231
2 15.2323 15.46463

How can I add a round() formula to every cell without doing that to each of
them seprately?

thanks,

 

[Gary''s Student]

Select the cells you want to convert and run this macro:

Sub round_um()
Set rr = Selection
For Each r In Selection
r.Formula = "=ROUND(" & r.Value & ",0)"
Next
End Sub

 

[Eastar]

Thank you Gary, this works.
by the way, can we do this without macro?

thanks,

 

[Gary''s Student]

You can do it by manual typing, but that's a huge in in the a$$. Using the
macro is not too bad:


Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To use the macro from the normal Excel window:

1. ALT-F8
2. Select the macro
3. Touch Run



To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

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

 

[Eastar]

Thank you very much Gary. That is great!

You can do it by manual typing, but that's a huge in in the a$$. Using the
macro is not too bad:


Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To use the macro from the normal Excel window:

1. ALT-F8
2. Select the macro
3. Touch Run



To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

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

 

[Gord Dibben]

Using another column as a helper.

Assuming numbers are in column A and column B as shown

In C1 enter =ROUND(A1,0) copy to D1

Select C1 and D1 then Double-click to copy down.


Gord Dibben MS Excel MVP

 

[Jake]

I notice that when the below rounding macro is used on a formula instead of a
number, it converts the formula to its calculated number. Is there a macro
that will add the +ROUND() to cells while preserving the formula within the
cell? Thanks in advance for your help!

 

[dortelina]

Add round formula to existing formula-- macro needed

Hello,

I have tried to use the macro below as it amost give me what i am looking for, but is there a chance that I can keep the original links (formulas) instead of values?

Could yo uplease help?
Thank you!

Sub round_um()
Set rr = Selection
For Each r In Selection
r.Formula = "=ROUND(" & r.Value & ",0)"
Next
End Sub