looping within a select case

R

Reeza

New Member
Joined
Oct 22, 2015
Messages
4
I have been given the data below which has credit scores as well as ratings. the whole point is to write a code that will assign a rating to a credit score. Im not sure if im making sense but its much easier to understand when looking at the data. I have been told that you could use select case but im not entirely sure how. Would select case be the best method? Im using Excel 2010.
[TABLE="width: 500"]
<tbody>[TR]
[TD]

<colgroup><col width="87"></colgroup><tbody>
[TD="width: 87"]Credit Score
[/TD]
[TD="width: 87"] Rating
[/TD]

</tbody>
[/TD]
[TD]

<colgroup><col width="64"></colgroup><tbody>
[TD="width: 64"]Scale[/TD]

</tbody>
[/TD]
[TD]

<colgroup><col width="64"></colgroup><tbody>
[TD="width: 64"]Rating[/TD]

</tbody>
[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]

<colgroup><col width="64"></colgroup><tbody>
[TD="class: xl65, width: 64"]20 to 16[/TD]

</tbody>
[/TD]
[TD]AAA
[/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD]

<colgroup><col width="64"></colgroup><tbody>
[TD="width: 64"]15 to 11[/TD]

</tbody>
[/TD]
[TD]AA
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]

<colgroup><col width="64"></colgroup><tbody>
[TD="width: 64"]10 to 7[/TD]

</tbody>
[/TD]
[TD]A
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]

<colgroup><col width="64"></colgroup><tbody>
[TD="width: 64"]7 to 4[/TD]

</tbody>
[/TD]
[TD]BBB
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]

<colgroup><col width="64"></colgroup><tbody>
[TD="width: 64"]3 to 1[/TD]

</tbody>
[/TD]
[TD]BB
[/TD]
[/TR]
[TR]
[TD]20
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
Welcome to Mr Excel

Maybe something like this

Observe:
1. to make the things easier i split the boundaries (Scale) in two columns
2. i adjusted the upper boundary of BBB rating to 6 (i think 7 is not correct because it is the lower boundary of A rating)


[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]
A​
[/TD]
[TD]
B​
[/TD]
[TD]
C​
[/TD]
[TD]
D​
[/TD]
[TD]
E
​
[/TD]
[/TR]
[TR]
[TD]
1​
[/TD]
[TD]
Credit Score​
[/TD]
[TD]
Rating​
[/TD]
[TD]
Higher​
[/TD]
[TD]
Lower​
[/TD]
[TD]
Rating​
[/TD]
[/TR]
[TR]
[TD]
2​
[/TD]
[TD]
14​
[/TD]
[TD]
AA​
[/TD]
[TD]
20​
[/TD]
[TD]
16​
[/TD]
[TD]
AAA​
[/TD]
[/TR]
[TR]
[TD]
3​
[/TD]
[TD]
15​
[/TD]
[TD]
AA​
[/TD]
[TD]
15​
[/TD]
[TD]
11​
[/TD]
[TD]
AA​
[/TD]
[/TR]
[TR]
[TD]
4​
[/TD]
[TD]
1​
[/TD]
[TD]
BB​
[/TD]
[TD]
10​
[/TD]
[TD]
7​
[/TD]
[TD]
A​
[/TD]
[/TR]
[TR]
[TD]
5​
[/TD]
[TD]
5​
[/TD]
[TD]
BBB​
[/TD]
[TD]
6​
[/TD]
[TD]
4​
[/TD]
[TD]
BBB​
[/TD]
[/TR]
[TR]
[TD]
6​
[/TD]
[TD]
10​
[/TD]
[TD]
A​
[/TD]
[TD]
3​
[/TD]
[TD]
1​
[/TD]
[TD]
BB​
[/TD]
[/TR]
[TR]
[TD]
7​
[/TD]
[TD]
20​
[/TD]
[TD]
AAA​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
8​
[/TD]
[TD]
1​
[/TD]
[TD]
BB​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
9​
[/TD]
[TD]
9​
[/TD]
[TD]
A​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
10​
[/TD]
[TD]
1​
[/TD]
[TD]
BB​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
11​
[/TD]
[TD]
14​
[/TD]
[TD]
AA​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
12​
[/TD]
[TD]
10​
[/TD]
[TD]
A​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
13​
[/TD]
[TD]
9​
[/TD]
[TD]
A​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
14​
[/TD]
[TD]
4​
[/TD]
[TD]
BBB​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
15​
[/TD]
[TD]
14​
[/TD]
[TD]
AA​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
16​
[/TD]
[TD]
10​
[/TD]
[TD]
A​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
17​
[/TD]
[TD]
19​
[/TD]
[TD]
AAA​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
18​
[/TD]
[TD]
12​
[/TD]
[TD]
AA​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
19​
[/TD]
[TD]
18​
[/TD]
[TD]
AAA​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
20​
[/TD]
[TD]
2​
[/TD]
[TD]
BB​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
21​
[/TD]
[TD]
4​
[/TD]
[TD]
BBB​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
22​
[/TD]
[TD]
3​
[/TD]
[TD]
BB​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
23​
[/TD]
[TD]
20​
[/TD]
[TD]
AAA​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
24​
[/TD]
[TD]
5​
[/TD]
[TD]
BBB​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
25​
[/TD]
[TD]
18​
[/TD]
[TD]
AAA​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
26​
[/TD]
[TD]
2​
[/TD]
[TD]
BB​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Formula in B2 copied down
=INDEX($E$2:$E$6,MATCH(A2,$C$2:$C$6,-1))

Hope this helps

M.
 
Last edited:
Upvote 0
Thank you so much for the response, i really appreciate it :) sorry about that, I should have been more specific. I need to use VBA to write a code. I have the following code but im not fully understanding it, so I was kinda hoping to find an easier way to do it or an explanation of the code.

Sub UsingSelectCase()
Dim iRow As Integer

iRow = 3
iCredSc = 0
Do Until IsEmpty(Cells(iRow, 2))
iCredSc = Cells(iRow, 2)
SelectCase
Cells(iRow, 3) = sRating
iRow = iRow + 1
Loop


End Sub
 
Upvote 0
Sorry, I don't understand your code. So I cannot help you to fix it.

The formula suggested in the message # 2 is an efficient solution.
If you really need a solution with VBA, you can:
1. turn on the macro recorder
2. enter the formula in the first cell
3. copy the formula to the relevant cells
4. turn off the macro recorder
5. review and adapt the generated code.

M.
 
Upvote 0
Hi Reeza, as an alternative you can try the following macro in a COPY of your workbook. This code is inserted into a standard module and applied to a button to be run.

Rich (BB code): 
Sub Apply_Credit_Rating()
' Defines variables
Dim Cell, cRange As Range
' Defines range to check
Set cRange = Range("A2:A26")
' For each cell in range
    For Each Cell In cRange
' If cell value is equal to or between 16 and 20 then update the next adjacent cell to the right with AAA
        If Cell.Value <= 20 And Cell.Value >= 16 Then
            Cell.Offset(0, 1).Value = "AAA"
        End If
' If cell value is equal to or between 11 and 15 then update the next adjacent cell to the right with AA
        If Cell.Value <= 15 And Cell.Value >= 11 Then
            Cell.Offset(0, 1).Value = "AA"
        End If
' If cell value is equal to or between 7 and 10 then update the next adjacent cell to the right with A
        If Cell.Value <= 10 And Cell.Value >= 7 Then
            Cell.Offset(0, 1).Value = "A"
        End If
' If cell value is equal to or between 4 and 6 then update the next adjacent cell to the right with BBB
        If Cell.Value <= 6 And Cell.Value >= 4 Then
            Cell.Offset(0, 1).Value = "BBB"
        End If
' If cell value is equal to or between 7 and 3 then update the next adjacent cell to the right with BB
        If Cell.Value <= 3 And Cell.Value >= 1 Then
            Cell.Offset(0, 1).Value = "BB"
        End If
' Move to next cell in range
    Next Cell
End Sub

You can change the bold red A2:A26 to suit where you data is and how far down the page it goes (as a word of warning if you change it to A:A it will take a while to run and may look like Excel has frozen). This code also assumes that the rating column is the next column to the right of the credit score column.
 
Last edited:
Upvote 0
Hmmm, thinking about it (and I don't know why I didn't do this the first time around), this would be much better for you if you didn't have to specifically define the range, especially if the list is going to get longer over time. This new updated version works out the last row based on where the values in column A end, then applies the rule from A2 down to the last row.

Rich (BB code): 
Sub Apply_Credit_Rating()
' Defines variables
Dim Cell As Range
' Defines last row by the last value in column A
    LastRow = ActiveSheet.Columns("A").Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).Row
' Defines range to check as A2 to the last row with a value in column A
Set Range = Range("A2:A" & LastRow)
' For each cell in range
    For Each Cell In Range
' If cell value is equal to or between 16 and 20 then update the next adjacent cell to the right with AAA
        If Cell.Value <= 20 And Cell.Value >= 16 Then
            Cell.Offset(0, 1).Value = "AAA"
        End If
' If cell value is equal to or between 11 and 15 then update the next adjacent cell to the right with AA
        If Cell.Value <= 15 And Cell.Value >= 11 Then
            Cell.Offset(0, 1).Value = "AA"
        End If
' If cell value is equal to or between 7 and 10 then update the next adjacent cell to the right with A
        If Cell.Value <= 10 And Cell.Value >= 7 Then
            Cell.Offset(0, 1).Value = "A"
        End If
' If cell value is equal to or between 4 and 6 then update the next adjacent cell to the right with BBB
        If Cell.Value <= 6 And Cell.Value >= 4 Then
            Cell.Offset(0, 1).Value = "BBB"
        End If
' If cell value is equal to or between 7 and 3 then update the next adjacent cell to the right with BB
        If Cell.Value <= 3 And Cell.Value >= 1 Then
            Cell.Offset(0, 1).Value = "BB"
        End If
' Move to next cell in range
    Next Cell
End Sub
 
Upvote 0
Thank you so so much. I used the first explanation you gave, and it worked perfectly. I also some how find it alot easier to understand. The second one didn't run as it brought up an error saying that LastRow wasn't defined. Do you have any idea how the code I put up works? Because I have no clue what they are doing.
 
Upvote 0
Reeza said:
Thank you so so much. I used the first explanation you gave, and it worked perfectly. I also some how find it alot easier to understand.
Click to expand...
Thanks for the feedback, and you're welcome. I certainly find code that has explanations added really help me remember what I am doing and help the people I share them with even more.

Reeza said:
The second one didn't run as it brought up an error saying that LastRow wasn't defined
Click to expand...
Hmm, curious. I think I may have actually cross copy / pasted and botched up the second version when i moved it from Excel to the forums. A tested and working version using the LastRow can be found below (I still didn't "define" LastRow with Dim as it doesn't seem to need it)
Rich (BB code): 
Sub Apply_Credit_Rating()
' Defines variables
Dim Cell, cRange As Range
' Defines last row by the last value in column A
    LastRow = ActiveSheet.Columns("A").Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).Row
' Defines range to check as A2 to the last row with a value in column A
Set cRange = Range("A2:A" & LastRow)
' For each cell in range
    For Each Cell In cRange
' If cell value is equal to or between 16 and 20 then update the next adjacent cell to the right with AAA
        If Cell.Value <= 20 And Cell.Value >= 16 Then
            Cell.Offset(0, 1).Value = "AAA"
        End If
' If cell value is equal to or between 11 and 15 then update the next adjacent cell to the right with AA
        If Cell.Value <= 15 And Cell.Value >= 11 Then
            Cell.Offset(0, 1).Value = "AA"
        End If
' If cell value is equal to or between 7 and 10 then update the next adjacent cell to the right with A
        If Cell.Value <= 10 And Cell.Value >= 7 Then
            Cell.Offset(0, 1).Value = "A"
        End If
' If cell value is equal to or between 4 and 6 then update the next adjacent cell to the right with BBB
        If Cell.Value <= 6 And Cell.Value >= 4 Then
            Cell.Offset(0, 1).Value = "BBB"
        End If
' If cell value is equal to or between 7 and 3 then update the next adjacent cell to the right with BB
        If Cell.Value <= 3 And Cell.Value >= 1 Then
            Cell.Offset(0, 1).Value = "BB"
        End If
' Move to next cell in range
    Next Cell
End Sub

Reeza said:
Do you have any idea how the code I put up works? Because I have no clue what they are doing.
Click to expand...
To be honest I am far from an expert. I have had a look at your original code and am not too sure what it is supposed to be doing either. Certainly the whole Case Select part suggests there should be more code where each case is defined but that does not appear to have been included.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
VBA to Categorise based on columns
Replies
3
Views
146
Peter_SSs
Peter_SSs
A
  • Locked
  • Question Question
Multiply an array based on date value entered.
Replies
1
Views
40
Fluff
Fluff
A
  • Question Question
Multiply an array based on date value entered.
Replies
8
Views
119
Fluff
Fluff
JenniferMurphy
  • Question Question
Can someone help me understand Bayesian averages?
Replies
1
Views
132
FormulasOnly
F
P
  • Solved
Select Case in VBA
Replies
3
Views
675
rollis13
rollis13

Forum statistics

Threads
1,223,909
Messages
6,175,315
Members
452,634
Latest member
cpostell

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top