Comparative operation failing
 

I'm using the following code to verify a user entered value calculates to a
minimum margin specified on an Excel worksheet. The code works fine unless
the calculated margin is equal to the specified minimum margin in which case
the else event should be triggered but it is not. In a test example when the
user enters values that produce a margin of 25% (0.25) and the specified
minimum margin is 25% (0.25), the message box prompts the user to re-enter a
new value - not the result I was looking for. Any advice on what I might be
doing wrong, or a better way to validate user entries would be very much
appreciated.

Failing code snippet:

If Target.Value < "" And Target.Offset(-1, 0) < "" Then
dblActualMargin = Target.Offset(1, 0)
'Minimum Margin
If CDbl(dblActualMargin) < CDbl(dblMinimumMargin) Then
Debug.Print "The actual margin is " & dblActualMargin
Debug.Print "The minimum margin is " & dblMinimumMargin
'Entered retail price does not produces required margin
MsgBox "The entered unit retail does not meet minimum "
& strMessageMargin & "% margin." & vbCrLf & "Please re-enter unit retail
pricing that meets minimum margin.", vbOKOnly + vbExclamation, "Invalid
Retail Price"
Cells(Target.Row, Target.Column) = ""
Cells(Target.Row, Target.Column).Activate
Exit Sub
Else
'Entered retail price meets required margin
Cells((Target.Row + 2), Target.Column).Activate
End If
Else
'Exit routine
Exit Sub
End If

Comparative operation failing
 

The issue is most likely one of precision.
0.25
might actually be
0.25000000001

Computers do that kind of thing when working with doubles. Try rounding the
two numbers

If round(CDbl(dblActualMargin), 4) < round(CDbl(dblMinimumMargin), 4) Then
--
HTH...

Jim Thomlinson


"Ken Warthen" wrote:

I'm using the following code to verify a user entered value calculates to a
minimum margin specified on an Excel worksheet. The code works fine unless
the calculated margin is equal to the specified minimum margin in which case
the else event should be triggered but it is not. In a test example when the
user enters values that produce a margin of 25% (0.25) and the specified
minimum margin is 25% (0.25), the message box prompts the user to re-enter a
new value - not the result I was looking for. Any advice on what I might be
doing wrong, or a better way to validate user entries would be very much
appreciated.

Failing code snippet:

If Target.Value < "" And Target.Offset(-1, 0) < "" Then
dblActualMargin = Target.Offset(1, 0)
'Minimum Margin
If CDbl(dblActualMargin) < CDbl(dblMinimumMargin) Then
Debug.Print "The actual margin is " & dblActualMargin
Debug.Print "The minimum margin is " & dblMinimumMargin
'Entered retail price does not produces required margin
MsgBox "The entered unit retail does not meet minimum "
& strMessageMargin & "% margin." & vbCrLf & "Please re-enter unit retail
pricing that meets minimum margin.", vbOKOnly + vbExclamation, "Invalid
Retail Price"
Cells(Target.Row, Target.Column) = ""
Cells(Target.Row, Target.Column).Activate
Exit Sub
Else
'Entered retail price meets required margin
Cells((Target.Row + 2), Target.Column).Activate
End If
Else
'Exit routine
Exit Sub
End If

Comparative operation failing
 

Thanks Jim. That did the trick.

Ken

"Jim Thomlinson" wrote:

The issue is most likely one of precision.
0.25
might actually be
0.25000000001

Computers do that kind of thing when working with doubles. Try rounding the
two numbers

If round(CDbl(dblActualMargin), 4) < round(CDbl(dblMinimumMargin), 4) Then
--
HTH...

Jim Thomlinson


"Ken Warthen" wrote:

I'm using the following code to verify a user entered value calculates to a
minimum margin specified on an Excel worksheet. The code works fine unless
the calculated margin is equal to the specified minimum margin in which case
the else event should be triggered but it is not. In a test example when the
user enters values that produce a margin of 25% (0.25) and the specified
minimum margin is 25% (0.25), the message box prompts the user to re-enter a
new value - not the result I was looking for. Any advice on what I might be
doing wrong, or a better way to validate user entries would be very much
appreciated.

Failing code snippet:

If Target.Value < "" And Target.Offset(-1, 0) < "" Then
dblActualMargin = Target.Offset(1, 0)
'Minimum Margin
If CDbl(dblActualMargin) < CDbl(dblMinimumMargin) Then
Debug.Print "The actual margin is " & dblActualMargin
Debug.Print "The minimum margin is " & dblMinimumMargin
'Entered retail price does not produces required margin
MsgBox "The entered unit retail does not meet minimum "
& strMessageMargin & "% margin." & vbCrLf & "Please re-enter unit retail
pricing that meets minimum margin.", vbOKOnly + vbExclamation, "Invalid
Retail Price"
Cells(Target.Row, Target.Column) = ""
Cells(Target.Row, Target.Column).Activate
Exit Sub
Else
'Entered retail price meets required margin
Cells((Target.Row + 2), Target.Column).Activate
End If
Else
'Exit routine
Exit Sub
End If