rounding macro

1inus

New Member
Joined
Jul 6, 2006
Messages
40
I have several cells that sum (i.e. sum(d55:d64) or sum(aa1:aa15). is there a way to have a macro add the round formula to these cells so they will show:

round(sum(a1:a15)/1000,-.5)

thanks
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

tmcfadden

Board Regular
Joined
Aug 17, 2005
Messages
151
If you just put an "=" in front of your forumula, you should be ok.

I'm not sure why you need a macro to do this.

Also, I'm not sure you are using the Num_digits properly. I believe it should be an integer.

Code:
=ROUND(SUM(a1:a15)/1000,-5)
 

respree

Active Member
Joined
Apr 14, 2003
Messages
258
What don't you use the copy command, copying the rounded formula to other applicable cells (you want rounded)?
 

1inus

New Member
Joined
Jul 6, 2006
Messages
40
the equal sign is already there, the formulas currently will say

row A7=sum(a1:a5)
row A14=sum(a10:a12)
I actually should have written, =round(sum(a10:a12),0) to round to the nearest dollar.

I only was curious about a macro becasuse these formulas go in columns and vary in the amount of rows they sum,
=sum(a1:a5), =sum(b1:b5)
=sum(a10:a12), =sum(b10:b12)

so I can edit the formula in A7 and copy it accross the columns but I can not copy from A7 to A14 because A14 only sums 3 rows and not 5.

I was looking for a macro that would just add the round to the existing formula in cells that I have selected.

thanks
 

tmcfadden

Board Regular
Joined
Aug 17, 2005
Messages
151

ADVERTISEMENT

Code:
Sub AddRound()

ActiveCell.Formula = "=ROUND(" & Mid(ActiveCell.Formula, 2) & ",0)"

End Sub

If you call that function while the cell is selected it will add your ROUND function.
 

1inus

New Member
Joined
Jul 6, 2006
Messages
40
that works great but I try to divide the original sum by 1000

from = sum(a1:a10)
to =round(sum(a1:a10)/1000,0)

I keep putting that 1000 in a wrong spot in the macro.

Also, is there a way to do that to a range of cell I select? (from left to right, columns a1:c1)

thanks
 

1inus

New Member
Joined
Jul 6, 2006
Messages
40
figured out my problem with the divide by thousand, just need help on doing this to every cell selected.

thanks
 

tmcfadden

Board Regular
Joined
Aug 17, 2005
Messages
151
Select all the cells that you want to update. Then run the following macro:
Code:
Sub AddRound()

For Each cell In Selection
    cell.Formula = "=ROUND(" & Mid(cell.Formula, 2) & ",0)"
Next cell

End Sub

-Tim
 

Watch MrExcel Video

Forum statistics

Threads
1,114,403
Messages
5,547,750
Members
410,811
Latest member
adustin42
Top