# rounding macro

#### 1inus

##### New Member
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

### Excel Facts

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

##### Board Regular
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
What don't you use the copy command, copying the rounded formula to other applicable cells (you want rounded)?

#### 1inus

##### New Member
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

##### Board Regular

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
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
figured out my problem with the divide by thousand, just need help on doing this to every cell selected.

thanks

##### Board Regular
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

Replies
14
Views
98
Replies
0
Views
65
Replies
5
Views
62
Replies
7
Views
100
Replies
23
Views
254