rounding macro

[1inus]

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

 

[tmcfadden]

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.

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

 

[respree]

What don't you use the copy command, copying the rounded formula to other applicable cells (you want rounded)?

 

[1inus]

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]

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]

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]

figured out my problem with the divide by thousand, just need help on doing this to every cell selected.

thanks

 

[tmcfadden]

Select all the cells that you want to update. Then run the following macro:

Sub AddRound()

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

End Sub

-Tim