Round several cells

Guentjo

Active Member
Joined
Jul 27, 2004
Messages
347
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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi Guentjo:

If you are open to a VBA solution, try the following ...
Code:
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

Active Member
Joined
Jul 27, 2004
Messages
347
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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Guentjo said:
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 ...
Code:
Sub AddFormulaToActiveCell()
    '=ROUND(existingEntry,3)
    ActiveCell = "=round(" & Mid(ActiveCell.Formula, 2, Len(ActiveCell) - 1) & ",3)"
End Sub
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
And if you want to use it for a range of cells, then use the following instead ...
Code:
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

Active Member
Joined
Jul 27, 2004
Messages
347
Great job. I modified it slightly to accomodate longer formula strings.

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

Forum statistics

Threads
1,077,782
Messages
5,336,284
Members
399,074
Latest member
rlong98

Some videos you may like

This Week's Hot Topics

Top