Round up to nearest multiple of 5

[taylorkand]

I am writing a macro and I need it to take a number and round it up to
the nearest multiple of 5.

For example:

If the number is 74 the macro would change it to 75
If the number is 65.7 the macro would change it to 70 etc.

Can someone show me how to do this?

Thanks,
Kyle

 

[Bernie Deitrick]

Kyle,

Working with numbers on a worksheet: select them first then run

Sub myRoundUp()
Dim myC As Range
For Each myC In Selection
myC.Value = Application.WorksheetFunction.RoundUp(myC.Value * 2, -1) / 2
Next myC
End Sub

working with variables in a macro:

Sub myRoundUp2()
Dim myV As Double
myV = 68.7
myV = Application.WorksheetFunction.RoundUp(myV * 2, -1) / 2
MsgBox myV
End Sub

HTH,
Bernie
MS Excel MVP

 

[Rick Rothstein \(MVP - VB\)]

Give this a try (simply select all the cells you want to round in this
manner and run the macro)...

Sub RndToNearest5()
Dim R As Range
For Each R In Selection
If IsNumeric(R.Value) Then R.Value = 5 * (Int(CDbl(R.Value) + 2.5) \ 5)
Next
End Sub

Rick

 

[taylorkand]

Thanks for all your help, it's working perfectly now!

Kyle