¥i§_½Ð¦U¦ì¥ý¶iÀ°§Ú²¤Æ¥N½X

fyo00241

¥i¥H½Ð¦U¦ì¥ý¶iÀ°¤p§Ì²¤Æ¥N½X,Åý¥L°õ¦æ§Ö¤@ÂI,¨ì«á­±50´X¦æ´N«ÜºC¤F,ÁÂÁ¦U¦ì...


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

register313

¦^´_ 1# fyo00241
ªì¾ÇªÌVBA
¨Ï¥ÎFIND§ó¦n ¦ý¸Õ¤£¥X¨Ó

1. Private Sub EE()
   
2. Application.ScreenUpdating = False
   
3. Columns("E").Clear
   
4. For a = 2 To 10000
   
5.   For b = 2 To 26
   
6.     If Cells(a, 1) = "" Then Exit For
   
7.     If Cells(a, 4) Like Cells(b, 7) & "*" Then
   
8.        Cells(a, 5) = Cells(b, 8)
   
9.     End If
   
10.   Next b
    
11. Next a
    
12. Application.ScreenUpdating = True
    
13. End Sub

½Æ»s¥N½X

¤U¸ü (64.1 KB)

2011-12-28 14:06

kimbal

¸Õ¸Õ³o¼Ë(§Ú¨S¦³´ú¸Õ)
¦ý¬O¦pªG­n¼g10000 ­Ócell,¤£·|§Ö«Ü¦h.

1. Private Sub jump123()
   
2. Application.Calculation = xlCalculationManual
   
3. Application.ScreenUpdating = False
   
4. For a = 2 To 10000
   
5.   If Cells(a, 1) = "" Then Exit For
   
6.     s = Left(Cells(a, 4), 6)
   
7.     b = False
   
8.     If s = "201106" Then
   
9.         b = True
   
10.         Cells(a, 5) = "A"
    
11.     ElseIf s = "201101" Then
    
12.         b = True
    
13.         Cells(a, 5) = "B"
    
14.     ElseIf s = "201111" Then
    
15.         b = True
    
16.         Cells(a, 5) = "C"
    
17.     ElseIf s = "201102" Then
    
18.         b = True
    
19.         Cells(a, 5) = "D"
    
20.     ElseIf s = "201127" Then
    
21.         b = True
    
22.         Cells(a, 5) = "E"
    
23.     End If
    
24.     If Not (b) Then
    
25.         k1 = Left(s, 2)
    
26.         If k1 = "11" Then
    
27.             k2 = Mid(s, 3, 2)
    
28.             If k2 >= "01" And k2 <= "17" Then
    
29.                 Cells(a, 5) = Chr(69 + Val(k2))
    
30.             ElseIf k2 >= "18" And k2 <= "20" Then
    
31.                 Cells(a, 5) = Chr(70 + Val(k2))
    
32.             End If
    
33.         End If
    
34.     End If
    
35.    Next a
    
36. Application.Calculation = xlCalculationAutomatic
    
37. Application.ScreenUpdating = True
    
38. End Sub

½Æ»s¥N½X

fyo00241

·PÁÂ2¦ì¤j¤jªº¤À¨É,§Ú¦A¶K¤W§Ú«á¨Óªº¥N½X,½Ð2¦ìÀ°§Ú²¤Æ¤@¤U,¦]¬°³t«×¨ì«á­±´N«ÜºC¤F§Úkey±ø½X¥¦§PÂ_®É´N·|lag¤F,¥ý·PÁÂ2¦ì¤F!!
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¦Lªí¾÷"

ElseIf s Like "1101*" Then
        brr(i) = "¤¤¤åÂFÄÁ"

ElseIf s Like "1102*" Then
        brr(i) = "­^¤åÂFÄÁ"

ElseIf s Like "1103*" Then
        brr(i) = "¤¤¤å»y­µÂFÄÁ"

ElseIf s Like "1104*" Then
        brr(i) = "­^¤å»y­µÂFÄÁ"

ElseIf s Like "1105*" Then
        brr(i) = "¤¤¤å»y­µÂFÄÁ(G)"

ElseIf s Like "1106*" Then
        brr(i) = "­^¤å»y­µÂFÄÁ(G)"

ElseIf s Like "1107*" Then
        brr(i) = "¥W¼Ñ"

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) = "¥|¦X¤@15PIN-E"

ElseIf s Like "1112*" Then
        brr(i) = "¥|¦X¤@15PIN-EL"

ElseIf s Like "1113*" Then
        brr(i) = "¥|¦X¤@9PIN"

ElseIf s Like "1114*" Then
        brr(i) = "GPS(¤è§Î)"

ElseIf s Like "1115*" Then
        brr(i) = "525¹q¦K"

ElseIf s Like "1116*" Then
        brr(i) = "747¹q¦K"

ElseIf s Like "1117*" Then
        brr(i) = "T+1·PÀ³ªO"

ElseIf s Like "1118*" Then
        brr(i) = "¶Ç°T¾÷5V"

ElseIf s Like "1119*" Then
        brr(i) = "¶Ç°T¾÷«D5V"

ElseIf s Like "1120*" Then
        brr(i) = "UIDŪ½X¾÷"
End If
           
   
Next
[E2].Resize(999, 1) = Application.WorksheetFunction.Transpose(brr)

register313

¦^´_ 4# fyo00241
1. key±ø½X key¦b¦ó³B? §@¦ó§PÂ_?
2.µ{¦¡¥\¯à¬°¦ó?
3.«ÜºC¬O¤j¬ù´X¬í? ¨Ì¹ê»Ú»Ý¨D¤j¬ù­n´X¬í§¹¦¨?
4.¦ó¤£ªþ¤WÀÉ®×?

fyo00241

¤é°O±b°O¿ý.zip (832.98 KB)

password:3543
¤U¸ü¦¸¼Æ: 14

2011-12-29 08:51

¦^´_ 5# register313
³Â½Ð¤F¬OUserForm1³o­Ó¦a¤è,¤]½Ð¤j¤jÀ°§Ú¬Ý¤@¤U¨º¨Ç¦a¤è¥i¥H¦A²¤Æ,¶¶¤@ÂIªºÁÂÁ¤F!!

register313

¦^´_ 6# fyo00241
   ¨Ì§Ú¬Ý§A¬O¥Îªí³æ¤@¦¸¿é¤J1­Ó§Ç¸¹(DÄæ),µM«á¬d¥X1­Ó«~¦W(EÄæ)   ===>   »Ý1t®É¶¡  
   ¦Ó¤£¬O¤@¦¸¤w¦³10000­Ó§Ç¸¹(DÄæ),),µM«á¤@¦¸¬d¥X10000­Ó«~¦W(EÄæ)   ===>   »Ý10000t®É¶¡(·íµM·|ºC)
  
   ¨Ì¤§«eµ{¦¡¤§¼gªk
  ¿é¤J²Ä1­Ó§Ç¸¹(¬d¸ß1­Ó)  ===>   »Ý1t®É¶¡
  ¿é¤J²Ä2­Ó§Ç¸¹(¬d¸ß2­Ó)  ===>   »Ý2t®É¶¡
  ¿é¤J²Ä1000­Ó§Ç¸¹(¬d¸ß1000­Ó)  ===>   »Ý1000t®É¶¡
  ©Ò¥H²Ö¿n·U¦h§Ç¸¹ ¨ì®É­Ô°õ¦æ³t«×´N·UºC

  ¥ý¬Ý¬ÝªþÀɽd¨Ò¬O¤£¬O§A­nªº
  ¦bDÄæ¿é¤J§Ç¸¹,°¨¤W¬d¥X«~¦W
  ¥u·|¬d¦³DÄæÅܰʤ§§Ç¸¹,¦Ó¤£¬O¨C¦¸³£¥þ³¡­«¬d

1. Private Sub worksheet_change(ByVal target As Range)
   
2. 
   
3. If target.Column = 4 Then
   
4.   a = target.Row
   
5.   For b = 2 To 27
   
6.     If Cells(a, 4) Like Cells(b, 7) & "*" Then
   
7.        Cells(a, 5) = Cells(b, 8)
   
8.     End If
   
9.   Next b
   
10. End If
    
11. 
    
12. End Sub

½Æ»s¥N½X

·s¼WMicrosoft Excel ¤u§@ªí.rar (6.59 KB)

¤U¸ü¦¸¼Æ: 10

2011-12-29 12:59

GBKEE

¦^´_ 6# fyo00241
§AªþÀÉ ªºVBA ¤WÂê¬Ý¤£¨£

register313

¦^´_ 6# fyo00241

½Ð°Ý­ì¨Óªºµ{¦¡¬O§_¯à¥¿±`°õ¦æ ¦n¦h¦a¤è¦³°ÝÃD

¥u­×¥¿¥»©«©Ò´£¤§°õ¦æ³t«×
¸Õ¸Õ¬Ý

¤é°O±b°O¿ý.zip (749.39 KB)

¤U¸ü¦¸¼Æ: 9

2011-12-29 18:52

fyo00241

¦^´_ 8# GBKEE


³ø§iG¤j:3543