Setting up a Case Select structure using a reference table

[A Guy Named Robby]

I have lots of Case Select scenarios set up. I'm going to have to add a lot more. A greatly condensed sample is below. How can I recreate this scenario using a reference table?

Beginning on row 84 to the end of the data, it's comparing what's in column O (college names) to hundreds/thousands of cases. If it finds a match, it enters a specific digit in column N.

Sub NumberFillIn()
Dim LastRow As Long
Dim i As Long
LastRow = Range("O" & Rows.Count).End(xlUp).Row
For i = 84 To LastRow

Select Case Range("O" & i)

Case "New York University", "NYU", "New York Univ.", "New York Univ"
Range("N" & i) = "1"

Case "Abraham Lincoln University"
Range("N" & i) = "2"

Case "Mississippi College"
Range("N" & i) = "3"

Case "Piedmont College"
Range("N" & i) = "4"

Case "Minnesota State University"
    Select Case Range("P" & i)
    Case "Bemidji"
    Range("N" & i) = "5"
    Case "Moorhead"
    Range("N" & i) = "6"
    Case "St Cloud", "St. Cloud"
    Range("N" & i) = "7"
    Case "Mankato"
    Range("N" & i) = "8"
    End Select

End Sub

 

[A Guy Named Robby]

Sorry again! With this code, it looks like the matches are case sensitive. For example, "new york university" won't be matched to "New York University." Help?

 

[Eric W]

Fuzzy matching is always tricky. There are lots of ways to do it, but they tend to get more and more complicated, and end up less and less effective. There's usually a break-even point somewhere where you have to accept you're just going to get some mismatches.

For just checking case, it's easy enough to tell the InStr function to do a text compare, which doesn't care about case. Change:

If InStr(MyOptions(j, 2), Match1) > 0 And InStr(MyOptions(j, 3), Match2) > 0 Then

to

If InStr(1, MyOptions(j, 2), Match1, vbTextCompare) > 0 And InStr(1, MyOptions(j, 3), Match2, vbTextCompare) > 0 Then