Re: Rounding numbers up or down

Ron, they are formulas for the most part - I'd like to automate the whole
thing with formulas. I'll try to modify the formula to see if it works.
Thanks for the rapid reply!
T

"Ron Rosenfeld" wrote:

> On Fri, 10 Jun 2005 12:54:01 -0700, "T" <T@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
>
> >All, I have a similar rounding situation as Michelle. I attempted the macro
> >you gave her, unfortunately it did not work.
> >
> >I have ranges of numbers in columns B:F (I edited the macro to say B:F
> >instead of A:A), my numbers are not dollars and cents, just dollars. I want
> >them to round to the nearest 5 or 0 (I edited the macro from 0.05 to just 5).
> > The data is in columns/rows A3:F35 (if that makes any difference).
> >
> >Here is an example of what I want to see:
> >184380 212040 239695 267350 295010
> >
> >from:
> >184381 212038 239695 267352 295009
> >
> >when I ran the "Michelle" macro, it changed nothing, what did I do wrong?
> >Or is there a better way to go about changing these ranges?
> >
> >Thanks.
>
> How do the numbers get into cells B3:F35? Manual entry or formulas?
>
> If they are the results of formulas, then modify the formula to be:
>
> =ROUND(your_formula/5,0)*5
>
> If they are entered manually, one at a time, then the modification of
> "Michelle's" event formula should work:
>
> ===========================
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim AOI As Range
> Dim c As Range
>
> Set AOI = [B:F] ' or [B3:F53]
>
> Application.EnableEvents = False
>
> If Intersect(Target, AOI) Is Nothing Then GoTo DONE
>
> For Each c In Target
> If Not Intersect(c, AOI) Is Nothing Then
> If IsNumeric(c) And Not IsEmpty(c) Then
> c.Value = Application.WorksheetFunction.Round(c.Value / 5, 0) * 5
> End If
> End If
> Next c
>
> DONE: Application.EnableEvents = True
> End Sub
> ==============================
>
> Post back with some more info.
>
> Best,
>
> --ron
>
.