Problem With Decimal Point

[sifuconman]

Hi,

I am a newbie in VBA programing. I have written a subroutine to calculate gas pressure in soft drink package.

- If the pressure entered in column D is between 11.6 and 12 then OK is displayed in column E

- If the pressure entered in column D is greater than 12 or less than 11.6 then the difference between 11.8 and the pressure entered will be displayed in column E together with the alphabet H or L as the case may be.

My problem is that if the pressure entered in column D is 12.1 the display in column E is expected to be 0.3 H but instead I got 0.299999999999999H. If the pressure entered in column D is 11.5 the display in column E became -0.300000000000001L instead of -0.3L .

Can someone tell me what is wrong with my subroutine ?

Thanks

Subroutine is as follows:

Do While Not IsEmpty(Cells(n, "B"))
Dim n As Integer
n = 6


If Cells(n, "B").Value = "APPLE" And (Cells(n, "C").Value = "PET 500" _
Or Cells(n, "C").Value = "PET 1500") And Cells(n, "D").Value <= 12 _
And Cells(n, "D").Value >= 11.6 Then
Cells(n, "E").Value = "OK"

ElseIf Cells(n, "B").Value = "APPLE" And (Cells(n, "C").Value = "PET 500" _
Or Cells(n, "C").Value = "PET 1500") And Cells(n, "D").Value > 12 Then
Cells(n, "E").Value = Cells(n, "D") - 11.8 & "H"

ElseIf Cells(n, "B").Value = "APPLE" And (Cells(n, "C").Value = "PET 500" _
Or Cells(n, "C").Value = "PET 1500") And Cells(n, "D").Value < 11.6 _
And Cells(n, "D").Value > 0 Then
Cells(n, "E").Value = Cells(n, "D") - 11.8 & "L"

End If

n=n+1
Loop
End sub

[Bob Phillips]

Can you just round it, like so

ElseIf Cells(n, "B").Value = "APPLE" And _
(Cells(n, "C").Value = "PET 500" Or Cells(n, "C").Value = "PET 1500")
And _
Cells(n, "D").Value > 12 Then
Cells(n, "E").Value = Round(Cells(n, "D") - 11.8, 1) & "H"



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"sifuconman" <[email protected]> wrote
in message news:[email protected]...
>
> Hi,
>
> I am a newbie in VBA programing. I have written a subroutine to
> calculate gas pressure in soft drink package.
>
> - If the pressure entered in column D is between 11.6 and 12 then OK
> is displayed in column E
>
> - If the pressure entered in column D is greater than 12 or less than
> 11.6 then the difference between 11.8 and the pressure entered will be
> displayed in column E together with the alphabet H or L as the case may
> be.
>
> My problem is that if the pressure entered in column D is 12.1 the
> display in column E is expected to be 0.3 H but instead I got
> 0.299999999999999H. If the pressure entered in column D is 11.5 the
> display in column E became -0.300000000000001L instead of -0.3L .
>
> Can someone tell me what is wrong with my subroutine ?
>
> Thanks
>
> Subroutine is as follows:
>
> Do While Not IsEmpty(Cells(n, "B"))
> Dim n As Integer
> n = 6
>
>
> If Cells(n, "B").Value = "APPLE" And (Cells(n, "C").Value = "PET
> 500" _
> Or Cells(n, "C").Value = "PET 1500") And Cells(n, "D").Value <= 12
> _
> And Cells(n, "D").Value >= 11.6 Then
> Cells(n, "E").Value = "OK"
>
> ElseIf Cells(n, "B").Value = "APPLE" And (Cells(n, "C").Value =
> "PET 500" _
> Or Cells(n, "C").Value = "PET 1500") And Cells(n, "D").Value > 12
> Then
> Cells(n, "E").Value = Cells(n, "D") - 11.8 & "H"
>
> ElseIf Cells(n, "B").Value = "APPLE" And (Cells(n, "C").Value =
> "PET 500" _
> Or Cells(n, "C").Value = "PET 1500") And Cells(n, "D").Value < 11.6
> _
> And Cells(n, "D").Value > 0 Then
> Cells(n, "E").Value = Cells(n, "D") - 11.8 & "L"
>
> End If
>
> n=n+1
> Loop
> End sub
>
>
> --
> sifuconman
> ------------------------------------------------------------------------
> sifuconman's Profile:
http://www.excelforum.com/member.php...o&userid=37017
> View this thread: http://www.excelforum.com/showthread...hreadid=567803
>

[Bob Phillips]

Can you just round it, like so

ElseIf Cells(n, "B").Value = "APPLE" And _
(Cells(n, "C").Value = "PET 500" Or Cells(n, "C").Value = "PET 1500")
And _
Cells(n, "D").Value > 12 Then
Cells(n, "E").Value = Round(Cells(n, "D") - 11.8, 1) & "H"



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"sifuconman" <[email protected]> wrote
in message news:[email protected]...
>
> Hi,
>
> I am a newbie in VBA programing. I have written a subroutine to
> calculate gas pressure in soft drink package.
>
> - If the pressure entered in column D is between 11.6 and 12 then OK
> is displayed in column E
>
> - If the pressure entered in column D is greater than 12 or less than
> 11.6 then the difference between 11.8 and the pressure entered will be
> displayed in column E together with the alphabet H or L as the case may
> be.
>
> My problem is that if the pressure entered in column D is 12.1 the
> display in column E is expected to be 0.3 H but instead I got
> 0.299999999999999H. If the pressure entered in column D is 11.5 the
> display in column E became -0.300000000000001L instead of -0.3L .
>
> Can someone tell me what is wrong with my subroutine ?
>
> Thanks
>
> Subroutine is as follows:
>
> Do While Not IsEmpty(Cells(n, "B"))
> Dim n As Integer
> n = 6
>
>
> If Cells(n, "B").Value = "APPLE" And (Cells(n, "C").Value = "PET
> 500" _
> Or Cells(n, "C").Value = "PET 1500") And Cells(n, "D").Value <= 12
> _
> And Cells(n, "D").Value >= 11.6 Then
> Cells(n, "E").Value = "OK"
>
> ElseIf Cells(n, "B").Value = "APPLE" And (Cells(n, "C").Value =
> "PET 500" _
> Or Cells(n, "C").Value = "PET 1500") And Cells(n, "D").Value > 12
> Then
> Cells(n, "E").Value = Cells(n, "D") - 11.8 & "H"
>
> ElseIf Cells(n, "B").Value = "APPLE" And (Cells(n, "C").Value =
> "PET 500" _
> Or Cells(n, "C").Value = "PET 1500") And Cells(n, "D").Value < 11.6
> _
> And Cells(n, "D").Value > 0 Then
> Cells(n, "E").Value = Cells(n, "D") - 11.8 & "L"
>
> End If
>
> n=n+1
> Loop
> End sub
>
>
> --
> sifuconman
> ------------------------------------------------------------------------
> sifuconman's Profile:
http://www.excelforum.com/member.php...o&userid=37017
> View this thread: http://www.excelforum.com/showthread...hreadid=567803
>

[sifuconman]

Hi Bob

My problem solved after changing the code as advised.

Thank you very much !