Hsieh

¦^´_ 1# Duck

¤£­«½Æ©w¸q¬O§_B&D¤£­«½Æ?

¤U¸ü (220.24 KB)

2014-7-25 10:54

¦pªG¬Oªº¸Ü
´¡¤J¦Û­qªí³æ¡A¥¬¸m¤@­Ó¤U©Ô²M³æ
ªí³æ¼Ò²Õ¤¤µ{¦¡½X¦p¤U

1. Private Sub ComboBox1_Change()
   
2. Set d = CreateObject("Scripting.Dictionary")
   
3. With ¤u§@ªí2
   
4. Set Rng = ¤u§@ªí2.[A1]
   
5. .[A:E].Clear
   
6. With ¤u§@ªí1
   
7.    With .Range("A1").CurrentRegion
   
8.       .AutoFilter 4, ComboBox1
   
9.       .SpecialCells(xlCellTypeVisible).Copy Rng
   
10.       .AutoFilter
    
11.    End With
    
12. End With
    
13. mystr = "=COUNTIF(C5,RC9)/(COUNTA(C7)-1)"
    
14. For Each a In .Range(.[B2], .[B1].End(xlDown))
    
15.     d(a & a.Offset(, 3)) = Array(a, "", a.Offset(, 3))
    
16. Next
    
17. .Range("G1").CurrentRegion.Offset(1).ClearContents
    
18. .[G2].Resize(d.Count, 3) = Application.Transpose(Application.Transpose(d.items))
    
19. .[J2].Resize(d.Count, 1).FormulaR1C1 = mystr
    
20. .[H2] = d.Count
    
21. End With
    
22. Unload Me
    
23. End Sub
    
24. 
    
25. Private Sub UserForm_Initialize()
    
26. Set d = CreateObject("Scripting.Dictionary")
    
27. With ¤u§@ªí1
    
28.    For Each a In .Range(.[D2], .[D2].End(xlDown))
    
29.       d(a.Text) = ""
    
30.    Next
    
31. End With
    
32. ComboBox1.List = d.keys
    
33. End Sub

½Æ»s¥N½X

¤W¶Ç¥Î.zip (28.34 KB)

¤U¸ü¦¸¼Æ: 28

2014-7-25 10:59

Hsieh

¦^´_ 6# Duck

1. Private Sub ComboBox1_Change()
   
2. Set d = CreateObject("Scripting.Dictionary")
   
3. Set d1 = CreateObject("Scripting.Dictionary")
   
4. Set d2 = CreateObject("Scripting.Dictionary")
   
5. Set d3 = CreateObject("Scripting.Dictionary")
   
6. d2("CHT_IDX") = "BÄ椣­«½Æ¼Æ" 'L:MªºÄæ¦ì¦WºÙ"
   
7. d3("CHT_IDX") = "BÄ椣­«½Æ¼Æ"
   
8. With ¤u§@ªí2
   
9. Set Rng = ¤u§@ªí2.[A1]
   
10. .[A:E].ClearContents '²M°£¤§«e¿z¿ïµ²ªG
    
11. With ¤u§@ªí1
    
12.    With .Range("A1").CurrentRegion
    
13.       .AutoFilter 4, ComboBox1 '¨Ì¾Ú¤U©Ô¿ï³æ¿z¿ï¸ê®Æ
    
14.       .SpecialCells(xlCellTypeVisible).Copy Rng '±N¿z¿ïµ²ªG½Æ»s¨ì²Ä¤G¤u§@ªí
    
15.       .AutoFilter '¨ú®ø¿z¿ï
    
16.    End With
    
17. End With
    
18. mystr = "=COUNTIF(C5,RC9)/(COUNTA(C7)-1)" 'JÄ椽¦¡
    
19. For Each a In .Range(.[B2], .[B1].End(xlDown)) 'BÄæ¸ê®Æ°µ°j°é
    
20.     d(a.Value) = "" 'Àx¦sDATESEQ¤£­«½Æ²M³æ
    
21.     d1(a.Offset(, 3).Value) = "" 'Àx¦sPRICE_NAME¤£­«½Æ²M³æ
    
22.     d3(a.Offset(, -1).Value) = _
    
23.     IIf(InStr(d3(a.Offset(, -1).Value), a) = 0, d3(a.Offset(, -1).Value) & ";" & a, d3(a.Offset(, -1).Value)) '¥HAÄ欰¯Á¤Þ¡A­Y¥¼§tBÄæ¦r¦ê¡A«h¥H¤À¸¹;³sµ²BÄæ¦r¦ê
    
24.     d2(a.Offset(, -1).Value) = UBound(Split(d3(a.Offset(, -1).Value), ";")) '¥H¤À¸¹¤Á³Î¦r¦ê¡A­pºâ¥X°}¦C¤¸¯À¼Æ¶q¡A§Y¬°¦PCHT_IDXªº¤£­«½ÆBÄæ¼Æ¶q
    
25. Next
    
26. .Range("G1").CurrentRegion.Offset(1).ClearContents
    
27. .[L:M].ClearContents '²M°£L:MÄæ
    
28. '¼g¤JG:MÄæ
    
29. .[G2].Resize(d.Count, 1) = Application.Transpose(d.Keys)
    
30. .[I2].Resize(d1.Count, 1) = Application.Transpose(d1.Keys)
    
31. .[L1].Resize(d3.Count, 1) = Application.Transpose(d3.Keys)
    
32. .[M1].Resize(d2.Count, 1) = Application.Transpose(d2.items)
    
33. .[J2].Resize(d1.Count, 1).FormulaR1C1 = mystr
    
34. .[H2] = d.Count
    
35. End With
    
36. Unload Me '¨ø¸üªí³æ
    
37. End Sub

½Æ»s¥N½X