Kubi

¦^´_ 1# »a³·
¥ÎVBA¸Ñ¦p¤U¡G
Sub test()
    arr = Array("L20", "L10", "L30")
    Application.AddCustomList listArray:=arr
    n = Application.CustomListCount
    For r = 1 To 9
        Cells(r, 2).Value = Split(Cells(r, 1).Value, "[")(0)
        Cells(r, 3).Value = Split(Split(Cells(r, 1).Value, "/")(0), "[")(1)
        Cells(r, 4).Value = Split(Split(Cells(r, 1).Value, "/")(1), "]")(0)
    Next r
    [A1].CurrentRegion.Sort Key1:=[B1], OrderCustom:=n + 1, key2:=[C1], key3:=[D1]
    Application.DeleteCustomList n
    Columns("B:D").Delete
End Sub

¥t¥~©³¤U¶¶§Ç¬O§_¤Ï¤F¡H

L20[7.5/B.5]S
L20[7/C]N

À³¸Ó¬O¡G
L20[7/C]N
L20[7.5/B.5]S

Kubi

¦^´_ 15# »a³·
¥i¯à­n¥ý¤F¸Ñ2­Ó°ÝÃD¡G
1.±Æ§Ç¯Á¤ÞÄæ¬O¦b¨º¤@Äæ¡H¨Ò¦p¡G¥»µ{¦¡¬O¥HAÄæ°µ¬°±Æ§Ç±ø¥ó¡C
2.¦UÄæ²Ä1¦C¦³¨S¦³©ïÀY¡H

¥»¥¨¶°¬O³z¹L»²§UÄæ¨Ó±Æ§Çªº¡A©Ò¥H­n¥ýÂç²MÄæ¦ì¦ì¸m¤Î¼Æ¶q¡A¦p¯à¤W¶ÇÀɮקó¦n¡C

Kubi

¦^´_ 17# »a³·
½Ð°Ñ¦Ò

1. Sub test()
   
2.     arr = Array("L20", "L10", "L30")
   
3.     Application.ScreenUpdating = False
   
4.     Application.AddCustomList listArray:=arr
   
5.     n = Application.CustomListCount
   
6.     er = [C65536].End(3).Row
   
7.     For r = 2 To er
   
8.         Cells(r, 5).Value = Split(Cells(r, 3).Value, "[")(0)
   
9.         Cells(r, 6).Value = Split(Split(Cells(r, 3).Value, "/")(0), "[")(1)
   
10.         Cells(r, 7).Value = Split(Split(Cells(r, 3).Value, "/")(1), "]")(0)
    
11.     Next r
    
12.     Range("A2:G" & er).Sort Key1:=[E2], OrderCustom:=n + 1, key2:=[F2], key3:=[G2]
    
13.     Application.DeleteCustomList n
    
14.     Columns("E:G").Delete
    
15.     Application.ScreenUpdating = True
    
16. End Sub

½Æ»s¥N½X

Kubi

¦^´_ 19# »a³·
Q1.¬yµ{»¡©ú¡G
1.¦]§Aªº²Ä¤@¶¶¦ì±Æ§Ç(L20, L10, L30)«D±`³W©Ê¡A¦]¦¹¥ý±N§Aªº³W«h®M¤J¦Û­q±Æ§Ç²M³æ¤¤¡C
2.±NCÄæ±Æ§Ç¯Á¤ÞÄ檺¸ê®Æ¡A§Q¥ÎSplit¸ê®Æ­åªR¤è¦¡©î¸Ñ¦ÜE¡BF¡BGªº»²§UÄ椺¡C
3.§Q¥ÎE¡BF¡BGÄ欰¯Á¤Þ¡A¨Ã¨Ì¨ä¶¶§Ç¤À3¦¸­«·s±Æ§Ç©Ò¦³¸ê®Æ¡A½Ðª`·N²Ä1¦¸¬O¥Î¦Û­q²M³æ¨Ó±Æ§Ç¡C
4.±Æ§Ç§¹¦¨«á¡A²¾°£­è¸m¤Jªº¦Û­q±Æ§Ç²M³æ¡A¤]§R°£¤£»Ý¦A¨Ï¥ÎªºE¡BF¡BGÄæ¡C

Q2.©î¶}¤§«á¡A¤S¬O«ç»ò±N¥L­Ì²Õ¦X¡H
¥Ñ¤W­z©Òª¾¡Aµ{¦¡¥u¦³©î¸Ñ¨Ã¨S¦³¦A±N¥¦²Õ¦X³á¡C

Kubi

¦^´_ 21# »a³·
¬O°Ú¡ã±Æ§Ç§¹¦¨«á¡A´N¤£»Ý­n¦A¯d¦sµL¥Îªº¸ê®ÆÅo¡C

¥t¥~¥H¼ÒÀÀ¸ê®Æªº¤è¦¡¡A¥[¼g¨â¤ä¤£¥Î¦Û­q²M³æªº±Æ§Çµ{¦¡¡A1¤ä¬O¥u¥Î1­Ó»²§UÄæ¨Ó±Æ§Ç¡A¥t¥~1¤ä¬O¤£¥Î
»²§UÄæ¨Ó±Æ§Ç¡A¦³¿³½ìªº¸Ü½Ð¤U¸ü°Ñ¦Ò¡C
ÁÙ¦³¡A¥H¤W§¡¨S¦³¥[¼g¨¾§bµ{¦¡½X³á¡A­Y¦³Bug½Ð¦Û¦æ°£¿ù¡C
Excel VBA±ø¥ó±Æ§Ç.rar (20.04 KB)

¤U¸ü¦¸¼Æ: 8

2018-1-9 20:29

Kubi

¦^´_ 23# »a³·
½Ð°Ñ¦Ò¡C
Sub test()
    arr = Array("L20", "L10", "L30")
    Application.ScreenUpdating = False
    er = [C65536].End(3).Row
    For r = 2 To er
        L = Application.Match(Split(Cells(r, 3).Value, "[")(0), arr, 0)
        N = Format(Split(Split(Cells(r, 3).Value, "/")(0), "[")(1), "00.0")
        E = Left(Split(Split(Cells(r, 3).Value, "/")(1), "]")(0), 1)
        If L = 1 And N >= 7 And N <= 16 And Asc(E) >= 65 And Asc(E) <= 70 Then
            Cells(r, 5).Value = L & N & E
        End If
    Next r
    Range("E2:E" & er).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    Range("A2:E" & er).Sort Key1:=[E2]
    Columns("E").ClearContents
    Application.ScreenUpdating = True
End Sub