Round number to 5

[Guest]

I need to round numbers to the nearest 5 in VBA.

ex:

8 -> 10
23 -> 25
42 -> 40

Any suggestions.

Thanks.

 

[Lonnie M.]

Hi, there is probably a more elegant way to do this, but give this a
try:

Sub roundTest()
Dim myRound&
Dim X%
For X = 1 To 3
If WorksheetFunction.RoundUp(Cells(X + 2, 2) / 100, 1) * 100 <= 5 +
Cells(X + 2, 2) Then
myRound = WorksheetFunction.RoundUp(Cells(X + 2, 2) / 100, 1) * 100
Else
myRound = WorksheetFunction.RoundDown(Cells(X + 2, 2) / 100, 1) * 100
End If
Debug.Print Cells(X + 2, 2) & " Rounded to : " & myRound

Next X
End Sub

HTH--Lonnie M.

 

[Lonnie M.]

P.S. my example was using your values starting in cell B3.

 

[Juan Pablo González]

I think this should do it

=IF(CEILING(A2,5)-A2<=2.5,CEILING(A2,5),FLOOR(A2,5))

 

[Juan Pablo González]

Actually, much simpler:

=ROUND(A2/5,0)*5

 

[Tom Ogilvy]

use

Application.round(number,1)

which uses the Excel rounding function rather than

round(number,1)

which uses the VBA rounding function which rounds to the nearest even value.

 

[Myrna Larson]

The worksheet formula is =ROUND(A1/5,0)*5. You can do the same thing in VBA,
i.e.

Y = Round(X/5,0)*5

 

[Ron Rosenfeld]

The worksheet formula is =ROUND(A1/5,0)*5. You can do the same thing in VBA,
i.e.

Y = Round(X/5,0)*5

In more recent versions of VBA, one does not get the same answer. Try 12.5:
VBA Round -- 10
worksheet -- 15


--ron

 

[Myrna Larson]

I think that's true in ALL versions of VBA. It uses Banker's Rounding.

 

[JE McGimpsey]

Only VBA6+. VBA5 (XL97, MacXL) doesn't have the ROUND() function.