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?

 

[rollingzep]

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

I added the Trim and that did the trick. Thanks for the tip!

 

[Fluff]

Glad to help & thanks for the feedback.