rollingzep
Board Regular
- Joined
- Nov 18, 2013
- Messages
- 214
- Office Version
- 365
- Platform
- Windows
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>
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>