Rounding numbers in Excel 1

[lamaar]

I wish to make all numbers entered in Excel be rounded up to the nearest 50.
I can do it cell by cell, but have to enter a formula (round(cell, 50).

Does anyone know of any VB code I can enter under 'Worksheet' 'Selection_change' which will do this for me?

Thanks Lamaar75@hotmail.com

 

[xlbo]

Hmmmm - the only function that I can think of that will round to 50 is MROUND and that rounds DOWN as well as UP. also - MROUND is not available to VBA - you may have a problem here..... Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[Kudel]

Hi

If you do:

Round((YOUR_NUMBER + 24,9999999999)/50;0) * 50

This works as long as you do not enter 0,0000000001 in one of the cells

-Kudel

 

[AnanthaP]

Hi,

(round(x*2,-2)/2) doos the result.

"X" bees the number.

I once beed round to nearest 25 paise (equivalent to cents in India). So i dood

(round(x*4,0)/4).

Hope this helps.

End

 

[Loomah]

Hi
I think this function will do the trick. You would be able to call it from the change event (though not tested) passing the 2 arguments.

Function MRoundUP(rng As Range, ByVal fact As Integer) As Integer
    If rng.Cells.Count <> 1 Then
        MRoundUP = &quot;&quot;
        Exit Function
    End If
    If rng.Value Mod fact = 0 Then
        MRoundUP = rng.Value
    Else
        MRoundUP = rng.Value - (rng.Value Mod fact) + fact
    End If
End Function

;-)
(I'm still learning this function thing!!) If a man says something and there are no women there to hear him, is he still wrong?

 

[xlbo]

Cool - in that case, use the worksheet CHANGE event rather than selection change and use this code:


target.value = worksheetfunction.ROUND(((target.value+24.9999999999)/50),0)*50

This will round any figure you enter UP to the nearest 50 Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[Loomah]

Of course instead of passing fact to my function as an argument it could be dim'd and just assigned a value ie fact = 50

The world seems such a wonderful place with lots of different ideas at the moment!!

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[mbarron]

Excel has a built in function for rounding up (or down) to and interval.

ceiling(number,interval) or floor(number,interval)

=CEILING(your cell,50) to round up to intervals of 50
=FLOOR(your cell,50) to round down to intervals of 50 Mike

 

[Loomah]

Of course there are. It's obvious!!!

You'd have to be stupid to write a function for something that already exists

Have a purple point pip for pointing us peeps to the prize!!

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[xlbo]

D'OH - I even looked at those 2 functions and then didn't do anything about it - what's that you say...there's something oozing out of my ear ??? aaaaaaaah - must be what's left of my brain that's what excel does to you.....ya think ya've got it licked and then it just turns round and bites you on da bum....I'm blaming it all on sunstroke !!
LOL LOL LOL LOL Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[lamaar]

Guys,

Thanks for the info.

Xlbo, your'e a star. I used your line of code.

As I mentioned, I wanted it to refer to all cells and not to any particular one, therefore I did not want to reference any cell in particular.

I have want I want!!
Lamaar75@hotmail.com

 

[xlbo]

Yeh but instead of:
target.value = worksheetfunction.ROUND(((target.value+24.9999999999)/50),0)*50
you can use:
target.value = worksheetfunction.CEILING(target.value,50)

Easier and simpler no? Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde