ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

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

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

¥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 ©ó 2011-12-28 14:09 ½s¿è

¦^´_ 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

TOP

¸Õ¸Õ³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
À´±oµo°Ý,µª®×´N·|¦b¨ä¤¤

¤µ¤éの¤@¬íは  ©ú¤éにない
http://kimbalko-chi.blogspot.com
http://kimbalko.blogspot.com

TOP

¥»©«³Ì«á¥Ñ fyo00241 ©ó 2011-12-29 01:13 ½s¿è

·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)

TOP

¥»©«³Ì«á¥Ñ register313 ©ó 2011-12-29 08:29 ½s¿è

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

TOP

¥»©«³Ì«á¥Ñ fyo00241 ©ó 2011-12-29 08:56 ½s¿è

¤é°O±b°O¿ý.zip (832.98 KB)
¦^´_ 5# register313
³Â½Ð¤F¬OUserForm1³o­Ó¦a¤è,¤]½Ð¤j¤jÀ°§Ú¬Ý¤@¤U¨º¨Ç¦a¤è¥i¥H¦A²¤Æ,¶¶¤@ÂIªºÁÂÁ¤F!!

TOP

¦^´_ 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. If target.Column = 4 Then
  3.   a = target.Row
  4.   For b = 2 To 27
  5.     If Cells(a, 4) Like Cells(b, 7) & "*" Then
  6.        Cells(a, 5) = Cells(b, 8)
  7.     End If
  8.   Next b
  9. End If

  10. End Sub
½Æ»s¥N½X
·s¼WMicrosoft Excel ¤u§@ªí.rar (6.59 KB)

TOP

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

TOP

¦^´_ 6# fyo00241

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

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

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

TOP

¥»©«³Ì«á¥Ñ fyo00241 ©ó 2011-12-29 21:43 ½s¿è

¦^´_ 8# GBKEE


³ø§iG¤j:3543

TOP

        ÀR«ä¦Û¦b : ¡i¥Í©R¦b©I§l¶¡¡j¦òªû»¡¡G¡u¥Í©R¦b©I§l¶¡¡C¡v¤HµLªkºÞ¦í¦Û¤vªº¥Í©R¡A§óµLªk¾×¦í¦º´Á¡AÅý¦Û¤v¥Ã¦í¤H¶¡¡C¬JµM¥Í©R¥h¨Ó³o»òµL±`¡A§Ú­Ì§óÀ³¸Ó¦n¦n¦a·R±¤¥¦¡B§Q¥Î¥¦¡B¥R¹ê¥¦¡AÅý³oµL±`¡BÄ_¶Qªº¥Í©R¡A´²µo¥¦¯uµ½¬üªº¥ú½÷¡A¬M·Ó¥X¥Í©R¯u¥¿ªº»ù­È¡C
ªð¦^¦Cªí ¤W¤@¥DÃD