Round up to nearest multiple of 5

T

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
 
B

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
 
R

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top