Select Case Not Running Every Case

Question

I have a select case function inside an excel document that is not running every case. Specifically, it is skipping 12044-12053. I do not understand why it isn't treating all of the data the same.

Sub Main()

    Dim c As Range

    For Each c In Range("E2:E1000")

        Select Case UCase(c)

            Case "11001"
                c = "C. Matern"
            Case "11002"
                c = "C. Matern"
            Case "11004"
                c = "M. Jones"
            Case "11005"
                c = "J. Kimble"
            Case "11006"
                c = "A. McPike"
            Case "11007"
                c = "M. Jones"
            Case "11008"
                c = "M. Jones"
            Case "11009"
                c = "C. Matern"
            Case "11011"
                c = "J. Kimble"
            Case "11014"
                c = "J. Kimble"
            Case "11015"
                c = "Unknown"
            Case "11019"
                c = "J. Kimble"
            Case "11020"
                c = "Unknown"
            Case "11025"
                c = "M. Jones"
            Case "11026"
                c = "A. McPike"
            Case "11027"
                c = "J. Kimble"
            Case "11031"
                c = "Unknown"
            Case "11038"
                c = "D. Campbell"
            Case "11039"
                c = "J. Kimble"
            Case "11041"
                c = "J. Kimble"
            Case "11043"
                c = "D. Campbell"
            Case "11046"
                c = "C. Matern"
            Case "11046A"
                c = "C. Matern"
            Case "11053"
                c = "D. Campbell"
            Case "11059"
                c = "D. Campbell"
            Case "11059A"
                c = "D. Campbell"
            Case "11060"
                c = "J. Kimble"
            Case "11061"
                c = "C. Matern"
            Case "11063"
                c = "D. Campbell"
            Case "11068"
                c = "C. Matern"
            Case "11070"
                c = "C. Matern"
            Case "11073"
                c = "C. Matern"
            Case "11074"
                c = "A. McPike"
            Case "11077"
                c = "A. McPike"
            Case "11078"
                c = "D. Campbell"
            Case "11080"
                c = "D. Campbell"
            Case "11082"
                c = "J. Kimble"
            Case "11083"
                c = "D. Campbell"
            Case "11085"
                c = "C. Matern"
            Case "11086"
                c = "C. Matern"
            Case "11088"
                c = "A. McPike"
            Case "11089"
                c = "M. Jones"
            Case "11090"
                c = "D. Campbell"
            Case "11093"
                c = "J. Kimble"
            Case "11094"
                c = "D. Campbell"
            Case "11095"
                c = "C. Matern"
            Case "11098"
                c = "C. Matern"
            Case "11099"
                c = "A. McPike"
            Case "11100"
                c = "D. Campbell"
            Case "11101"
                c = "J. Kimble"
            Case "11104"
                c = "D. Campbell"
            Case "11105"
                c = "M. Jones"
            Case "11106"
                c = "M. Jones"
            Case "11106A"
                c = "J. Kimble"
            Case "11107"
                c = "C. Matern"
            Case "11108"
                c = "A. McPike"
            Case "11109"
                c = "D. Campbell"
            Case "11111"
                c = "M. Jones"
            Case "11113"
                c = "M. Jones"
            Case "11115"
                c = "C. Matern"
            Case "11124"
                c = "M. Jones"
            Case "11126"
                c = "D. Campbell"
            Case "12001"
                c = "A. McPike"
            Case "12002"
                c = "M. Jones"
            Case "12004"
                c = "D. Campbell"
            Case "12005"
                c = "A. McPike"
            Case "12006"
                c = "M. Jones"
            Case "12007"
                c = "A. McPike"
            Case "12008"
                c = "M. Jones"
            Case "12009"
                c = "A. McPike"
            Case "12011"
                c = "A. McPike"
            Case "12012"
                c = "D. Campbell"
            Case "12013"
                c = "D. Campbell"
            Case "12014"
                c = "J. Kimble"
            Case "12014A"
                c = "A. McPike"
            Case "12014P"
                c = "A. McPike"
            Case "12014S"
                c = "A. McPike"
            Case "12014W"
                c = "A. McPike"
            Case "12017"
                c = "Unknown"
            Case "12018"
                c = "A. McPike"
            Case "12019"
                c = "D. Campbell"
            Case "12020"
                c = "A. McPike"
            Case "12021"
                c = "A. McPike"
            Case "12024"
                c = "D. Campbell"
            Case "12025"
                c = "D. Campbell"
            Case "12026"
                c = "A. McPike"
            Case "12027"
                c = "C. Matern"
            Case "12028"
                c = "M. Jones"
            Case "12029"
                c = "A. McPike"
            Case "12029A"
                c = "A. McPike"
            Case "12030B"
                c = "A. McPike"
            Case "12031"
                c = "M. Jones"
            Case "12033"
                c = "J. Kimble"
            Case "12037"
                c = "D. Campbell"
            Case "12038"
                c = "A. McPike"
            Case "12038A"
                c = "A. McPike"
            Case "12038B"
                c = "Unknown"
            Case "12041"
                c = "A. McPike"
            Case "12042"
                c = "Unnknown"
            Case "12044"
                c = "A. McPike"
            Case "12045"
                c = "A. McPike"
            Case "12046"
                c = "A. McPike"
            Case "12047"
                c = "M. Jones"
            Case "12049"
                c = "Unknown"
            Case "12052"
                c = "J. Kimble"
            Case "12052a"
                c = "J. Kimble"
            Case "12052b"
                c = "J. Kimble"
            Case "12053"
                c = "C. Matern"
            Case "12055"
                c = "A. McPike"
            Case "12057"
                c = "D. Campbell"
            Case "12058"
                c = "A. McPike"
            Case "12060"
                c = "D. Campbell"
            Case "12062"
                c = "C. Matern"
            Case "12064"
                c = "D. Campbell"
            Case "12065"
                c = "Unknown"
            Case "12067"
                c = "McPike"
            Case "12068"
                c = "D. Campbell"
            Case "12069"
                c = "C. Matern"
            Case "12070"
                c = "C. Matern"
            Case "12075"
                c = "A. McPike"
            Case "12076"
                c = "A. McPike"
            Case "12077"
                c = "A. McPike"
            Case "12078"
                c = "C. Matern"
            Case "12080"
                c = "A. McPike"
            Case "12081"
                c = "M. Jones"
            Case "12084"
                c = "D. Campbell"
            Case "12085"
                c = "A. McPike"
            Case "12088"
                c = "A. McPike"
            Case "12089"
                c = "D. Campbell"
            Case "12090"
                c = "D. Campbell"
            Case "12091"
                c = "A. McPike"
            Case "12092"
                c = "J. Kimble"
            Case "12093"
                c = "A. McPike"
            Case "13003"
                c = "M. Jones"
            Case "22001"
                c = "J. Kimble"
            Case "2201A"
                c = "A. McPike"
            Case "22001B"
                c = "J. Kimble"
            Case "22001E"
                c = "Unknown"
            Case "22001F"
                c = "D. Campbell"
            Case "22001G"
                c = "D. Campbell"
            Case "22002"
                c = "J. Kimble"
            Case "22817"
                c = "C. Matern"
            Case "31001"
                c = "C. Matern"
            Case "31006"
                c = "C. Matern"
            Case "31007"
                c = "D. Campbell"
            Case "31008"
                c = "C. Matern"
            Case "32001"
                c = "A. McPike"
            Case "320022"
                c = "Unknown"
            Case "32008"
                c = "A. McPike"
            Case "32010"
                c = "A. McPike"
            Case "32011"
                c = "J. Kimble"
            Case "32013"
                c = "A. McPike"
            Case "32014"
                c = "Unknown"
            Case "32019"
                c = "J. Kimble"
            Case "32029"
                c = "C. Matern"
            Case "32030"
                c = "Unknown"
            Case "32031"
                c = "J. Kimble"
            Case "32033"
                c = "Unknown"
            Case "32044"
                c = "A. McPike"
            Case "32048"
                c = "A. McPike"
            Case "32049"
                c = "Unknown"
            Case "32060"
                c = "C. Matern"
            Case "32061"
                c = "Unknown"
            Case "32062"
                c = "C. Matern"
            Case "32067"
                c = "D. Campbell"
            Case "32068"
                c = "M. Jones"
            Case "32072"
                c = "A. McPike"
            Case "32073"
                c = "A. McPike"
            Case "32077"
                c = "A. McPike"
            Case "32081"
                c = "C. Matern"
            Case "32084"
                c = "A. McPike"
            Case "32086"
                c = "A. McPike"
            Case "32089"
                c = "M. Jones"
            Case "32090"
                c = "A. McPike"
            Case "32097"
                c = "C. Matern"
            Case "32107"
                c = "C. Matern"
            Case "32111"
                c = "D. Campbell"
            Case "32116"
                c = "D. Campbell"
            Case "32133"
                c = "A. McPike"
            Case "32141"
                c = "A. McPike"
            Case "32145"
                c = "A. McPike"
            Case "32146"
                c = "A. McPike"
            Case "32149"
                c = "M. Jones"
            Case "32169"
                c = "D. Campbell"
            Case "32170"
                c = "A. McPike"
            Case "32173"
                c = "A. McPike"
            Case "32174"
                c = "A. McPike"
            Case "32176"
                c = "A. McPike"
            Case "32177"
                c = "A. McPike"
            Case "99999"
                c = "Misc."
            Case "AAAAA2"
                c = "M.Jones"
            Case "B"
                c = "A. McPike"
            Case "BBBBB"
                c = "C. Matern"
            Case "C"
                c = "A. McPike"
            Case "D"
                c = "A. McPike"
            Case "Denied"
                c = "Denied"
            Case "E"
                c = "M. Jones"
            Case "F"
                c = "A. McPike"
            Case "G"
                c = "A. McPike"
            Case "JPN"
                c = "T. Okayasu"
            Case "MU001"
                c = "R. Martinez"
            Case "MU002"
                c = "M. Humes"
            Case "MU003"
                c = "R. Martinez"
            Case "MU004"
                c = "R. Martinez"
            Case "MU005"
                c = "M. Humes"
            Case "MU006"
                c = "R. Martinez"
            Case "MU007"
                c = "R. Martinez"
            Case "MU008"
                c = "M. Humes"
            Case "MU009"
                c = "M. Humes"
            Case "MU010"
                c = "M. Humes"
            Case "MU011"
                c = "R. Martinez"
            Case "MU012"
                c = "M. Humes"
            Case "MU013"
                c = "M. Humes"
            Case "MU014"
                c = "M. Humes"
            Case "MU015"
                c = "R. Martinez"
            Case "MU016"
                c = "R. Martinez"
            Case "MU017"
                c = "M. Humes"
            Case "MU018"
                c = "R. Martinez"
            Case "MU020"
                c = "M. Humes"
            Case "MU021"
                c = "M. Humes"
            Case "MU022"
                c = "R. Martinez"
            Case "MU023"
                c = "R. Martinez"
            Case "MU024"
                c = "M. Humes"
            Case "MU025"
                c = "R. Martinez"
            Case "MU026"
                c = "R. Martinez"
            Case "MU027"
                c = "R. Martinez"
            Case "MU028"
                c = "R. Martinez"
            Case "MU029"
                c = "R. Martinez"
            Case "MU031"
                c = "R. Martinez"
            Case "MU032"
                c = "JPD"
            Case "MU999"
                c = "R. Martinez"

      End Select
    Next
End Sub

I don't see a problem in your code. Can you manually find some cells which contain 12044-12053 and compare formatting with other ones? — , Jan 13 '14 at 14:45
@mehow Please see my comment below. It might shed some light.? — autumntiger, Jan 13 '14 at 14:49

score 4 · Accepted Answer · answered Jan 13 '14 at 14:27

4

It could be because some of your data fields are string values with a leading or trailing space. Try replacing the fourth line in your code with:

Select Case Trim(UCase(c))

This will remove any leading or trailing spaces of your input variables.

answered Jan 13 '14 at 14:27

Netloh

4,338
4
25
38

I tried changing the fourth line of the code, as you suggested, but it still acts the same way. It seems very peculiar. I just tried entering some of the values that were skipped. It seems that the macro will change all of them, except for 12052a and 12052b. Does that put up a flag? – autumntiger Jan 13 '14 at 14:42
It shouldn't be a problem with your code then. As just suggested in another comment, you should probably be sure to manually check that it is not a formatting issue. – Netloh Jan 13 '14 at 14:49
I went back and checked; I can't find any formatting issues. There is a 12052; is it possible that the "a" and "b" versions of this ID code are throwing the macro off? – autumntiger Jan 13 '14 at 14:55
1

Yes. That is definitely the issue. Perhaps you got so few observations that have "a" and "b" versions that you could just create more cases in your code to account for these. – Netloh Jan 13 '14 at 15:02
I changed the lower case "a" and "b" to upper case in the code. Despite how it appears in the formatting file, it changes to the desired results. Thank you very much for your help! – autumntiger Jan 13 '14 at 15:05
It would be my pleasure. – autumntiger Jan 13 '14 at 17:32

Select Case Not Running Every Case

1 Answers1