rounding numbers\nearest 1/4

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi to everyone

I'm at a wall as to exactly how you would round a number (any number or series of numbers) to the nearest 1/4, or 0.25. I know the Round and MRound functions very well, but I'm looking for some way of rounding the numbers "on the fly." In other words, if you open up a worksheet and enter the number 1.28 into cell A1, it will appear as 1.25 as soon as you hit the <Enter> key

I've tried all of the functions along with the If clause, and I keep getting Circular Reference errors. Any information is appreciated. Thanks

fjm
 
one way:

You can't do it with worksheet functions. Put this in your worksheet
code module (right-click on the worksheet tab and choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(.Cells, Range("A1:A10,J1:J10")) Is Nothing Then
Application.EnableEvents = False
.Value = Application.Round(.Value * 4, 0) / 4
Application.EnableEvents = True
End If
End With
End Sub


Change your range to suit.
 
Frank Murray said:
They did not automatically round; in fact, nothing happens. Is there
something I missed? Let me know when you get a chance. Thanks again.
Click to expand...

It should work fine as long as you're entering values in the range
defined in the macro, and as long as you have macros enabled. Do you by
chance have your security setting set to high?
 
Back
Top