c_c_lai

¦^´_ 122# starbox520

¤U¸ü (69.65 KB)

2016-12-24 18:40

c_c_lai

¦^´_ 118# starbox520
¸Õ¸Õ¬Ý¡I

1. Private Sub CommandButton2_Click()
   
2.     Dim Arr As Variant, Brr As Variant, aa As Variant, i&, j&, x$, Myr&
   
3.     Dim d As Object, t As Variant
   
4. 
   
5.     Set d = CreateObject("Scripting.Dictionary")
   
6.    
   
7.    
   
8.     '  ¤ñ¹ï "TR±Æ¾÷&²£¥X" ¦Ç¦âÄæ¦ì "E" (Customer)¡B "F" (Package)¡B "G" (Bodysize)¡B "H" (L/C) ¤ñ¹ï
   
9.     '  "¶q¤j¥¼±Æ¾÷" §ä¨ì¤@¼Ëªº¡A ¨q¤W "TR±Æ¾÷&²£¥X" ªº "B" Äæ (¾÷¥x½s¸¹) ¦P¤@¦Cªº "¾÷¥x½s¸¹"¡C
   
10.     With Sheets("TR±Æ¾÷&²£¥X")
    
11.         Arr = .[A1].CurrentRegion.Value
    
12.    
    
13.         For i = 4 To (UBound(Arr) - 3) Step 5
    
14.             If Not IsError(Arr(i, 5)) Then   '& "|" & Arr(i, 6) & "|" & Arr(i, 7) & "|" & Arr(i, 8)
    
15.                  x = Arr(i, 5) & "|" & Arr(i, 6) & "|" & Arr(i, 7) & "|" & Arr(i, 8)
    
16.                  d(x) = IIf(IsEmpty(d(x)), CStr(i), d(x) & "," & CStr(i))
    
17.             End If
    
18.         Next
    
19.     End With
    
20. 
    
21.     With Sheets("¶q¤j¥¼±Æ¾÷")
    
22.         Brr = .[A1].CurrentRegion
    
23.         
    
24.         For i = 2 To UBound(Brr)
    
25.             x = Brr(i, 1) & "|" & Brr(i, 2) & "|" & Brr(i, 3) & "|" & Brr(i, 4)
    
26.             
    
27.             If d.exists(x) Then
    
28.                 t = d(x)
    
29.                
    
30.                 If InStr(t, ",") Then
    
31.                     aa = Split(t, ",")
    
32.                     
    
33.                     For j = 0 To UBound(aa)
    
34.                         .Cells(i, 9 + j) = Arr(aa(j), 2)
    
35.                     Next
    
36.                 Else
    
37.                     .Cells(i, 9) = Arr(t, 2)
    
38.                 End If
    
39.             End If
    
40.         Next
    
41.         
    
42.         '  ¥D­n·Q³]¸m "H" (¼Æ¶q) Äæ ¼Æ¶q¥Ñ¤j¦Ü¤p¡A³oÃä¥H¿ý»s¥¨¶°¨Ï¥Î
    
43.         .AutoFilter.Sort.SortFields.Clear
    
44.         .AutoFilter.Sort.SortFields.Add Key:=Range( _
    
45.                 "H1:H500"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
    
46.                 xlSortNormal
    
47.         
    
48.         With .AutoFilter.Sort
    
49.             .Header = xlYes
    
50.             .MatchCase = False
    
51.             .Orientation = xlTopToBottom
    
52.             .SortMethod = xlPinYin
    
53.             .Apply
    
54.         End With
    
55.     End With
    
56. End Sub

½Æ»s¥N½X

starbox520

¦^´_ 120# c_c_lai


    ³o°ÝÃD¦û¤F¦n¦hªº¼Ó¼h  
    ·d¨ì§Ú³s§Ú¦^´_ªº©«³£¬Ý¤£¨ì¤FXD
    C¤j¦³¨ä¥L¥i¥H¦^´_ªº¦a¤è¶Ü~
    §Ú­Ì³B²z§¹¥áµ²ªG¤W¨Ó¦n¹³¤ñ¸û¤£¦ûªÅ¶¡...

starbox520

¦^´_ 120# c_c_lai

ªþÀɤ¤§Ú¦³¥[¤J³o­Ó«ö¶s
¦pªG¤ñ¹ï¬Û¦Pªº¸Ü
§Ú·|¨q¨º¤@Ä檺¾÷¥x"½s¸¹"
¤f»~°ÕQ0Q

¤U¸ü (11.05 KB)

2016-12-24 16:49

1. Private Sub CommandButton2_Click()
   
2. Sheets("¶q¤j¥¼±Æ¾÷").Select
   
3. Dim Arr, i&, Brr, aa, j&, x$, Myr&
   
4. 
   
5. Dim d, k, t
   
6. 
   
7. Set d = CreateObject("Scripting.Dictionary")
   
8. 
   
9. Sheets("¶q¤j¥¼±Æ¾÷").Activate
   
10. '¤ñ¹ï"TR±Æ¾÷&²£¥X" ¦Ç¦âÄæ¦ì"E""F""G""H"¤ñ¹ï"¶q¤j¥¼±Æ¾÷"§ä¨ì¤@¼Ëªº¡A¨q¤W"TR±Æ¾÷&²£¥X"ªº"B"Äæ¦P¤@¦Cªº¾÷¥x½s¸¹
    
11. With Sheets("TR±Æ¾÷&²£¥X")
    
12. 
    
13.     Myr = .Cells(.Rows.Count, 12).End(xlUp).Row
    
14. 
    
15.     Arr = .Range("a1:p" & Myr)
    
16. 
    
17. End With
    
18. 
    
19. For i = 4 To UBound(Arr) Step 5
    
20. 
    
21.     If Not IsError(Arr(i, 5)) Then   '& "|" & Arr(i, 6) & "|" & Arr(i, 7) & "|" & Arr(i, 8)
    
22. 
    
23.     x = Arr(i, 5) & "|" & Arr(i, 6) & "|" & Arr(i, 7) & "|" & Arr(i, 8)
    
24. 
    
25.     d(x) = d(x) & i & ","
    
26. 
    
27.     End If
    
28. 
    
29. Next
    
30. 
    
31. Brr = [a1].CurrentRegion
    
32.    
    
33. For i = 2 To UBound(Brr) Step 5
    
34. 
    
35.    x = Brr(i, 1) & "|" & Brr(i, 2) & "|" & Brr(i, 3) & "|" & Brr(i, 4)
    
36. 
    
37.   If d.exists(x) Then
    
38. 
    
39.       t = d(x)
    
40. 
    
41.         t = Left(t, Len(t) - 1)
    
42. 
    
43.        If InStr(t, ",") Then
    
44. 
    
45.            aa = Split(t, ",")
    
46. 
    
47.            For j = 0 To UBound(aa)
    
48. 
    
49.                Cells(i, 9 + j) = Arr(aa(j), 2)
    
50. 
    
51.            Next
    
52.       Else
    
53. 
    
54.            Cells(i, 9) = Arr(t, 2)
    
55. 
    
56.        End If
    
57. 
    
58.     End If
    
59. 
    
60. Next
    
61. '¥D­n·Q³]¸m"H"Äæ¼Æ¶q¥Ñ¤j¦Ü¤p¡A³oÃä¥H¿ý»s¥¨¶°¨Ï¥Î
    
62. ActiveWorkbook.Worksheets("¶q¤j¥¼±Æ¾÷").AutoFilter.Sort.SortFields.Clear
    
63.     ActiveWorkbook.Worksheets("¶q¤j¥¼±Æ¾÷").AutoFilter.Sort.SortFields.Add Key:=Range( _
    
64.         "H1:H500"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
    
65.         xlSortNormal
    
66.     With ActiveWorkbook.Worksheets("¶q¤j¥¼±Æ¾÷").AutoFilter.Sort
    
67.         .Header = xlYes
    
68.         .MatchCase = False
    
69.         .Orientation = xlTopToBottom
    
70.         .SortMethod = xlPinYin
    
71.         .Apply
    
72.     End With
    
73. End Sub

½Æ»s¥N½X

starbox520

¦^´_ 119# c_c_lai


    ´«§Ú²´ªá¤F§Ú¨S¬Ý¨ì³o¦æ«¢«¢...
    ­è­è´ú¸Õ¹L¤F!!
    ¨S¿ù«¢«¢~

c_c_lai

¦^´_ 118# starbox520
¤S¡B"¶q¤j¥¼±Æ¾÷" ªº¾÷¥x¸¹½X ¬O±q¨º¸Ì«_¥X¨Óªº¡H

c_c_lai

¦^´_ 118# starbox520
¨º #116 ¤W¦³Ãö BGA¡BQFN¡BQFP ªºµ{¦¡³¡¤À¡A
©p´ú¸Õªºµ²ªG¬O¤£¬O¦p§A©ÒÄ@¡H

starbox520

¦^´_ 117# c_c_lai


    ³oÃ䪺³¡¤À¬O§Ú  ¨Ò¦p§A¬Ý¨ìSQH191
    ¥L¤£¥i¯à¥X²{¦b¨âµ§¼Æ¾Ú¤W­±~
    ¦pªG¨Ï¥ÎªÌ¤â´Ý¡A¤@ª½ÂI«ö¶sªº¸Ü
    ´N·|¥X²{³oºØBug
    ¦ý§Ú¸Õ¹Ldebug­ì¦]¦n¹³¬O¥X¦b§Ú«á­±IJKLÄæ  ¤£·|¥ýClear±¼~"~
    ¾É­P¨C¦¸«ö¶s«ö¤U¥h¤ñ¹ïªº®É­Ô
    ·|¥X²{¿ù¶Ã«¢«¢
    §Ú¦³­Ó·Qªk´N¬O§Ú¦bArrangeMent³oÃä

1. If N = 0 Then Exit Sub
   
2.     With Sheets("¤u§@ªí2")
   
3.    .[A2].Resize(N, 8) = Brr
   
4.   
   
5. End With
   
6. If N = 0 Then Exit Sub
   
7.     With Sheets("¶q¤j¥¼±Æ¾÷")
   
8.    .[A2].Resize(N, 8) = Brr
   
9.    End With
   
10. End Sub

½Æ»s¥N½X

¶q¤j¥¼±Æ¾÷³¡¤À
§ï¦¨   .[A2].Resize(N, 12) = Brr
³o¼Ë¨C¦¸´N·|§ó·s¨ì«á­±³o4¦æ
¦b«ö¤U§Ú³]­pªº"¶q¤j¥¼±Æ¾÷"«ö¶s
¦ü¥G´N¤ñ¸û¤£·|¥X¿ù
¤£¹L³o¥u¬O§Úªº·QªkXD...
¦]¬°§Úµo²{³o¼Ë§ïªº¸Ü«á­±·|Åܦ¨¶]"N/A"¥X¨Ó


©Ò¥HC¤jÀ´§ÚBGA.QFN.QFP  ³o3ºØ¯S®íªºª¬ªp°µ§PÂ_
¦A»¡¤°»ò¤F¶Ü«¢«¢

c_c_lai

¦^´_ 101# starbox520

¤U¸ü (236.75 KB)

2016-12-24 10:36

c_c_lai

¦^´_ 111# starbox520

1.                 Select Case cts
   
2.                     Case 3      '  ²Ä 3 ºØ¿z¿ï¤è¦¡¡G TR±Æ¾÷&²£¥X    E F G H   (¤u§@ªí2        A B C D)
   
3.                         '  tf = arSh2(ct2, 1) = Adt_Rng.Offset(, -1) And arSh2(ct2, 2) = Adt_Rng.Value And arSh2(ct2, 3) = Adt_Rng.Offset(, 1) And arSh2(ct2, 4) = Adt_Rng.Offset(, 2)
   
4.                         tf = arSh2(ct2, 1) = Adt_Rng.Offset(, -1) And Right(arSh2(ct2, 2), 3) = Right(Adt_Rng.Value, 3) And arSh2(ct2, 3) = Adt_Rng.Offset(, 1) And arSh2(ct2, 4) = Adt_Rng.Offset(, 2)
   
5.                     Case 4      '  ²Ä 4 ºØ¿z¿ï¤è¦¡¡G TR±Æ¾÷&²£¥X      F G H   (¤u§@ªí2          B C D)
   
6.                         '  tf = arSh2(ct2, 2) = Adt_Rng.Value And arSh2(ct2, 3) = Adt_Rng.Offset(, 1) And arSh2(ct2, 4) = Adt_Rng.Offset(, 2)
   
7.                         tf = Right(arSh2(ct2, 2), 3) = Right(Adt_Rng.Value, 3) And arSh2(ct2, 3) = Adt_Rng.Offset(, 1) And arSh2(ct2, 4) = Adt_Rng.Offset(, 2)
   
8.                     Case 5      '  ²Ä 5 ºØ¿z¿ï¤è¦¡¡G TR±Æ¾÷&²£¥X      F G     (¤u§@ªí2          B C  )
   
9.                         '  tf = arSh2(ct2, 2) = Adt_Rng.Value And arSh2(ct2, 3) = Adt_Rng.Offset(, 1)
   
10.                         tf = Right(arSh2(ct2, 2), 3) = Right(Adt_Rng.Value, 3) And arSh2(ct2, 3) = Adt_Rng.Offset(, 1)
    
11.                 End Select

½Æ»s¥N½X