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

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


wrote in message
...
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

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


wrote in message
...
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

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

Kyle

On Aug 26, 4:44*pm, "Rick Rothstein \(MVP - VB\)"
wrote:
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

wrote in message

...



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- Hide quoted text -

- Show quoted text -