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

ÀH®É·s¼WÄæ¦ì°ÝÃD

ÀH®É·s¼WÄæ¦ì°ÝÃD

·Q½Ð°Ý¦U¦ì°ª¤â­Ì


¤p§Ì¤µ¤Ñ¤À§O¦bA1 B1 C1 D1 E1 F1 Äæ¦ì ¤À§O¥´¤W¤F¨C­Ó¤ëªº²Ä¤@¤Ñ

¤]´N¬O¤@¤ë¤@¤é ¦Ü ¤»¤ë¤@¤é

¤p§Ì·Q»s§@¤@­Ó¿é¤J¼Æ¦r¨Ã¥B«ö¤U«ö¶s«á  ´N¯à°÷¥H¿é¤Jªº¼Æ¦r¨Ó¤À³Î¥X«e­±´X­Ó¤ë ¨C¶gªº¬P´Á¤é

¥Î»¡ªº¥i¯à«ÜÃø²z¸Ñ Á|¨Ò¤@¤U

        A       B      C       D        E      F
1     1/1   2/1   3/1   4/1    5/1   6/1

°²³]¤µ¤Ñ¿é¤J¼Æ¦r :  2
¥Nªí«e­±¨â­Ó¤ë³£¥²¶·Åã¥Ü¥X¤@¤ë¤Î¤G¤ëªº¨C­Ó¬P´Á¤é ¤é´Á
¥H2012¦~¨Ó»¡
´NÅܦ¨³o¼Ë
        A         B       C          D           E           F         G        H          I           J         K         L        M       N      
1    1/1    1/8     1/15    1/22     1/29     2/1     2/4     2/11    2/18   2/25    3/1     4/1     5/1    6/1

¥H¦¹Ãþ±À
­Y¿é¤Jªº¼Æ¦r¬O3  ¨º´NÅã¥Ü¥X«e­±¤T­Ó¤ë¨C­Ó¤ëªº¬P´Á¤éªº¤é´Á

·Q½Ð°Ý¦U¦ì°ª¤â  ¥Ñ©ó¿é¤Jªº¼Æ¦r¤£¤@©w  ¨C¦~¨C¶gªº¬P´Á¤é¤]³£¤£¦P
½Ð±Ð¸Ó¦p¦ó¼g¥X¥¨¶°>"< §xÂZ«Ü¤[¤F ÁٽЦU¦ì¸Ñµª!!!!
WOW

¦^´_ 1# g93353

¼Ó¥D~ ³o­Ó¬O¥i¥H³Q¨Ï¥Îªº~ ¥u¬O¥¦ªº¨¾§b~ ¨S¦³¼gªº«Ü¦n~
¥i¥H¸Õ¬Ý¬Ý~ À³¸Ó¥i¥H²Å¦X§A­nªºµ²ªG~
  1. Sub EX()
  2. A = InputBox("½Ð¿é¤J¦~¥÷")
  3. B = InputBox("½Ð¿é¤J¤ë¥÷")

  4. If Len(A) = 4 And (Len(B) = 1 Or Len(B) = 2) Then
  5. A1 = DateValue(A & "¦~" & "1¤ë" & "1¤é")
  6. A2 = DateValue(A & "¦~" & "2¤ë" & "1¤é")
  7. A3 = DateValue(A & "¦~" & "3¤ë" & "1¤é")
  8. A4 = DateValue(A & "¦~" & "4¤ë" & "1¤é")
  9. A5 = DateValue(A & "¦~" & "5¤ë" & "1¤é")
  10. A6 = DateValue(A & "¦~" & "6¤ë" & "1¤é")
  11. I = 0
  12. Sheet1.Rows("1:1").ClearContents
  13. Select Case B

  14. Case 1
  15.      Do Until Month(A1 + I) > 1
  16.         If Weekday(A1 + I, 2) = 7 Then
  17.            If Sheet1.Range("A1") = "" Then
  18.               Sheet1.Range("A1") = A1 + I
  19.            Else
  20.               Sheet1.Range("IV1").End(xlToLeft).Offset(0, 1) = A1 + I
  21.            End If
  22.         End If
  23.      I = I + 1
  24.      Loop
  25.      Sheet1.Range("IV1").End(xlToLeft).Offset(0, 1) = A2
  26.      Sheet1.Range("IV1").End(xlToLeft).Offset(0, 1) = A3
  27.      Sheet1.Range("IV1").End(xlToLeft).Offset(0, 1) = A4
  28.      Sheet1.Range("IV1").End(xlToLeft).Offset(0, 1) = A5
  29.      Sheet1.Range("IV1").End(xlToLeft).Offset(0, 1) = A6
  30. Case 2
  31.      Do Until Month(A1 + I) > 2
  32.         If Weekday(A1 + I, 2) = 7 Then
  33.            If Sheet1.Range("A1") = "" Then
  34.               Sheet1.Range("A1") = A1 + I
  35.            Else
  36.               Sheet1.Range("IV1").End(xlToLeft).Offset(0, 1) = A1 + I
  37.            End If
  38.         End If
  39.      I = I + 1
  40.      Loop
  41.      Sheet1.Range("IV1").End(xlToLeft).Offset(0, 1) = A3
  42.      Sheet1.Range("IV1").End(xlToLeft).Offset(0, 1) = A4
  43.      Sheet1.Range("IV1").End(xlToLeft).Offset(0, 1) = A5
  44.      Sheet1.Range("IV1").End(xlToLeft).Offset(0, 1) = A6
  45. Case 3
  46.      Do Until Month(A1 + I) > 3
  47.         If Weekday(A1 + I, 2) = 7 Then
  48.            If Sheet1.Range("A1") = "" Then
  49.               Sheet1.Range("A1") = A1 + I
  50.            Else
  51.               Sheet1.Range("IV1").End(xlToLeft).Offset(0, 1) = A1 + I
  52.            End If
  53.         End If
  54.      I = I + 1
  55.      Loop
  56.      Sheet1.Range("IV1").End(xlToLeft).Offset(0, 1) = A4
  57.      Sheet1.Range("IV1").End(xlToLeft).Offset(0, 1) = A5
  58.      Sheet1.Range("IV1").End(xlToLeft).Offset(0, 1) = A6
  59. Case 4
  60.      Do Until Month(A1 + I) > 4
  61.         If Weekday(A1 + I, 2) = 7 Then
  62.            If Sheet1.Range("A1") = "" Then
  63.               Sheet1.Range("A1") = A1 + I
  64.            Else
  65.               Sheet1.Range("IV1").End(xlToLeft).Offset(0, 1) = A1 + I
  66.            End If
  67.         End If
  68.      I = I + 1
  69.      Loop
  70.      Sheet1.Range("IV1").End(xlToLeft).Offset(0, 1) = A5
  71.      Sheet1.Range("IV1").End(xlToLeft).Offset(0, 1) = A6
  72. Case 5
  73.      Do Until Month(A1 + I) > 5
  74.         If Weekday(A1 + I, 2) = 7 Then
  75.            If Sheet1.Range("A1") = "" Then
  76.               Sheet1.Range("A1") = A1 + I
  77.            Else
  78.               Sheet1.Range("IV1").End(xlToLeft).Offset(0, 1) = A1 + I
  79.            End If
  80.         End If
  81.      I = I + 1
  82.      Loop
  83.      Sheet1.Range("IV1").End(xlToLeft).Offset(0, 1) = A6
  84. Case 6
  85.      Do Until Month(A1 + I) > 6
  86.         If Weekday(A1 + I, 2) = 7 Then
  87.            If Sheet1.Range("A1") = "" Then
  88.               Sheet1.Range("A1") = A1 + I
  89.            Else
  90.               Sheet1.Range("IV1").End(xlToLeft).Offset(0, 1) = A1 + I
  91.            End If
  92.         End If
  93.      I = I + 1
  94.      Loop

  95. End Select

  96. End If


  97. End Sub
½Æ»s¥N½X
¾Ç²ß¤~¯à´£¤É¦Û¤v

TOP

¥ý¨Ó¬ã¨s¬Ý¬Ý  ÁÂÁÂhugh0620¤j¤j!!!!!!!!!
WOW

TOP

¦^´_ 3# g93353

¦³¦Aµy·L¬Ý¹L¼Ó¥D­n¨D~ ¦n¹³¬O­n§â¨C¤ëªº1¸¹±a¥X¨Ó~
¥i¨Ï¥Î¤U¦Cªº¤è¦¡~
­Y¤£¥Î±a¥X¨C¤ë1¸¹ªº¸ê®Æ~ ´N±Ä¥Î¤W­±²Ä¤@­Ó¼Ò²Õ~
  1. Sub EX()
  2. A = InputBox("½Ð¿é¤J¦~¥÷")
  3. B = InputBox("½Ð¿é¤J¤ë¥÷")

  4. If Len(A) = 4 And (Len(B) = 1 Or Len(B) = 2) Then
  5. A1 = DateValue(A & "¦~" & "1¤ë" & "1¤é")
  6. A2 = DateValue(A & "¦~" & "2¤ë" & "1¤é")
  7. A3 = DateValue(A & "¦~" & "3¤ë" & "1¤é")
  8. A4 = DateValue(A & "¦~" & "4¤ë" & "1¤é")
  9. A5 = DateValue(A & "¦~" & "5¤ë" & "1¤é")
  10. A6 = DateValue(A & "¦~" & "6¤ë" & "1¤é")
  11. I = 0
  12. Sheet1.Rows("1:1").ClearContents
  13. Select Case B

  14. Case 1
  15.      Do Until Month(A1 + I) > 1
  16.         If Weekday(A1 + I, 2) = 7 Or (Month(A1 + I) = 1 And Day(A1 + I) = 1) Then
  17.            If Sheet1.Range("A1") = "" Then
  18.               Sheet1.Range("A1") = A1 + I
  19.            Else
  20.               Sheet1.Range("IV1").End(xlToLeft).Offset(0, 1) = A1 + I
  21.            End If
  22.         End If
  23.      I = I + 1
  24.      Loop
  25.      Sheet1.Range("IV1").End(xlToLeft).Offset(0, 1) = A2
  26.      Sheet1.Range("IV1").End(xlToLeft).Offset(0, 1) = A3
  27.      Sheet1.Range("IV1").End(xlToLeft).Offset(0, 1) = A4
  28.      Sheet1.Range("IV1").End(xlToLeft).Offset(0, 1) = A5
  29.      Sheet1.Range("IV1").End(xlToLeft).Offset(0, 1) = A6
  30. Case 2
  31.      Do Until Month(A1 + I) > 2
  32.         If Weekday(A1 + I, 2) = 7 Or (Month(A1 + I) = 2 And Day(A1 + I) = 1) Then
  33.            If Sheet1.Range("A1") = "" Then
  34.               Sheet1.Range("A1") = A1 + I
  35.            Else
  36.               Sheet1.Range("IV1").End(xlToLeft).Offset(0, 1) = A1 + I
  37.            End If
  38.         End If
  39.      I = I + 1
  40.      Loop
  41.      Sheet1.Range("IV1").End(xlToLeft).Offset(0, 1) = A3
  42.      Sheet1.Range("IV1").End(xlToLeft).Offset(0, 1) = A4
  43.      Sheet1.Range("IV1").End(xlToLeft).Offset(0, 1) = A5
  44.      Sheet1.Range("IV1").End(xlToLeft).Offset(0, 1) = A6
  45. Case 3
  46.      Do Until Month(A1 + I) > 3
  47.         If Weekday(A1 + I, 2) = 7 Or (Month(A1 + I) = 3 And Day(A1 + I) = 1) Then
  48.            If Sheet1.Range("A1") = "" Then
  49.               Sheet1.Range("A1") = A1 + I
  50.            Else
  51.               Sheet1.Range("IV1").End(xlToLeft).Offset(0, 1) = A1 + I
  52.            End If
  53.         End If
  54.      I = I + 1
  55.      Loop
  56.      Sheet1.Range("IV1").End(xlToLeft).Offset(0, 1) = A4
  57.      Sheet1.Range("IV1").End(xlToLeft).Offset(0, 1) = A5
  58.      Sheet1.Range("IV1").End(xlToLeft).Offset(0, 1) = A6
  59. Case 4
  60.      Do Until Month(A1 + I) > 4
  61.         If Weekday(A1 + I, 2) = 7 Or (Month(A1 + I) = 4 And Day(A1 + I) = 1) Then
  62.            If Sheet1.Range("A1") = "" Then
  63.               Sheet1.Range("A1") = A1 + I
  64.            Else
  65.               Sheet1.Range("IV1").End(xlToLeft).Offset(0, 1) = A1 + I
  66.            End If
  67.         End If
  68.      I = I + 1
  69.      Loop
  70.      Sheet1.Range("IV1").End(xlToLeft).Offset(0, 1) = A5
  71.      Sheet1.Range("IV1").End(xlToLeft).Offset(0, 1) = A6
  72. Case 5
  73.      Do Until Month(A1 + I) > 5
  74.         If Weekday(A1 + I, 2) = 7 Or (Month(A1 + I) = 5 And Day(A1 + I) = 1) Then
  75.            If Sheet1.Range("A1") = "" Then
  76.               Sheet1.Range("A1") = A1 + I
  77.            Else
  78.               Sheet1.Range("IV1").End(xlToLeft).Offset(0, 1) = A1 + I
  79.            End If
  80.         End If
  81.      I = I + 1
  82.      Loop
  83.      Sheet1.Range("IV1").End(xlToLeft).Offset(0, 1) = A6
  84. Case 6
  85.      Do Until Month(A1 + I) > 6
  86.         If Weekday(A1 + I, 2) = 7 Or (Month(A1 + I) = 6 And Day(A1 + I) = 1) Then
  87.            If Sheet1.Range("A1") = "" Then
  88.               Sheet1.Range("A1") = A1 + I
  89.            Else
  90.               Sheet1.Range("IV1").End(xlToLeft).Offset(0, 1) = A1 + I
  91.            End If
  92.         End If
  93.      I = I + 1
  94.      Loop

  95. End Select

  96. End If


  97. End Sub
½Æ»s¥N½X
¾Ç²ß¤~¯à´£¤É¦Û¤v

TOP

·PÁÂhugh0620 ªº¦^ÂР ¤p§Ì¸Õ¹L«áµo²{ ¨C­Ó¤ëªº¤@¸¹¦³¨Ç¤ë¥÷¤£·|Åã¥Ü¥X¨Ó»¡  
¹³2011¦~´N¤£¦æ¤F>"<
WOW

TOP

¥»©«³Ì«á¥Ñ register313 ©ó 2012-3-2 15:10 ½s¿è
  1. Sub YY()
  2. Rows("2") = ""
  3. MX = InputBox("½Ð¿é¤J¤ë¥÷¼Æ")
  4. For M = 1 To MX
  5.   First = Cells(1, M) + (7 - Weekday(Cells(1, M), 2))
  6.   [A2].Offset(0, C) = First
  7.   C = C + 1
  8.   Do While Month(First + 7) = Month(Cells(1, M))
  9.      [A2].Offset(0, C) = First + 7
  10.      First = First + 7
  11.      C = C + 1
  12.   Loop
  13. Next M
  14. If MX <> 6 Then
  15.    Cells(1, MX + 1).Resize(1, 6 - MX).Copy [A2].Offset(0, C)
  16. End If
  17. Range([A2], [IV2].End(xlToLeft)).NumberFormatLocal = "m/d;@"
  18. End Sub
½Æ»s¥N½X

TOP

¥»©«³Ì«á¥Ñ hugh0620 ©ó 2012-3-2 13:42 ½s¿è

¦^´_ 5# g93353

­Y¼Ó¥D±zªº¬O¸Ó¦~«×¾ã¦~¥÷ªº¸ê®Æ~  (¶È­­¤@­Ó¦~«×)
³o¼Ë¤U­±ªº¼gªk~ ¥i¥H±o¨ì§A»Ý­nªºµ²ªG~

­Y±z»Ý­nªº³]©w¬Y¤@­Ó°Ï¶¡ªº¸ê®Æ~ ¼gªk¤S¤£¤@¼Ë¤F~
case by case
  1. Sub EX()
  2. Dim B As Integer
  3. A = InputBox("½Ð¿é¤J¦~¥÷")
  4. B = InputBox("½Ð¿é¤J¤ë¥÷")

  5. If Len(A) = 4 And (Len(B) = 1 Or Len(B) = 2) Then
  6. A1 = DateValue(A & "¦~" & "1¤ë" & "1¤é")
  7. I = 0
  8. Sheet1.Rows("1:1").ClearContents
  9.     Do Until Month(A1 + I) > B
  10.        If Weekday(A1 + I, 2) = 7 Or (Month(A1 + I) = 1 And Day(A1 + I) = 1) Then
  11.           If Sheet1.Range("A1") = "" Then
  12.              Sheet1.Range("A1") = A1 + I
  13.           Else
  14.              Sheet1.Range("IV1").End(xlToLeft).Offset(0, 1) = A1 + I
  15.           End If
  16.        End If
  17.     I = I + 1
  18.     Loop
  19.     If B <> 12 Then
  20.        For I = B + 1 To 12
  21.        Sheet1.Range("IV1").End(xlToLeft).Offset(0, 1) = DateValue(A & "¦~" & I & "¤ë" & "1¤é")
  22.     Next
  23.     End If
  24. End If

  25. End Sub
½Æ»s¥N½X
¾Ç²ß¤~¯à´£¤É¦Û¤v

TOP

ÁÂÁÂ register313 ¸ò hugh0620 ¤j¤j
·Q½Ð°Ý¤@¤U hugh0620 ¤j¤j

¤p§Ì¤w¸g¦Û¦æ§â¨C¤ëªº²Ä¤@¤Ñ³£¥ý¥´¦bÄæ¦ì¤W¤F(¦³¥i¯à¸ó¦~¥÷ EX : ±q2011 ¨ì2012)
¨º³o¼Ë¼gªk­n¦p¦ó§@­×§ï?
WOW

TOP

¦^´_ 8# g93353
  1. Sub YY()
  2. Rows("2") = ""
  3. MX = InputBox("½Ð¿é¤J¤ë¥÷¼Æ")
  4. For M = 1 To MX
  5.   First = Cells(1, M) + (7 - Weekday(Cells(1, M), 2))
  6.   [A2].Offset(0, C) = First
  7.   C = C + 1
  8.   Do While Month(First + 7) = Month(Cells(1, M))
  9.      [A2].Offset(0, C) = First + 7
  10.      First = First + 7
  11.      C = C + 1
  12.   Loop
  13. Next M
  14. Cells(1, MX + 1).Resize(1, 6 - MX).Copy [A2].Offset(0, C)
  15. Range([A2], [IV2].End(xlToLeft)).NumberFormatLocal = "m/d;@"
  16. End Sub
½Æ»s¥N½X

TOP

¥»©«³Ì«á¥Ñ g93353 ©ó 2012-3-2 16:01 ½s¿è

ÁÂÁÂregister313  
±z¸ò hugh0620 ¤j¤j¼gªk³£¬O¿é¤J¦~¤ë¥÷«á·|¦Û°Ê¶]¥X
¤p§Ì¤w¸g¤À§O§â¨C­Ó¤ë1¸¹³£¦U§O¼g¦bA1 ¨ì F1(°²³]¬O1¨ì6¤ë)
¦Ó¿é¤Jªº¼Æ¦r¬O«ü«e­±(¼Æ¦r)­Ó¤ë³£¥²¶·Åã¥Ü¥X¬P´Á¤é(±q¨C­Ó¤ëªº1¸¹«á­±¨Ì§ÇÅã¥Ü¥X)
¤j¤j­Ìªº§@ªk³£³¡¤£·|¦³·s¼WÄæ¦ìªº°ÝÃD
¤p§Ì¤w¥ý±N¨C­Ó¤ëªº²Ä¤@¤Ñ¥ý¥´¦n
³o¼ËÄæ¦ìªº·s¼W´N·|¤£©T©w
©Ò¥H³Â·Ð¦A½Ð±Ð¦U¦ì¤F
¥ý«e±Ô­z¦³ÂIÁU½k  ¤£¦n·N«ä¤F>"<
WOW

TOP

        ÀR«ä¦Û¦b : ¤H¥Í¤£¤@©w²y²y¬O¦n²y¡A¦ý¬O¦³¾ú½mªº±j¥´ªÌ¡AÀH®É³£¥i¥H´§´Î¡C
ªð¦^¦Cªí ¤W¤@¥DÃD