[µo°Ý] ¤é´Á°Ï¶¡¬d¸ß(¸ó¦~¤ë)

sammay

½Ð°Ý¦U¦ì«e½ú¡G
¤é´Á°Ï¶¡¬d¸ßÀ³¦p¦ó¹ê°µ©O¡H
¨Ò¦p¡G¦buserform1¤¤«ö¤U¬d¸ß«ö¶s¡A¦A(©óuserform3¤¤)¿ï¾Ü100¦~12¤ë¨ì101¦~1¤ë«ö¤U½T©w«ö¶s«á¡A±Nµ²ªG¿é¥X¨ì¬d¸ß©ú²Ó¡H
sh-test1-20120116.rar (36.42 KB)

sh-test1-20120116
¤U¸ü¦¸¼Æ: 32

2012-1-16 08:42

GBKEE

¦^´_ 1# sammay
UserForm3

1. Dim ¤é´Á()
   
2. Private Sub UserForm_Initialize()
   
3.     CommandButton1.Enabled = False                            '½T©w¶s±±¨î¶µ: ¤£¥i¥H¨Ï¥Î
   
4.     ¤é´Á = Array(ComboBox1, ComboBox2, ComboBox3, ComboBox4)  '±N¦~¤ëªº¿é¤J ¸m¤J¦b°}¦C
   
5.     ComboBox1.RowSource = "¤U©Ô¿ï³æ!c2:c11"
   
6.     ComboBox2.RowSource = "¤U©Ô¿ï³æ!d2:d13"
   
7.     ComboBox3.RowSource = "¤U©Ô¿ï³æ!c2:c11"
   
8.     ComboBox4.RowSource = "¤U©Ô¿ï³æ!d2:d13"
   
9. End Sub
   
10. Private Sub CommandButton1_Click()
    
11.     Dim Data As Range, Rng As Range, Day1 As Date, Day2 As Date, Msg As String, E As Range
    
12.     Set Data = Sheets("Á`ªí").Range("A3").CurrentRegion
    
13.     'Range("A3").CurrentRegion :   Á`ªíªº¸ê®Æ A2:D2 ,EÄæ ½Ð¤£­n¦³¸ê®Æ¿é¤J
    
14.     If Data.Rows.Count = 1 Then         '¥u¦³Äæ¦ì
    
15.         MsgBox "Á`ªí:  ¨S¦³¸ê®Æ !!!"
    
16.         Unload Me
    
17.         Exit Sub
    
18.     End If
    
19.     Day1 = DateSerial(¤é´Á(0), ¤é´Á(1), 1)                   'Âà¤J¤é´Á
    
20.     Day2 = DateSerial(¤é´Á(2), ¤é´Á(3), 1)
    
21.     For Each E In Data.Columns(1).Offset(1).Cells             '[A4]->
    
22.         If DateSerial(E, E.Cells(1, 2), 1) >= Day1 And DateSerial(E, E.Cells(1, 2), 1) <= Day2 Then
    
23.             If Rng Is Nothing Then                     'ªì¦¸
    
24.                 Set Rng = E.Resize(1, 4)
    
25.             Else                                        '²Ä¤G¦¸¥H«á
    
26.                 Set Rng = Union(Rng, E.Resize(1, 4))
    
27.             End If
    
28.         End If
    
29.     Next
    
30.     Msg = ¤é´Á(0) & "/" & ¤é´Á(1) & " - " & ¤é´Á(2) & "/" & ¤é´Á(3)
    
31.     If Rng Is Nothing Then
    
32.         MsgBox Msg & "§ä¤£¨ì  ¸ê®Æ"
    
33.     Else
    
34.         Rng.Copy Sheets("¬d¸ß©ú²Ó").Cells(Rows.Count, 1).End(xlUp).Offset(1)
    
35.         'Rng ½Æ»s¨ì "¬d¸ß©ú²Ó"AÄæ ³Ì«á¤@µ§¦³¸ê®Æªº¤U¤@®æ  Offset(1)
    
36.         MsgBox Msg & " §ä¨ì  " & Rng.Count / Data.Columns.Count & " µ§¸ê®Æ"
    
37.     End If
    
38.         Unload Me
    
39. End Sub
    
40. Private Sub ComboBox1_Change()
    
41.     Check_¤é´Á
    
42. End Sub
    
43. Private Sub ComboBox2_Change()
    
44.      Check_¤é´Á
    
45. End Sub
    
46. Private Sub ComboBox3_Change()
    
47.      Check_¤é´Á
    
48. End Sub
    
49. Private Sub ComboBox4_Change()
    
50.     Check_¤é´Á
    
51. End Sub
    
52. Private Sub Check_¤é´Á()      '§P§O ¦~¤ë¿é¤J
    
53.     Dim Msg As Boolean, E As Variant
    
54.     For Each E In ¤é´Á                          '¨Ì§Ç³B¸Ì: ¦~¤ëªº¿é¤J
    
55.         If Not IsNumeric(E) Then                '¤£¬O¼Æ¦r
    
56.             CommandButton1.Enabled = False      '½T©w¶s±±¨î¶µ: ¤£¥i¥H¨Ï¥Î
    
57.             Msg = True                          'Msg³]©w¬° True
    
58.             Exit For
    
59.         End If
    
60.     Next
    
61.     If Msg = False Then                         '¤é´Á¬Ò¬°¼Æ¦r
    
62.         If DateSerial(¤é´Á(0), ¤é´Á(1), 1) <= DateSerial(¤é´Á(2), ¤é´Á(3), 1) Then
    
63.           'DateSerial(¦~,¤ë, 1)
    
64.             CommandButton1.Enabled = True           '½T©w¶s±±¨î¶µ: ¥i¥H¨Ï¥Î
    
65.         Else
    
66.             CommandButton1.Enabled = False          '½T©w¶s±±¨î¶µ: ¤£¥i¥H¨Ï¥Î
    
67.         End If
    
68.     End If
    
69. End Sub

½Æ»s¥N½X

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

sammay

¦^´_ 2# GBKEE
·PÁÂG¤j¦^ÂСA¤w¸g¥i¥H°õ¦æ¡A¨ä¤¤µ{¦¡½X
Range("A3").CurrentRegion :   Á`ªíªº¸ê®Æ A2:D2 ,EÄæ ½Ð¤£­n¦³¸ê®Æ¿é¤J
¤£¤Ó©ú¥Õ¡H

sammay

¦^´_ 3# Hsieh
·PÁÂH¤j¦^ÂСAµ{¦¡½X
s = CDate(Val(ComboBox1) + 1911 & "/" & ComboBox2 & "/1"): s1 = CDate(Val(ComboBox3) + 1911 & "/" & ComboBox4 & "/1")
¨ä¤¤"/1"¬°¦ó·N©O¡HÁÙ¦³¡Ï1911¥Î·N©O¡H

GBKEE

¦^´_ 4# sammay
CurrentRegion ÄÝ©Ê   ¸Óª«¥ó¥Nªí¥Ø«eªº°Ï°ì¡C¥Ø«e°Ï°ì¬O«ü¥H¥ô·NªÅ¥Õ¦C¤ÎªÅ¥ÕÄ檺²Õ¦X¬°Ãä¬Éªº½d³ò¡C

1. Sub Ex()
   
2.     With ActiveCell.CurrentRegion.Borders
   
3.         .LineStyle = 1
   
4.         .ColorIndex = 7
   
5.     End With
   
6. End Sub

½Æ»s¥N½X

¦p¹Ï ªº½d³ò¤¤¿ï¨ú³æ¤@¦³¸ê®ÆªºªS¦s®æ °õ¦æEx¥¨¶°

¤U¸ü (21.51 KB)

2012-1-17 09:18

¨ä¤¤"/1"¬°¦ó·N©O¡HÁÙ¦³¡Ï1911¥Î·N©O¡H
/1 :            ¨Ï¨ä¤é´Á¬°¨C¤ëªº1¤é
¡Ï1911 :  ¦]§Aªº¦~«×¬O¥Á°ê, +1911¦¨¬°¦è¤¸¦~«×

sammay

¦^´_ 6# GBKEE
·PÁÂG¤j¦^ÂСAÁA¸Ñ¡C
·q¯¬ ¦U¦ì¥ý¶i«e½ú­Ì ·s¦~§Ö¼Ö ¸U¨Æ¦p·N ¤ß·Q¨Æ¦¨

JOYARK

ÁÂÁ´£¨Ñ¾Ç²ß«D±`¹ê¥Î

afu9240

¦^´_ 6# GBKEE

G¤j ±z¦n
  ¦pªG¤é´Á®æ¦¡¬°§Úªþ¥ó³oºØÃþ«¬­n¦p¦ó¨ú¤é´Á½d³ò¥X¨Ó,¦A¶K¦b·sªº¤u§@ªí©O!!! ¬d¸ß¤é´Á°Ï¶¡¸ê®Æ.zip (31.65 KB)

¤U¸ü¦¸¼Æ: 5

2018-2-27 16:20

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