Rounding issue

  • Thread starter Thread starter asa.flynt.reynolds
  • Start date Start date
A

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
 
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)
 
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.)
 
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)
Click to expand...

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
 
Back
Top