Round several cells

[Guentjo]

I have put together a sheet that references several other tabs. The problem is, I should have round each cell to the 1000s (-3). I can't simply change one cell and copy/paste. Is there a way to add the round function to the beginning of every cell in a range?

I could replace "=" with "=round(", but how can I get the ",-3)" on the back w/o errors?

 

[Yogi Anand]

Hi Guentjo:

If you are open to a VBA solution, try the following ...

Sub AddFormulaToExistingRangeofEntries2()
    '=ROUND(existingEntry,3)
    'assuming the range is c2:c8
    Set yRange = [c2:c8]
    For Each cl In yRange
        cl.Value = "=round(" & Mid(cl.Formula, 2, Len(cl) - 1) & ",3)"
    Next cl
End Sub

Edited code per discussion with OP

 

[Guentjo]

I'm open to VBA and I like that very much.

How could I change this so that it only effects the activecell?

Thanks.

 

[Yogi Anand]

I'm open to VBA and I like that very much.
How could I change this so that it only effects the activecell?
Thanks.

Hi Guentjo:

Here we go ...

Sub AddFormulaToActiveCell()
    '=ROUND(existingEntry,3)
    ActiveCell = "=round(" & Mid(ActiveCell.Formula, 2, Len(ActiveCell) - 1) & ",3)"
End Sub

 

[Yogi Anand]

And if you want to use it for a range of cells, then use the following instead ...

Sub AddFormulaToExistingRangeofEntries2()
    '=ROUND(existingEntry,3)
    'assuming the range is c2:c8
    Set yRange = [c2:c8]
    For Each cl In yRange
        cl.Value = "=round(" & Mid(cl.Formula, 2, Len(cl) - 1) & ",3)"
    Next cl
End Sub

 

[Guentjo]

Great job. I modified it slightly to accomodate longer formula strings.

Sub AddFormulaToActiveCell()
    '=ROUND(existingEntry,-3)
    ActiveCell = "=round(" & Mid(ActiveCell.Formula, 2, Len(ActiveCell.Formula) - 1) & ",-3)"
End Sub

Thanks