MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Rounding on the tens

  Asked By: Chigaru    Date: Feb 12    Category: MS Office    Views: 528

I have a cell where users have to put in a number in blocks of 10.

If they forget, I need to round the number up or down to the nearest
10 based on the normal rounding procedures.

ie if 54 then 50
if 55 then 60
if 1 - 9 then 10

The range is 0-1500

Any thoughts?



2 Answers Found

Answer #1    Answered By: Saila Iqbal     Answered On: Feb 12

You said, "I have a cell"
did you actually mean that you have a column or group of cells?

I'd use the worksheet_change event.
test to see if the column is the one being watched
test to see if the value is NUMERIC!
divide the number  by 10, round  it off, multiply by 10 and put  it back

it would look like:

Private Sub Worksheet_Change(ByVal Target As Range)
If ((Not Intersect(Range(Target.Address), Range("C2:C1000")) Is Nothing))
If (IsNumeric(Target.Value)) Then
If (Target.Value < 10) Then Range(Target.Address) = 10
Range(Target.Address) = Round(Target.Value / 10) * 10
MsgBox "Value: " & Target.Value & " is not a number"
End If
End If
End Sub

Answer #2    Answered By: Wallace Kelly     Answered On: Feb 12

Nope, just a cell. It's an order form and I'm already using
Private Sub Worksheet_Change(ByVal Target As Range).
Case structure based  on the Target.Row, so I know what cells have what ranges
and critria.
All inputs cells are in the same column.

I will look over your code but at first glance it looks good.

Didn't find what you were looking for? Find more on Rounding on the tens Or get search suggestion and latest updates.