Rounding issue

[asa.flynt.reynolds]

I've searched the archives and can't really find what I am looking
for. I have a macro that will format a result based upon the number
of digits the result contains. If the result is > 1000 then I need to
round the result to the 10's place (e.g. 1042 would be rounded to
1040). When I put in Round(result, -1), I get a generic error. I know
this is the Excel formula. I have copied the code below. If anyone
can help it would be greatly appreciated.

If result < 0.69 Then
new_result = "<0.69"
Else
If result > 1000 Then
new_result = Round(result, -1) '''''''''This is where
the error occurs.''''''''''
Else
If result > 100 Then
new_result = Round(result, 0)
Else
If result > 10 Then
new_result = Round(result, 1)
Else
new_result = Round(result, 2)
End If
End If
End If
End If

 

[Bob Phillips]

Try this

If result < 0.69 Then
new_result = "<0.69"
Else
If result > 1000 Then
new_result = Application.Round(result, -1)
Else
If result > 100 Then
new_result = Round(result, 0)
Else
If result > 10 Then
new_result = Round(result, 1)
Else
new_result = Round(result, 2)
End If
End If
End If
End If

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Try using the following:

New_Result = Application.WorksheetFunction.Round("A1:A10", -1)

 

[Dave Peterson]

VBA's round and Excel's round are different.

You could use:
new_result = Application.Round(Result, -1)

Or you could use arithmetic (depending on your rules):

new_result = int(result/10)*10
(this truncates--no rounding. 1042 becomes 1040, but so does 1049.)

 

[asa.flynt.reynolds]

Try this

If result < 0.69 Then
new_result = "<0.69"
Else
If result > 1000 Then
new_result = Application.Round(result, -1)
Else
If result > 100 Then
new_result = Round(result, 0)
Else
If result > 10 Then
new_result = Round(result, 1)
Else
new_result = Round(result, 2)
End If
End If
End If
End If

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Thank you, Bob, for your quick response. This little trick worked.
And thank you Kevin and Dave for your advice as well. It is very much
appreciated.

Asa