Issue with CASE

[rollingzep]

Hi,

I am getting only "OPEN" for all values of BG and no other values.

This is my requirement

• Huge negative number (usually -44865) populate “BSA – TERM” column as “OPEN”
• If value is “1”(or “3” if report was on a Friday, or even “4” if it was a long weekend) populate “BSA – TERM” column as “O/N”
• If value between “2” (unless weekend or long weekend—one more than the value for the “O/N” classification) and “7”, populate “BSA – TERM” column as “2-7D”
• If value between “8” and “30”, populate “BSA – TERM” column as “8D-1M”
• If value between “31” and “90”, populate “BSA – TERM” column as “2-3M”
• If value between “91” and “180”, populate “BSA – TERM” column as “3-6M”
• If value is “181” or more, populate “BSA – TERM” column as “6M+”

This is my Code

<CODE>
LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
Select Case ws.Range("BG" & i).Value
Case Is > -44500
ws.Range("BH" & i).Value = "OPEN"
Case 1, 3, 4
ws.Range("BH" & i).Value = "O/N"
Case 2 To 7
ws.Range("BH" & i).Value = "2-7D"
Case 8 To 30
ws.Range("BH" & i).Value = "8D-1M"
Case 31 To 90
ws.Range("BH" & i).Value = "2-3M"
Case 91 To 180
ws.Range("BH" & i).Value = "3-6M"
Case Is > 181
ws.Range("BH" & i).Value = "6M+"
End Select

Next i

</CODE>

 

[rlv01]

You have to pay attention to execution order of the case conditions. Case Is > -44500 probably needs to be the last case condition, not the first.

 

[Fluff]

Should this Case Is > -44500 be Case Is < -44500 all your numbers are greater than -44500

 

[rollingzep]

Thanks for the replies!
After I reversed the order, the values are now correctly populated.

But for this,
"Huge negative number (usually -44865) populate “BSA – TERM” column as “OPEN”."
I am not getting any values. The actual value is -44872, which may be less or more than -44865.

<CODE>
LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
Select Case ws.Range("BG" & i).Value
Case 1, 3, 4
ws.Range("BH" & i).Value = "O/N"
Case 2 To 7
ws.Range("BH" & i).Value = "2-7D"
Case 8 To 30
ws.Range("BH" & i).Value = "8D-1M"
Case 31 To 90
ws.Range("BH" & i).Value = "2-3M"
Case 91 To 180
ws.Range("BH" & i).Value = "3-6M"
Case Is > 181
ws.Range("BH" & i).Value = "6M+"
Case Is > -44500
ws.Range("BH" & i).Value = "OPEN"
End Select

Next i
</CODE>

If I make it as Case -44872. it works correctly.

 

[Fluff]

But for this,
"Huge negative number (usually -44865) populate “BSA – TERM” column as “OPEN”."

Which is why I suggested using < rather than >