Add Round Function to All Cells

[rrrexcel]

Is there a way to add the round function to the current formula in multiple cells at once? I have a worksheet that has many cells that are linked to another worksheet or workbook, and I need all of the cells to be rounded to 2 digits. Can I do this with a macro?

 

[jproffer]

Sub add_round()
Dim rng As Range
    For Each rng In Selection
        rng.Value = "=Round(" & Mid(rng.Formula, 2, 9999) & ",2)"
    Next rng
    
End Sub

Select all the cells you want to ammend and try that. PLEASE try it on a copy of your worksheet though

 

[beedistinct]

Is there a way to add the round function to the current formula in multiple cells at once? I have a worksheet that has many cells that are linked to another worksheet or workbook, and I need all of the cells to be rounded to 2 digits. Can I do this with a macro?

This is a crude way I can think of:

If your formula is like this =SUM(A1:E1) (for simplicity's sake!)

1. Type a number 0 in a cell and copy it
2. Select all the formulas you want to add ROUND function to and use paste special (add) option "Add".
Te formula will look like =SUM(A1:E1)+0
3. Replace all "=" signs with a character of your choice that has less chance of being already present in those formulas like "=ROUND(" and then replace ")+0" to ",2)"

 

[rrrexcel]

Thank you for your quick responses. I just tried the solution from beedistinct, and that definitely works. I will also try the solution from jproffer. Thank you again, so much!

 

[rrrexcel]

Wow, the solution from jproffer worked brilliantly. Thank you, Thank you.

 

[imskin]

jproffer, thanks..

 

[StuMegu]

Sub add_round()
Dim rng As Range
    For Each rng In Selection
        rng.Value = "=Round(" & Mid(rng.Formula, 2, 9999) & ",2)"
    Next rng
    
End Sub

Select all the cells you want to ammend and try that. PLEASE try it on a copy of your worksheet though

hi, just wanted to add my thanks to jproffer for this, in my case though it trimmed off the first digit of the number so i modified the code as follows (and i didn't want any decimals keeping):

Sub add_round()
Dim rng As Range
For Each rng In Selection
rng.Value = "=Round(" & rng.Value & ",0)"
Next rng
End Sub

Once the above was done I copied the whole lot and paste-special-values over the top to remove the "=round.." function..