[µo°Ý] ¸óÄæ¸m¤¤»PÂ^¨ú¤å¦r°ÝÃD

v03586

·Q½Ð°Ý¦U¦ì°ª¤â­Ì
§Ú·Q­nÅã¥Ü¹Ï¤ù¤¤ªº®ÄªG
¸ê®Æ¤@¶}©l³£¬O²V¶Ãªº(¤W¤dµ§)
1. ¥ý¨Ì·ÓAÄæ¥ý±Æ§Ç, ª¾«á¦A¨Ì·Ó¬Û¦PªF¦è(¦pAAA)¦A¤@¦¸¨Ì·ÓDÄæ¦A±Æ§Ç¤@¦¸
2. ¨Ì·ÓCÄæ,DÄæ,EÄæ °µÂ²¤Æ
   2-1. CÄæ¦@³qÂI±q²Ä¤@­Ó¦r¤¸¶}©l¨ì²Ä¤@­Ó¡y  -  ¡z ³£«O¯d
   2-2. DÄæ¦@³qÂI±q05-xxxx- ²Ä¤G­Ó¡y  -  ¡z ³£«O¯d
   2-3. EÄæ¦@³qÂI²Ä¤@­Ó¦r¤¸¨ì¨ä¤¤·|¦³­Ó¦r¦ê¡y  SPC  ¡z ©Î ¡y  SCL  ¡z,«á­±³£¤£­n
   2-4. ¥i¥H¦AÀ°§Ú¯u¹ïEÄæ ¦A°µ¥t¤@­Óª©¥»¶Ü , ¥t¤@­Óª©¥»¬O±q¦r¦ê¤¤ ,±q¡yQVS¡z«á¨ì¡y  SPC  ¡z ©Î ¡y  SCL  ¡z
3. §¹¦¨1¡B2¨BÆJ«á,¨Ì·ÓBÄæ¬Û¦Pªº¸óÄæ¸m¤¤, ¦p¹Ï¤ù¤¤
4.¨Ì·ÓDÄæ¬Û¦Pªº¦r¦ê¥Î¶Â®Ø½u
5.BÄæ¦ì¸ê®Æ¦rÅé¤j¤p16 , C»PDÄæ¦rÅé¤j¤p12 , ¸ê®ÆªíÁY©ñ¤ñ¨Ò63%À˵ø

¤U¸ü (201.53 KB)

2016-1-9 21:41

stillfish00

¦^´_ 1# v03586
¹Ï¤ù±K±K³Â³Â«ç¤£ªþÀÉ¡A­n¸Õªº¤H¦Û¤v¨Ì¹Ï¥h¶ñ¶Ü?

hcm19522

http://blog.xuite.net/hcm19522/twblog/372926021
http://blog.xuite.net/hcm19522/twblog/372906150
°Ñ¦Ò

v03586

Sorry , ¸ÉÀÉ®×
test.rar (87.5 KB)

¤U¸ü¦¸¼Æ: 9

2016-1-11 20:20

v03586

¦^´_ 3# hcm19522


    ¤j¤jªGµM¬O¨ç¼Æ¹F¤H!!!!  ¦]³B²z¸ê®Æ¤W¤dµ§, ©Ò¥H¥²¶·¼g¤J°j°é
§Úı±o¤j¤j³o­Ó¤èªk¤]«Ü¦n!!!­È±o°Ñ¦Ò

stillfish00

¦^´_ 4# v03586
Try this
4.¨Ì·ÓDÄæ¬Û¦Pªº¦r¦ê¥Î¶Â®Ø½u  => §ï¬°¨Ì¦X¨ÖªºAÄæµe²Ê®Ø

1. Sub Test()
   
2.     Dim oRegexp As Object: Set oRegexp = CreateObject("vbscript.regexp")
   
3.     Dim ar, i, j, s
   
4.    
   
5.     ar = Sheets("³øªí").Range("A9:F21").Value  'or .Range("A9").CurrentRegion.Value
   
6.    
   
7.     With oRegexp
   
8.         .Pattern = "^[^-]*-"
   
9.         For i = 1 To UBound(ar)
   
10.             If .Test(ar(i, 3)) Then ar(i, 3) = .Replace(ar(i, 3), "")
    
11.         Next
    
12.         .Pattern = "^[^-]*-[^-]*-"
    
13.         For i = 1 To UBound(ar)
    
14.             If .Test(ar(i, 4)) Then ar(i, 4) = .Replace(ar(i, 4), "")
    
15.         Next
    
16.         .Pattern = "^(.{2})(.)(.*)[a-zA-Z]$"
    
17.         For i = 1 To UBound(ar)
    
18.             If .Test(ar(i, 5)) Then
    
19.                 s = .Replace(ar(i, 5), "$1-$2-$3")
    
20.                 mch = Application.VLookup(s, Sheets("Flow").[A:B], 2, False)
    
21.                 If Not IsError(mch) Then ar(i, 5) = mch
    
22.             End If
    
23.         Next
    
24.         .Pattern = "(SPC|SCL).*$"
    
25.         '¥t¤@ºØ QVS to SPC/SCL
    
26.         '.Pattern = "^.*?(QVS.*?(SPC|SCL)).*$"
    
27.         For i = 1 To UBound(ar)
    
28.             If .Test(ar(i, 6)) Then ar(i, 6) = .Replace(ar(i, 6), "$1")
    
29.         Next
    
30.         
    
31.     End With
    
32.    
    
33.     Application.ScreenUpdating = False
    
34.     With Sheets.Add.[A1].Resize(UBound(ar), UBound(ar, 2))
    
35.         .Value = ar
    
36.         .Sort key1:=.Cells(1, 1), order1:=xlAscending, key2:=.Cells(1, 4), order2:=xlAscending
    
37.         .Borders.LineStyle = xlContinuous
    
38.         .Borders.Weight = xlThin
    
39.         Application.DisplayAlerts = False
    
40.         i = 1
    
41.         For j = 1 To UBound(ar)
    
42.             If .Cells(j, 2).Value <> .Cells(j + 1, 2).Value Then
    
43.                 If i <> j Then Range(.Cells(i, 1), .Cells(j, 1)).Merge
    
44.                 With .Cells(i, 1).MergeArea.Resize(, .Columns.Count)
    
45.                     .Borders(xlEdgeTop).Weight = xlMedium
    
46.                     .Borders(xlEdgeBottom).Weight = xlMedium
    
47.                     .Borders(xlEdgeLeft).Weight = xlMedium
    
48.                     .Borders(xlEdgeRight).Weight = xlMedium
    
49.                 End With
    
50.                 i = j + 1
    
51.             End If
    
52.         Next
    
53.         Application.DisplayAlerts = True
    
54.         
    
55.         [B:B].Font.Size = 16
    
56.         [C:D].Font.Size = 12
    
57.         ActiveWindow.Zoom = 63
    
58.         [A:F].EntireColumn.AutoFit
    
59.         [A:B].HorizontalAlignment = xlCenter
    
60.     End With
    
61.     Application.ScreenUpdating = True
    
62. End Sub

½Æ»s¥N½X

v03586

¦^´_ 6# stillfish00


    ·PÁ¤j¤jªºÀ°¦£!!!
¦ý§Ú³¡¤À¬Ý¤£¤Ó²M·¡ , ¥i§_³Â·Ð¤j¤j°w¹ï´X¶µ¥\¯à¨ó§U°µ­Ó»¡©ú

EX :

1.    With oRegexp
   
2.         .Pattern = "^[^-]*-"
   
3.         For i = 1 To UBound(ar)
   
4.             If .Test(ar(i, 3)) Then ar(i, 3) = .Replace(ar(i, 3), "")
   
5.         Next
   
6.         .Pattern = "^[^-]*-[^-]*-"

½Æ»s¥N½X

³o¤@¬q¬O§PÂ_¤°»ò¥\¯à?

1.         For i = 1 To UBound(ar)
   
2.             If .Test(ar(i, 4)) Then ar(i, 4) = .Replace(ar(i, 4), "")
   
3.         Next
   
4.         .Pattern = "^(.{2})(.)(.*)[a-zA-Z]$"

½Æ»s¥N½X

³o¤@¬q¬O§PÂ_¤°»ò¥\¯à
§Ú¤£ª¾¹D³o¼Ë¤Á ¦³¨S¦³¤Á¿ù¬q¸¨

stillfish00

¦^´_ 7# v03586

1. oRegexp : regular expression ¥¿«hªí¹F¦¡/¥¿³Wªí¥Üªk¡A¥Î¨Ó¬d§ä¡B´À´«¡B´£¨ú¡BÅçÃÒ¦r¦ê¡A
   
2. ¡A¥i°Ñ¦Ò http://club.excelhome.net/thread-1128647-1-2.html ¾Ç²ß
   
3. 
   
4. .Pattern = "^[^-]*-" ¥Î¨Ó³]©w³W«h
   
5. ^ : ¦r¦ê¶}ÀY¦ì¸m
   
6. [^-] : «D-ªº¥ô·N¦r¤¸
   
7. [^-]* : 0~n­Ó«D-ªº¥ô·N¦r¤¸
   
8. - : -¦r¤¸
   
9. ©Ò¥H¬O¤Ç°t¶}ÀY¨ì²Ä¤@­Ó-¦r¤¸
   
10. 
    
11. .Test ¬O¥Î¨Ó´ú¸Õ¬O§_²Å¦X³W«h
    
12. .Replace ¬O§â²Å¦X³W«h³¡¤Àªº¦r¦ê¨ú¥N±¼

½Æ»s¥N½X

­ã´£³¡ªL

¥Î³Ì°ò¥»ªºµ{¦¡»yªk, ¦Û¦æ¥h¬ã¨s, ¤£¦A¦h»¡©ú~~

1. Sub TEST()
   
2. Dim R&, xArea As Range, xR As Range, xH As Range, T, TT, X
   
3. R = [³øªí!A65536].End(xlUp).Row: If R < 9 Then Exit Sub
   
4. Set xArea = Sheets("³øªí").Range("A9:A" & R)
   
5. For Each xR In xArea
   
6. ¡@¡@T = xR(1, 3): xR(1, 3) = Mid(T, InStr(T, "-") + 1)
   
7. ¡@¡@xR(1, 4) = Right(xR(1, 4), 9)
   
8.      ¡@
   
9. ¡@¡@T = xR(1, 5):  T = Left(T, 2) & "-" & Mid(T, 3, 1) & "-" & Mid(T, 4, 4)
   
10. ¡@¡@TT = Application.VLookup(T, [Flow!A:B], 2, 0)
    
11. ¡@¡@If Not IsError(TT) Then xR(1, 5) = TT Else xR(1, 5).Font.Color = vbRed
    
12.      ¡@
    
13. ¡@¡@T = xR(1, 6)
    
14. ¡@¡@T = Mid(T & ",QVS", InStr(T, "QVS") + 4) '¨ú QVS ¥H«á¦r¦ê
    
15. ¡@¡@For Each TT In Array("SPC", "SCL")
    
16. ¡@¡@¡@¡@X = InStr(T, TT): If X > 0 Then xR(1, 6) = Left(T, X + 2): Exit For
    
17. ¡@¡@Next
    
18. Next
    
19. ¡@
    
20. xArea.Resize(, 6).Sort Key1:=xArea(1, 1), Order1:=xlAscending, _
    
21.                        Key2:=xArea(1, 4), Order2:=xlAscending, Header:=xlNo
    
22.                       ¡@
    
23. Application.DisplayAlerts = False
    
24. For Each xR In xArea
    
25. ¡@¡@If xR & xR(1, 2) <> xR(0) & xR(0, 2) Then Set xH = xR
    
26. ¡@¡@If xR & xR(1, 2) <> xR(2) & xR(2, 2) Then
    
27. ¡@¡@¡@Range(xH, xR).Merge: Range(xH(1, 2), xR(1, 2)).Merge
    
28. ¡@¡@¡@Range(xH, xR(1, 6)).Borders.LineStyle = 1
    
29. ¡@¡@¡@For i = 7 To 10
    
30. ¡@¡@¡@¡@¡@Range(xH, xR(1, 6)).Borders(i).Weight = xlMedium
    
31. ¡@¡@¡@Next i
    
32. ¡@¡@End If
    
33. Next
    
34. End Sub

½Æ»s¥N½X

v03586

¦^´_ 9# ­ã´£³¡ªL


    Excel ¥´¶}¤ñ¹ïA2Äæ¦ì¦³ ON HAND--PC_ONHAND2HR_1ST_FLOW
´Nµ¥¦P¶×¤J³øªí¶Ü