Using a CASE than If

[rollingzep]

Hi,

I need to code for a certain condition like below

If Col AZ is Banks or Local, AND Col AK is “AAA”, “AA+”, “AA”, or “AA-”, then populate the Col BJ as “1”
If Col AZ is Banks or Local, AND Col AK is “A+”, “A”, or “A-”, then populate the Col C as “2”
If Col AZ is Banks or Local, AND Col AK is “BBB+”, “BBB”, “BBB-”, “BB+”, “BB”, “BB-”, “B+”, “B”, and “B-”, then populate the Col BJ as “3”
If Col AZ is Banks or Local, AND Col AK is “CCC+”, “CCC”, “CCC-”, “CC”, “C”, and “D”, then populate the Col BJ as “4”
If Col AZ is Banks or Local, AND Col AK is not rated, then populate the Col BJ as “5”
If Col AZ is Institutions or Corps, AND Col AK is “AAA”, “AA+”, “AA”, or “AA-”, then populate the Col BJ as “6”
If Col AZ is Institutions or Corps, AND Col AK is “A+”, “A”, “A-”,“A+”, “A”, “A-”, “BBB+”, “BBB”, “BBB-”, “BB+”, “BB”, “BB-”, “B+”, “B”, “B-”, “CCC+”, “CCC”, “CCC-”, “CC”, “C”, “D”, or unrated then populate the Col BJ as “7”

I use the

LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
If (ws.Range("AZ" & i).Value = "Banks" Or ws.Range("AZ" & i).Value = "Local") AND (ws.Range("AK" & i).Value = "AAA" Or ws.Range("AK" & i).Value = "AA+") Then
ws.Range("BJ" & i).Value = "1"
.................................
..............................
End If

Next i

How to use the CASE statement rather than IF? Will that make this faster?

 

[Fluff]

You can nest them like

For i = 2 To Lastrow
   Select Case Ws.Range("AZ" & i).Value
      Case "Banks", "Local"
         Select Case Range("AK" & i)
            Case "AAA", "AA+", "AA", "AA-"
               Ws.Range("BJ" & i).Value = 1
            Case "A+", "A", "A-"
               Ws.Range("BJ" & i).Value = 2
         End Select
      Case "Institutions", "Corps"
         Select Case Ws.Range("AK" & i)
            Case "AAA", "AA+", "AA", "AA-"
               Ws.Range("BJ" & i).Value = 6
            Case "A+", "A", "A-"
               Ws.Range("BJ" & i).Value = 2
         End Select
   End Select
Next i

 

[rollingzep]

Thank you!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[rollingzep]

First off, I apologize to the Moderators (Fluff) for starting a different thread.
I was unaware of the rules!

The NEST is not returning any values.

This is my code

For i = 2 To Lastrow
   Select Case Ws.Range("AZ" & i).Value
      Case "Banks", "Local"
         Select Case Range("AK" & i)
            Case "AAA", "AA+", "AA", "AA-"
               Ws.Range("BJ" & i).Value = 1
            Case "A+", "A", "A-"
               Ws.Range("BJ" & i).Value = 2
         End Select
      Case "Institutions", "Corps"
         Select Case Ws.Range("AK" & i)
            Case "AAA", "AA+", "AA", "AA-"
               Ws.Range("BJ" & i).Value = 6
            Case "A+", "A", "A-"
               Ws.Range("BJ" & i).Value = 2
         End Select
   End Select
Next i

Two questions

How/where to add CASE Else and If Col AZ is Banks or Local, AND Col AK is not rated, then populate the Col BJ as “5”
If col AK is Null, how do I include that condition.

 

[Fluff]

For Banks & Local you just ned to add the other Case lines in the same manner as I showed.
For Institutions or Corps, you can change the 2nd Case like

For i = 2 To Lastrow
   Select Case Ws.Range("AZ" & i).Value
      Case "Banks", "Local"
         Select Case Range("AK" & i)
            Case "AAA", "AA+", "AA", "AA-"
               Ws.Range("BJ" & i).Value = 1
            Case "A+", "A", "A-"
               Ws.Range("BJ" & i).Value = 2
         End Select
      Case "Institutions", "Corps"
         Select Case Ws.Range("AK" & i)
            Case "AAA", "AA+", "AA", "AA-"
               Ws.Range("BJ" & i).Value = 6
            Case Else
               Ws.Range("BJ" & i).Value = 7
         End Select
   End Select
Next i

 

[rollingzep]

For Banks & Local you just ned to add the other Case lines in the same manner as I showed.
For Institutions or Corps, you can change the 2nd Case like

For i = 2 To Lastrow
   Select Case Ws.Range("AZ" & i).Value
      Case "Banks", "Local"
         Select Case Range("AK" & i)
            Case "AAA", "AA+", "AA", "AA-"
               Ws.Range("BJ" & i).Value = 1
            Case "A+", "A", "A-"
               Ws.Range("BJ" & i).Value = 2
         End Select
      Case "Institutions", "Corps"
         Select Case Ws.Range("AK" & i)
            Case "AAA", "AA+", "AA", "AA-"
               Ws.Range("BJ" & i).Value = 6
            Case Else
               Ws.Range("BJ" & i).Value = 7
         End Select
   End Select
Next i

I modified as

    LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
    
    For i = 2 To LastRow
    Select Case ws.Range("AZ" & i).Value
       Case "Commercial Banks", "State and Local Gov. Agency"
          Select Case ws.Range("AK" & i).Value
             Case "AAA", "AA+", "AA", "AA-"
                ws.Range("BJ" & i).Value = 1
             Case "A+", "A", "A-"
                ws.Range("BJ" & i).Value = 2
             Case "BBB+", "BBB", "BBB-", "BB+", "BB", "BB-", "B+", "B", "B-"
                ws.Range("BJ" & i).Value = 3
             Case "CCC+", "CCC", "CCC-", "CC", "C", "D"
                ws.Range("BJ" & i).Value = 4
            Case Else
               ws.Range("BJ" & i).Value = 5  'if not rated, then populate the “RWA – Counterparty” column as “5”
          End Select
       Case "Financial Institution", "Brokers", "Corporations"
          Select Case ws.Range("AK" & i).Value
             Case "AAA", "AA+", "AA", "AA-"
                ws.Range("BJ" & i).Value = 6
             Case Else
                ws.Range("BJ" & i).Value = 7    'if “A+”, “A”, “A-”,“A+”, “A”, “A-”, “BBB+”, “BBB”, “BBB-”, “BB+”, “BB”, “BB-”, “B+”, “B”, “B-”, “CCC+”, “CCC”, “CCC-”, “CC”, “C”, “D”, or unrated
          End Select
    End Select
    Next i

Only values for 5 and 7 are populated and not for other conditions.

 

[Fluff]

Does col AK have the exact text like "AAA" or "AA+" on it's own in the cell?

 

[rollingzep]

Does col AK have the exact text like "AAA" or "AA+" on it's own in the cell?

yes

 

[Fluff]

Ok, check that they don't have any leading/trailing spaces.