Hsieh

¦^´_ 1# sammay

1. Private Sub CommandButton1_Click()
   
2. Dim Ar()
   
3. s = CDate(Val(ComboBox1) + 1911 & "/" & ComboBox2 & "/1"): s1 = CDate(Val(ComboBox3) + 1911 & "/" & ComboBox4 & "/1")
   
4. s1 = DateAdd("m", 1, s1) - 1
   
5. With Sheet1
   
6. For Each a In .Range(.[A4], .[A4].End(xlDown))
   
7. d = DateSerial(a + 1911, a.Offset(, 1), a.Offset(, 2))
   
8. If s <= d And s1 >= d Then
   
9. ReDim Preserve Ar(i)
   
10. Ar(i) = a.Resize(, 4).Value
    
11. i = i + 1
    
12. End If
    
13. Next
    
14. End With
    
15. With Sheet2
    
16. .Select
    
17. .Range(.[A4:D4], .[A4:D4].End(xlDown)) = ""
    
18. If i > 0 Then
    
19. .[A4].Resize(i, 4) = Application.Transpose(Application.Transpose(Ar))
    
20. Else
    
21. MsgBox "µL²Å¦X¸ê®Æ"
    
22. End If
    
23. End With
    
24. Unload Me
    
25. End Sub
    
26. 
    
27. Private Sub UserForm_Initialize()
    
28. Set d = CreateObject("Scripting.Dictionary")
    
29. With Sheet1
    
30. For Each a In .Range(.[A4], .[A4].End(xlDown))
    
31. d(a.Value) = ""
    
32. Next
    
33. End With
    
34. ComboBox1.List = d.keys
    
35. ComboBox2.List = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12)
    
36. ComboBox3.List = d.keys
    
37. ComboBox4.List = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12)
    
38. End Sub

½Æ»s¥N½X

Hsieh

¦^´_ 9# afu9240

1. Private Sub CommandButton4_Click() '¬d¸ß«ö¶s
   
2. Set d = CreateObject("Scripting.Dictionary")
   
3. s = DateValue(ComboBox2 & "/" & ComboBox3 & "/1")
   
4. x = DateAdd("m", 1, DateValue(ComboBox5 & "/" & ComboBox4 & "/1")) - 1
   
5. With ¤u§@ªí1
   
6. For Each a In .Range(.[A2], .[A2].End(xlDown))
   
7.    If a >= s And a <= x Then
   
8.       d(a.Offset(, 2).Value) = d(a.Offset(, 2).Value) + a.Offset(, 1)
   
9.    End If
   
10. Next
    
11. End With
    
12. With Sheets("Á`ªí")
    
13.   For Each a In .[C3:C13]
    
14.      a.Offset(, 1) = d(a.Value)
    
15.   Next
    
16. End With
    
17. End Sub

½Æ»s¥N½X

½Æ¥» ¦p¦ó±N¸ê®Æ¶×¤JÁ`ªí.zip (33.03 KB)

¤U¸ü¦¸¼Æ: 10

2018-2-27 17:08

Hsieh

¦^´_ 11# afu9240
³o¬O³Ð«Ø¦r¨åª«¥óªº·N«ä
´N¬O±N¸ê®Æ¥HÃöÁä¦r¦s©ñ¤º®e
°ò¥»»yªk
object.add key,item
object¬°¦r¨åª«¥ó
add¤èªk¼W¥[¶µ¥Ø
key¬°ÃöÁä¯Á¤Þ¡A¥Hadd¤èªk¥[¤J¶µ¥Ø®É¡A­Y¯Á¤Þ­È­«½Æ«h·|²£¥Í¿ù»~
item¬°¹ïÀ³key¯Á¤Þ­È¤§¤º®e
©Ò¥H¥Î¨Æ¥Ñ°µ¬°¯Á¤Þ­È¡A¹ïÀ³­È¬°¥[Á`ª÷ÃB¥ý¦s¦b¦r¨åª«¥ó¤¤
¦A¥ÑÁ`ªí¨Æ¥ÑÄæ¦ì¹ïÀ³¨ú¥X¦r¨å¤º®e¶ñ¤J

Hsieh

¦^´_ 13# afu9240
§A¤u§@ªí1ªºÄæ¦ì§ïÅÜ
Private Sub CommandButton4_Click() '¶×¤J­pºâ«ö¶s
Set d = CreateObject("Scripting.Dictionary")
s = DateValue(ComboBox2 & "/" & ComboBox3 & "/1")
x = DateAdd("m", 1, DateValue(ComboBox5 & "/" & ComboBox4 & "/1")) - 1
With ¤u§@ªí1
For Each a In .Range(.[A2], .[A2].End(xlDown))
   If a >= s And a <= x Then
      d(a.Offset(, 1).Value) = d(a.Offset(, 2).Value) + a.Offset(, 2)
   End If
Next
End With
With Sheets("Á`ªí")
  For Each a In .[C3:C13]
     a.Offset(, 1) = d(a.Value)
  Next
End With
End Sub

Hsieh

¦^´_ 15# afu9240

§Ú·s¼W¤@­Óªí³æ¸Õ§@¬yµ{¡A§A°Ñ¦Ò¬Ý¬Ý

    ¦U¶µ¥I´Ú.zip (47.28 KB)

¤U¸ü¦¸¼Æ: 7

2018-3-9 15:28