Hsieh

¦^´_ 2# sax868

1. Sub ex()
   
2. Dim A As Range
   
3. Set d = CreateObject("Scripting.Dictionary")
   
4. With Sheet2 'Size
   
5. For Each A In .Range(.[A2], .[A2].End(xlDown))
   
6.    d(A & A.Offset(, 2)) = A.Offset(, 3).Value
   
7. Next
   
8. With Sheet3 'Color
   
9. For Each A In .Range(.[A2], .[A2].End(xlDown))
   
10.    d(A & A.Offset(, 2)) = A.Offset(, 3).Value
    
11. Next
    
12. With Sheet1 'Original
    
13. For Each A In .Range(.[G2], .[G2].End(xlDown))
    
14. ar = Split(Replace(A.Offset(, 1), " ", ""), ",") 'Color
    
15. ay = Split(Replace(A.Offset(, 3), " ", ""), ",") 'Size
    
16.    For i = 0 To UBound(ar)
    
17.        y = y + d(A & ar(i))
    
18.    Next
    
19.    For i = 0 To UBound(ay)
    
20.       x = x + d(A & ay(i))
    
21.    Next
    
22.    
    
23.    A.Offset(, 8) = A & A.Offset(, -1)
    
24.    A.Offset(, 9).Resize(, 2) = Array(y, x)
    
25.    x = 0: y = 0
    
26. Next
    
27. End With
    
28. End With
    
29. End With
    
30. End Sub

½Æ»s¥N½X

Hsieh

¦^´_ 5# sax868

1. Sub ex()
   
2. 
   
3. Dim A As Range
   
4. 
   
5. Set d = CreateObject("Scripting.Dictionary")
   
6. 
   
7. With Sheet2 'Size
   
8. 
   
9. For Each A In .Range(.[A2], .Cells(.Rows.Count, 1)).SpecialCells(xlCellTypeConstants)
   
10. 
    
11.    d(A & A.Offset(, 2)) = A.Offset(, 3).Value
    
12. 
    
13. Next
    
14. 
    
15. With Sheet3 'Color
    
16. 
    
17. For Each A In .Range(.[A2], .Cells(.Rows.Count, 1)).SpecialCells(xlCellTypeConstants)
    
18. 
    
19.    d(A & A.Offset(, 2)) = A.Offset(, 3).Value
    
20. 
    
21. Next
    
22. 
    
23. With Sheet1 'Original
    
24. 
    
25. For Each A In .Range(.[G2], .Cells(.Rows.Count, 7)).SpecialCells(xlCellTypeConstants)
    
26. 
    
27. ar = Split(Replace(A.Offset(, 1), " ", ""), ",") 'Color
    
28. 
    
29. ay = Split(Replace(A.Offset(, 3), " ", ""), ",") 'Size
    
30. 
    
31.    For i = 0 To UBound(ar)
    
32. 
    
33.        y = y + d(Left(A, 7) & ar(i))
    
34. 
    
35.    Next
    
36. 
    
37.    For i = 0 To UBound(ay)
    
38. 
    
39.       x = x + d(Left(A, 7) & Split(ay(i), "-")(0))
    
40. 
    
41.    Next
    
42. 
    
43.    
    
44. 
    
45.    A.Offset(, 8) = A & A.Offset(, -1)
    
46. 
    
47.    A.Offset(, 9).Resize(, 2) = Array(Format(y, "#.##%"), Format(x, "#.##%"))
    
48. 
    
49.    x = 0: y = 0
    
50. 
    
51. Next
    
52. 
    
53. End With
    
54. 
    
55. End With
    
56. 
    
57. End With
    
58. 
    
59. End Sub

½Æ»s¥N½X

¤£²M·¡²Ä5­Ó°ÝÃD¦b´y­z¬Æ»ò?