Code not rounding

[Otto Moehrbach]

Excel 2002, WinXP
I have the 3 following lines of code:
MsgBox Round(SumGrossReturn - 1, 3)
DestPut.Offset(, 6) = Round(SumGrossReturn - 1, 3)
DestPut.Offset(, 6).NumberFormat = "0.0%"

The message box returns 0.227
The destination cell gets 22.6999999999%
Why doesn't the destination cell get 22.7% ?
Thanks for your help. Otto

 

[Jim Cone]

Hi Otto,

Because the MsgBox function only displays the rounded number,
it does not change it. This should work...

SumGrossReturn = Round(SumGrossReturn - 1, 3)

Note: Unless you save the original value in another variable,
you lose it.
Another note: Excel 97 VBA does not have a "Round" function.
You have to use: WorksheetFunction.Round(....). So if you expect
your code to run on other machines then...

Regards,
Jim Cone
San Francisco, CA

----- Original Message -----
From: "Otto Moehrbach" <[email protected]>
Newsgroups: microsoft.public.excel.programming
Sent: Saturday, July 24, 2004 2:19 PM
Subject: Code not rounding

 

[Jim Cone]

Otto,
Please disregard my earlier response, I misread your question.
Regards,
Jim Cone

 

[Ron Rosenfeld]

Excel 2002, WinXP
I have the 3 following lines of code:
MsgBox Round(SumGrossReturn - 1, 3)
DestPut.Offset(, 6) = Round(SumGrossReturn - 1, 3)
DestPut.Offset(, 6).NumberFormat = "0.0%"

The message box returns 0.227
The destination cell gets 22.6999999999%
Why doesn't the destination cell get 22.7% ?
Thanks for your help. Otto

Perhaps there is something else going on in your code. The following seems to
work just fine for me, with 0.227 showing in the message box, and 22.7%
appearing in G6.

=========================
Sub TestRound()
Dim DestPut As Range
Const SumGrossReturn As Double = 1.226999999

Set DestPut = [A6]

MsgBox Round(SumGrossReturn - 1, 3)

DestPut.Offset(, 6) = Round(SumGrossReturn - 1, 3)
DestPut.Offset(, 6).NumberFormat = "0.0%"

End Sub
========================
--ron