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

ex:

8 - 10
23 - 25
42 - 40

Any suggestions.

Thanks.

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.

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

I think this should do it

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

--
Regards

Juan Pablo González

"R-Enemy" wrote in message
...
I need to round numbers to the nearest 5 in VBA.

ex:

8 - 10
23 - 25
42 - 40

Any suggestions.

Thanks.

Actually, much simpler:

=ROUND(A2/5,0)*5

--
Regards

Juan Pablo González

"Juan Pablo González" wrote in message
...
I think this should do it

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

--
Regards

Juan Pablo González

"R-Enemy" wrote in message
...
I need to round numbers to the nearest 5 in VBA.

ex:

8 - 10
23 - 25
42 - 40

Any suggestions.

Thanks.

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.

--
Regards,
Tom Ogilvy

"R-Enemy" wrote in message
...
I need to round numbers to the nearest 5 in VBA.

ex:

8 - 10
23 - 25
42 - 40

Any suggestions.

Thanks.

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



On Wed, 9 Feb 2005 08:59:07 -0800, "R-Enemy"
wrote:

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

ex:

8 - 10
23 - 25
42 - 40

Any suggestions.

Thanks.

On Wed, 09 Feb 2005 15:08:54 -0600, Myrna Larson
wrote:

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

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

On Wed, 09 Feb 2005 17:13:08 -0500, Ron Rosenfeld
wrote:

On Wed, 09 Feb 2005 15:08:54 -0600, Myrna Larson
wrote:

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

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




In article ,
Myrna Larson wrote:

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