Nest not working in CASE

[rollingzep]

Hi,

In a recent thread, I had requested a solution for the below. By oversight, I had marked that reply as Solved.

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”

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

I am not getting values populated in col BJ.

 

[RoryA]

You just need to add in the additional Case statements for the column AK values you didn't have specified before. It appears that your last current Case line could just be replaced with Case Else since it appears to be that all other situations are a 7.

 

[Fluff]

Duplicate to: Using a CASE than If

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.