Problem with ElseIf

[bb29829]

Can someone tell me what I am doing wrong? POSITION_RR_AMOUNT has to be between 2 values as well as POSITION_RR_LEVEL has to =A in order for RRCALC to = the number listed. I am only getting "0" no matter the value of POSITION_RR_AMOUNT. Thanks in advance!!

Private Sub RRCALC_Click()
If POSITION_RR_AMOUNT <= 180000 And POSITION_RR_LEVEL = A Then
RRCALC = "1"
ElseIf POSITION_RR_AMOUNT <= 360000 > 180000 And POSITION_RR_LEVEL = A Then
RRCALC = "2"
ElseIf POSITION_RR_AMOUNT <= 720000 > 360000 And POSITION_RR_LEVEL = A Then
RRCALC = "3"
ElseIf POSITION_RR_AMOUNT <= 1400000 > 720000 And POSITION_RR_LEVEL = A Then
RRCALC = "4"
ElseIf POSITION_RR_AMOUNT <= 2900000 > 1400000 And POSITION_RR_LEVEL = A Then
RRCALC = "5"
ElseIf POSITION_RR_AMOUNT <= 5800000 > 2900000 And POSITION_RR_LEVEL = A Then
RRCALC = "6"
ElseIf POSITION_RR_AMOUNT <= 11500000 > 5800000 And POSITION_RR_LEVEL = A Then
RRCALC = "7"
ElseIf POSITION_RR_AMOUNT <= 23000000 > 11500000 And POSITION_RR_LEVEL = A Then
RRCALC = "8"
ElseIf POSITION_RR_AMOUNT <= 46100000 > 23000000 And POSITION_RR_LEVEL = A Then
RRCALC = "9"
ElseIf POSITION_RR_AMOUNT <= 92200000 > 46100000 And POSITION_RR_LEVEL = A Then
RRCALC = "10"
ElseIf POSITION_RR_AMOUNT <= 184300000 > 92200000 And POSITION_RR_LEVEL = A Then
RRCALC = "11"
ElseIf POSITION_RR_AMOUNT <= 368600000 > 184300000 And POSITION_RR_LEVEL = A Then
RRCALC = "12"
ElseIf POSITION_RR_AMOUNT <= 720000000 > 368600000 And POSITION_RR_LEVEL = A Then
RRCALC = "13"
ElseIf POSITION_RR_AMOUNT <= 1400000000 > 720000000 And POSITION_RR_LEVEL = A Then
RRCALC = "14"
ElseIf POSITION_RR_AMOUNT <= 2900000000# > 1400000000 And POSITION_RR_LEVEL = A Then
RRCALC = "15"
ElseIf POSITION_RR_AMOUNT <= 5800000000# > 2900000000# And POSITION_RR_LEVEL = A Then
RRCALC = "16"
ElseIf POSITION_RR_AMOUNT <= 11500000000# > 5800000000# And POSITION_RR_LEVEL = A Then
RRCALC = "17"
Else
RRCALC = "0"
End If
End Sub

 

[TJPoorman]

Try putting a break on your code and hover over your variable to see what the value is. You could set the break on the first line then use F8 to step through the code.

 

[sneuberg]

Where are POSITION_RR_AMOUNT and POSITION_RR_LEVEL getting their values? Are they globals. I suggest putting some Debug.Print statement in this code to see what the values of these are.

 

[TJPoorman]

You're missing an 'AND' in each ELSEIF.
ElseIf POSITION_RR_AMOUNT <= 360000 AND POSITION_RR_AMOUNT > 180000 And POSITION_RR_LEVEL = A Then

 

[moke123]

ElseIf POSITION_RR_AMOUNT > 180000 and <= 360000 And POSITION_RR_LEVEL = A Then

 

[missinglinq]

POSITION_RR_LEVEL = A

Text, such a A, has to be delimited with Quotes! So

POSITION_RR_LEVEL = A

needs to be

POSITION_RR_LEVEL = "A"

As for

RRCALC = "1"

it depends on whether RRCALC is defined as Text (in which case "1" is correct) or as a Number, in which case it would be

RRCALC = 1

Linq ;0)>

 

[TJPoorman]

You could also clean this up dramatically using a case statement instead of ElseIf:

If POSITION_RR_LEVEL = "A" Then
    Select Case POSITION_RR_AMOUNT
        Case Is <= 180000: RRCALC = "1"
        Case 180001 To 360000: RRCALC = "2"
        Case 360001 To 72000: RRCALC = "3"
        Case 720001 To 1400000: RRCALC = "4"
        Case 1400001 To 2900000: RRCALC = "5"
        Case 2900001 To 5800000: RRCALC = "6"
        Case 5800001 To 11500000: RRCALC = "7"
        Case 11500001 To 23000000: RRCALC = "8"
        Case 23000001 To 46100000: RRCALC = "9"
        Case 46100001 To 92200000: RRCALC = "10"
        Case 92200001 To 184300000: RRCALC = "11"
        Case 184300001 To 368600000: RRCALC = "12"
        Case 368600001 To 720000000: RRCALC = "13"
        Case 720000001 To 1400000000: RRCALC = "14"
        Case 1400000001 To 2900000000#: RRCALC = "15"
        Case 2900000001# To 5800000000#: RRCALC = "16"
        Case 5800000001# To 11500000000#: RRCALC = "17"
        Case Else: RRCALC = "0"
Else
    RRCALC = "0"
End If

 

[MarkK]

You also don't need to re-check the lower limit if a previous ElseIf statement would have absorbed that value, for instance, here we do a test . . .

If [COLOR="Blue"]POSITION_RR_AMOUNT <= 180000[/COLOR] Then
   RRCALC = "1"

. . . but now when we execute a subsequent ElseIf block, like . . .

ElseIf POSITION_RR_AMOUNT <= 360000 [COLOR="Blue"]And POSITION_RR_AMOUNT > 180000[/COLOR] Then

. . . the test in blue was already performed, and we already know the value exceeds 180000.

This principle, applied to TJ's excellent post, leaves us with . . .

RRCALC="0"
If POSITION_RR_LEVEL = "A" Then
    Select Case POSITION_RR_AMOUNT
        Case Is <= 180000: RRCALC = "1"
        Case Is <= 360000: RRCALC = "2"
        Case Is <= 720000: RRCALC = "3"
        '...
        Case Is <= 11500000000#: RRCALC = "17"
    End Select
End If

Finally, I would put all this in a table anyway, rather than hard-code it. Then it is much easier to adjust or update if these thresholds ever change.

 

[bb29829]

Thanks to you all! Got it!