問題描述
選擇不運行每個案例的案例 (Select Case Not Running Every Case)
我在一個 Excel 文檔中有一個選擇案例功能，它不是每個案例都運行。具體來說，它正在跳過 12044‑12053。我不明白為什麼它沒有對所有數據一視同仁。
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
參考解法
方法 1:
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.
(by autumntiger、Netloh)