Rounding up to nearest 10 when "Enter" is pressed or user has clickedoff the cell

[Maddoktor]

Hi all,

I am using the ROUNDUP(A1,-1) formula to round up to the next ten of the
value entered by a user into A1 i.e. 317.2 rounds up to 320. Is it
possible to automatically round up to the next 10 the value the user
enters into A1 i.e. automatically change from 317.2 to 320 once the user
presses Enter or clicks off the cell.


Thanks in advance

 

[JE McGimpsey]

One way:

Put this in your worksheet code module:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Range("A1")
If Not Intersect(.Cells, Target) Is Nothing Then
If IsNumeric(.Value) Then
On Error Resume Next
Application.EnableEvents = False
.Value = Application.RoundUp(.Value, -1)
Application.EnableEvents = True
On Error GoTo 0
End If
End If
End With
End Sub

 

[Maddoktor]

Thank you. Exactly what I was looking for.

One way:

Put this in your worksheet code module:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Range("A1")
If Not Intersect(.Cells, Target) Is Nothing Then
If IsNumeric(.Value) Then
On Error Resume Next
Application.EnableEvents = False
.Value = Application.RoundUp(.Value, -1)
Application.EnableEvents = True
On Error GoTo 0
End If
End If
End With
End Sub