ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

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

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

½Ð°Ý¦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)

¦^´_ 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

TOP

¦^´_ 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. Private Sub UserForm_Initialize()
  27. Set d = CreateObject("Scripting.Dictionary")
  28. With Sheet1
  29. For Each a In .Range(.[A4], .[A4].End(xlDown))
  30. d(a.Value) = ""
  31. Next
  32. End With
  33. ComboBox1.List = d.keys
  34. ComboBox2.List = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12)
  35. ComboBox3.List = d.keys
  36. ComboBox4.List = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12)
  37. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

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

TOP

¥»©«³Ì«á¥Ñ sammay ©ó 2012-1-17 08:44 ½s¿è

¦^´_ 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

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2012-1-17 09:18 ½s¿è

¦^´_ 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¥¨¶°



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

TOP

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

TOP

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

TOP

¦^´_ 6# GBKEE

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

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2018-2-27 17:08 ½s¿è

¦^´_ 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)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

        ÀR«ä¦Û¦b : °ß¨ä´L­«¦Û¤vªº¤H¡A¤~§ó«i©óÁY¤p¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD