Looping using Select Case Statement

[tdewale]

I have a range of scores say in Column A and they relate to one of the corresponding codes in the REFERENCE TABLE.
So a score of 621 would be between 620 & 639 I need to fill a column with the corresponding code# based on the table below.
My code will fill the desired column but it doesn't loop through. It fills the column with the first select CASE statement so the whole column fills in with "FICO1"
Desired Result should look like this
Column A Column B
621 FICO7
700 FICO3
681 FICO4


REFERENCE TABLE
Low High Code#
740 900 FICO1
720 739 FICO2
700 719 FICO3
680 699 FICO4
660 679 FICO5
640 659 FICO6
620 639 FICO7



Sub CategoryChanger()

Dim rng As Range
Dim r As Range
Dim result As String

Sheet1.Activate
'## Defines a range to represent the cells over which you would like to iterate:


Set rng = Range("Loan_Credit_Score__FICO")
'## Iterate each cell in the Range defined "rng"
For Each r In rng.Cells
Select Case r.Value

Case (Value >= 740 And Value <= 900)
result = "FICO1"
Case (Value >= 720 And Value <= 739)
result = "FICO2"
Case (Value >= 700 And Value <= 719)
result = "FICO3"
Case (Value >= 680 And Value <= 699)
result = "FICO4"
Case (Value >= 660 And Value <= 679)
result = "FICO5"
Case (Value >= 640 And Value <= 659)
result = "FICO6"
End Select

'## Put result in the cell 25 columns to right
r.Offset(0, 25).Value = result
Next
End Sub

[Norie]

Try this.

Please Login or Register  to view this content.

[Zer0Cool]

You could do this with VLOOKUP as well, have you considered that instead?

Using a reference table that and the approximate match argument would allow you to pull the FICO# you want. Ill edit to add an example shortly.

Ref table:
0 | FICO8
620 | FICO7
640 | FICO6
660 | FICO5
680 | FICO4
700 | FICO3
720 | FICO2
740 | FICO1

then as a formula for your codes column (B):

Please Login or Register  to view this content.

Basically TRUE allows it to find the closest value not over the lookup value and use that line to return from. IE: 619 = 0 = FICO8, 620 = 620 = FICO7, 621 = 620 = FICO7, etc.

Not sure if you have a macro doing much more and this is just part of it, but VLOOKUp would be a much simpler way to do only this.

[CK76]

If using Select Case. I'd do it like below.

Since Select Case will exit at first match... going from <620 ... <=900 will work as well with out limiting to whole numbers.

Please Login or Register  to view this content.

[tdewale]

Norie - that worked like a charm.
Awesome .. this was really bugging me for two days. You are the man!!!