Rounding numbers up or down

[Ron Rosenfeld]

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

 

[Guest]

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

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

 

[Guest]

Ron, the formula is working beautifully, but it doesn't stay a formula in the
cell. I use this spreadsheet over and over and don't want to have to "type"
the formula over and over - any suggestions?
T

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

 

[Guest]

Sorry for bothering you on this one - I was working on the spreadsheet with
the macro in it and that's why the formula wasn't overwriting the cells.
Thanks again for your help!

Ron, the formula is working beautifully, but it doesn't stay a formula in the
cell. I use this spreadsheet over and over and don't want to have to "type"
the formula over and over - any suggestions?
T

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

 

[Ron Rosenfeld]

Sorry for bothering you on this one - I was working on the spreadsheet with
the macro in it and that's why the formula wasn't overwriting the cells.
Thanks again for your help!

Glad it's working for you. Thank you for the feedback.


--ron

 

[Guest]

Thank you, The Macro To round to Nearest 5 Cents Works On New Entries But
Not On Existing Cells With Formulars
can you Sugest Anything Else


Regards

Giorgos

 

[Guest]

Thank You,

I will try This nd get Back to you In a couple Days

"

 

[Guest]

"The Formular You gave me MROUND(a1/0,05/0)80.05)
Works fine but it erases all formulars in selected cells
which I don't want is there another Way?>

 

[Jose]

Ron

Thanks for posting this macro. This was just what I needed. Works perfectly.
Thanks again!