Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

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?

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))

Then

If (IsNumeric(Target.Value)) Then

If (Target.Value < 10) Then Range(Target.Address) = 10

Range(Target.Address) = Round(Target.Value / 10) * 10

Else

MsgBox "Value: " & Target.Value & " is not a number"

End If

End If

End Sub

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.

Related Topics: