Rounding issue
 

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

Rounding issue
 

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)



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

Rounding issue
 

Try using the following:

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

--
Kevin Backmann


" wrote:

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

Rounding issue
 

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.)

wrote:

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

--

Dave Peterson

Rounding issue
 

On Aug 28, 11:24 am, "Bob Phillips" wrote:
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)

wrote in message

ups.com...

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

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