可否請各位先進幫我簡化代碼

可以請各位先進幫小弟簡化代碼,讓他執行快一點,到後面50幾行就很慢了,謝謝各位...

Private Sub jump123()
For a = 2 To 10000
  If Cells(a, 1) = "" Then Exit For
s = Cells(a, 4)

If s Like "201106*" Then
      Cells(a, 5) = "A"
End If
If s Like "201101*" Then
      Cells(a, 5) = "B"
End If
If s Like "201111*" Then
      Cells(a, 5) = "C"
End If
If s Like "201102*" Then
      Cells(a, 5) = "D"
End If
If s Like "201127*" Then
      Cells(a, 5) = "E"
End If
If s Like "1101*" Then
      Cells(a, 5) = "F"
End If
If s Like "1102*" Then
      Cells(a, 5) = "G"
End If
If s Like "1103*" Then
      Cells(a, 5) = "H"
End If
If s Like "1104*" Then
      Cells(a, 5) = "I"
End If
If s Like "1105*" Then
      Cells(a, 5) = "J"
End If
If s Like "1106*" Then
      Cells(a, 5) = "K"
End If
If s Like "1107*" Then
      Cells(a, 5) = "L"
End If
If s Like "1108*" Then
      Cells(a, 5) = "M"
End If
If s Like "1109*" Then
      Cells(a, 5) = "N"
End If
If s Like "1110*" Then
      Cells(a, 5) = "O"
End If
If s Like "1111*" Then
      Cells(a, 5) = "P"
End If
If s Like "1112*" Then
      Cells(a, 5) = "Q"
End If
If s Like "1113*" Then
      Cells(a, 5) = "R"
End If
If s Like "1114*" Then
      Cells(a, 5) = "S"
End If
If s Like "1115*" Then
      Cells(a, 5) = "T"
End If
If s Like "1116*" Then
      Cells(a, 5) = "U"
End If
If s Like "1117*" Then
      Cells(a, 5) = "V"
End If
If s Like "1118*" Then
      Cells(a, 5) = "X"
End If
If s Like "1119*" Then
      Cells(a, 5) = "Y"
End If
If s Like "1120*" Then
      Cells(a, 5) = "Z"
End If

Next a

End Sub

fyo00241

中學生 Rank: 2

帖子: 19
主題: 1
精華: 0
積分: 50
點名: 0
作業系統: windows7
軟體版本: Office2007
閱讀權限: 20
註冊時間: 2011-10-10
最後登錄: 2023-12-14

2^#

發表於 2011-12-29 01:12 | 顯示全部帖子

本帖最後由 fyo00241 於 2011-12-29 01:13 編輯

感謝2位大大的分享,我再貼上我後來的代碼,請2位幫我簡化一下,因為速度到後面就很慢了我key條碼它判斷時就會lag了,先感謝2位了!!
arr = [d2:d10000]
For i = 1 To UBound(arr)
s = arr(i, 1)
If s Like "201106*" Then
      brr(i) = "手打鐘Ⅰ"

ElseIf s Like "201101*" Then
      brr(i) = "手打鐘"

ElseIf s Like "201111*" Then
      brr(i) = "手打鐘Ⅱ"

ElseIf s Like "201102*" Then
      brr(i) = "手打鐘"

ElseIf s Like "201103*" Then
      brr(i) = "手打鐘"

ElseIf s Like "201127*" Then
      brr(i) = "T27印表機"

ElseIf s Like "1101*" Then
      brr(i) = "中文鴿鐘"

ElseIf s Like "1102*" Then
      brr(i) = "英文鴿鐘"

ElseIf s Like "1103*" Then
      brr(i) = "中文語音鴿鐘"

ElseIf s Like "1104*" Then
      brr(i) = "英文語音鴿鐘"

ElseIf s Like "1105*" Then
      brr(i) = "中文語音鴿鐘(G)"

ElseIf s Like "1106*" Then
      brr(i) = "英文語音鴿鐘(G)"

ElseIf s Like "1107*" Then
      brr(i) = "凹槽"

ElseIf s Like "1108*" Then
      brr(i) = "CI"

ElseIf s Like "1109*" Then
      brr(i) = "單格15PIN"

ElseIf s Like "1110*" Then
   brr(i) = "單格9PIN"

ElseIf s Like "1111*" Then
      brr(i) = "四合一15PIN-E"

ElseIf s Like "1112*" Then
      brr(i) = "四合一15PIN-EL"

ElseIf s Like "1113*" Then
      brr(i) = "四合一9PIN"

ElseIf s Like "1114*" Then
      brr(i) = "GPS(方形)"

ElseIf s Like "1115*" Then
      brr(i) = "525電匠"

ElseIf s Like "1116*" Then
      brr(i) = "747電匠"

ElseIf s Like "1117*" Then
      brr(i) = "T+1感應板"

ElseIf s Like "1118*" Then
      brr(i) = "傳訊機5V"

ElseIf s Like "1119*" Then
      brr(i) = "傳訊機非5V"

ElseIf s Like "1120*" Then
      brr(i) = "UID讀碼機"
End If


Next
[E2].Resize(999, 1) = Application.WorksheetFunction.Transpose(brr)

TOP

fyo00241