Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparative Histogram | Charts and Charting in Excel | |||
Change cut/paste operation to cut/insert operation | Excel Programming | |||
Comparative Formulas | Excel Worksheet Functions | |||
Comparative spreadsheets | Excel Worksheet Functions | |||
COMPARATIVE COUNTING | Excel Worksheet Functions |