[µo°Ý] ¨D§U

aa7551

[attach]13980[/attach]¸ê®Æ¦³¦¨«~¤Î¹s¥ó,·Q­n¿ï¥X:
¤@¦¨«~¤¤§ä¥X²Ä¤­,¤»½X¬°11,17,20,25,37,¤Î²Ä¤E½X¦³¬Û¹ï¤§L,R(¨ä¾l½X¬Û¦PªÌ)¥u¿ï¥XL
¤G¹s¥ó²Ä¤K,¤E½X¬°11,20,¤Î²Ä¤Q¤G½X¦³¬Û¹ï¤§L,R(¨ä¾l½X¬Û¦PªÌ)¥u¿ï¥XL
½Ð¦U¦ì¤j¤jÀ°¦£

aa7551

¦^´_ 19# stillfish00
ÁÂÁ¤j¤j«ü±Ð,À³¬O¥¿½T¸Ñµª

stillfish00

¦^´_ 18# aa7551

1. Sub test()
   
2. Dim ar, s5s6, s8s9, s, d1, d2
   
3. Set d1 = CreateObject("scripting.dictionary")
   
4. Set d2 = CreateObject("scripting.dictionary")
   
5. 
   
6. With Sheets(1)
   
7.     ar = .Range(.[a2], .[a2].End(xlDown)).Value
   
8.     For Each s In ar
   
9.         s5s6 = Mid(s, 5, 2)
   
10.         If Mid(s, 4, 1) = "-" And (s5s6 = "11" Or s5s6 = "17" Or s5s6 = "20" Or s5s6 = "25" Or s5s6 = "37") Then d1.Item(s) = ""
    
11.             
    
12.         s8s9 = Mid(s, 8, 2)
    
13.         If Mid(s, 7, 1) = "-" And (s8s9 = "11" Or s8s9 = "20") Then d2.Item(s) = ""
    
14.     Next
    
15.     For Each s In d1.keys
    
16.         If Mid(s, 9, 1) = "R" And d1.exists(Left(s, 8) & "L" & Mid(s, 10)) Then
    
17.             d1.Remove (s)
    
18.         End If
    
19.     Next
    
20.     For Each s In d2.keys
    
21.         If Mid(s, 12, 1) = "R" And d2.exists(Left(s, 11) & "L" & Mid(s, 13)) Then
    
22.             d2.Remove (s)
    
23.         End If
    
24.     Next
    
25.    
    
26.     .[k2].Resize(d1.Count) = Application.Transpose(d1.keys)
    
27.     .[n2].Resize(d2.Count) = Application.Transpose(d2.keys)
    
28. End With
    
29. Set d1 = Nothing: Set d2 = Nothing
    
30. End Sub

½Æ»s¥N½X

aa7551

µ§ªÌ§@·~¼Ò¦¡¬O³vµ§§R°£:
¤@.¦¨«~²Ä¤­,¤»½X¬O§_²Å¦X11,17,20,25,37,¦p¤£²Å¦X§Y§R°£,¦A±N²Ä¤E½X¬OL,R°t¹ï(¨ä¾l½X¬O¬Û¦PªÌ,¦p112-1104L-LD,112-1104R-LD)©ñ¦b¤@°_,°t¹ï¦¨¥\,§R¥hR(112-1104R-LD),¦pµL°t¹ï,«h«O¯d
¤G.¹s¥ó²Ä¤K,¤E½X¬O§_²Å¦X11,20,¦p¤£²Å¦X§Y§R°£,¦A±N²Ä¤Q¤G½X¬OL,R°t¹ï(¨ä¾l½X¬O¬Û¦PªÌ,¦p00#440-1126LEND,00#440-1126REND)©ñ¦b¤@°_,°t¹ï¦¨¥\,§R¥hR(00#440-1126REND),¦pµL°t¹ï,«h«O¯d

aa7551

¦^´_ 15# Hsieh
ÁÂÁ¤j¤j«ü±Ð,´£¨Ñ¤@­Ó·s¤è¦¡

aa7551

¦^´_ 12# yanto913
ÁÂÁ¤j¤jªº«ü±Ð,§Ú¥ý»¡©ú¦Û¤vªº¤â°Ê§@ªk,¥ý§âLR°t¹ï©ñ¦b¤@°_,½T»{¦³°t¹ï,¦AÀˬd¬O§_²Å¦X¦¨«~11,20©Î¹s¥ó11,17,20,25,37,¦p¦³²Å¦X,«h§R¥hR

Hsieh

¦^´_ 9# aa7551

³W«h¦pªG¯à½T©w¡A¥Î¶i¶¥¿z¿ï¤]¬O¤£¿ùªº¤èªk

    ¶i¶¥¿z¿ï.rar (131.14 KB)

¤U¸ü¦¸¼Æ: 9

2013-1-18 23:26

aa7551

¦^´_ 13# stillfish00
115-1926L-A
132-1905L-LD-U
211-1946L-A
211-1946L-A-S        ³o¨Ç¤£²Å¦X±ø¥ó,¦¨«~ªº²Ä¤­,¤»½X­nµ¥©ó11,17,20,25,37

stillfish00

¦^´_ 11# aa7551
³o¼Ë§PÂ_§A¬Ýµ²ªG²Å¤£²Å¦X

1. Sub test()
   
2. Dim ar, s, ss, d1, d2
   
3. Set d1 = CreateObject("scripting.dictionary")
   
4. Set d2 = CreateObject("scripting.dictionary")
   
5. 
   
6. With Sheets(1)
   
7.     ar = .Range(.[a2], .[a2].End(xlDown)).Value
   
8.     For Each s In ar
   
9.         '¦¨«~
   
10.         If s Like "???-????L*" Then
    
11.             d1.Item(s) = ""
    
12.             ss = Left(s, 8) & "R" & Mid(s, 10)
    
13.             If d1.exists(ss) Then d1.Remove (ss)
    
14.         End If
    
15.         If s Like "???-????R*" Then '«D¦¨¹ï«O¯d
    
16.             If Not d1.exists(Left(s, 8) & "L" & Mid(s, 10)) Then d1.Item(s) = ""
    
17.         End If
    
18.         
    
19.         '¹s¥ó
    
20.         If s Like "??????-????L*" Then
    
21.             d2.Item(s) = ""
    
22.             ss = Left(s, 11) & "R" & Mid(s, 13)
    
23.             If d2.exists(ss) Then d2.Remove (ss)
    
24.         End If
    
25.         If s Like "??????-????R*" Then '«D¦¨¹ï«O¯d
    
26.             If Not d2.exists(Left(s, 11) & "L" & Mid(s, 13)) Then d2.Item(s) = ""
    
27.         End If
    
28.     Next
    
29.     .[k2].Resize(d1.Count) = Application.Transpose(d1.keys)
    
30.     .[n2].Resize(d2.Count) = Application.Transpose(d2.keys)
    
31. End With
    
32. Set d1 = Nothing: Set d2 = Nothing
    
33. End Sub

½Æ»s¥N½X

yanto913

§ï¦¨¤£µ¥©ó"R"