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

[µo°Ý] ½Ð°Ý ARRAY½d³ò §ï¦¨ °ÊºA «ü©w ½d³ò°ÝÃD ½Ð«e½ú­Ì«ü±Ð

[µo°Ý] ½Ð°Ý ARRAY½d³ò §ï¦¨ °ÊºA «ü©w ½d³ò°ÝÃD ½Ð«e½ú­Ì«ü±Ð

Private Sub CommandButton2_Click()
'3­Ó¤ë
If TextBox2.Value = 1 Then A = Array(3, 5, 7, 15, 17, 19)
If TextBox2.Value = 2 Then A = Array(5, 7, 9, 17, 19, 21)
If TextBox2.Value = 3 Then A = Array(7, 9, 11, 19, 21, 23)
If TextBox2.Value = 4 Then A = Array(9, 11, 13, 21, 23, 25)
If TextBox2.Value = 5 Then A = Array(11, 13, 15, 23, 25)
If TextBox2.Value = 6 Then A = Array(13, 15, 17, 25)
If TextBox2.Value = 7 Then A = Array(15, 17, 19)
If TextBox2.Value = 8 Then A = Array(17, 19, 21)
If TextBox2.Value = 9 Then A = Array(19, 21, 23)
If TextBox2.Value = 10 Then A = Array(21, 23, 25)
If TextBox2.Value = 11 Then A = Array(23, 25)
If TextBox2.Value = 12 Then A = Array(25)
If TextBox2.Value = "" Then A = Array(3, 5, 7, 15, 17, 19)

For I = LBound(A) To UBound(A)
    For J = 4 To Cells(3, 4).End(xlToRight).Column
        If Cells(A(I), J) = "¤é¯Z" Then Cells(A(I), J) = "©]¯Z"
        If Cells(A(I), J) <> "" And Cells(A(I), J) = "©]¯Z" Then
           Cells(A(I), J).Font.Color = RGB(Cells(1, 26), Cells(1, 27), Cells(1, 28))
           Cells(A(I), J).Interior.Color = RGB(Cells(1, 32), Cells(1, 33), Cells(1, 34))
        End If
    Next J
Next I
Erase A

UserForm3.Hide
TextBox2.Value = ""
End Sub

½Ð°Ý«e½ú­Ì ¥H¤U³o¬q¸Ó¦p¦ó§ï¼g ¤~¯à°÷§ï¦¨°ÊºAªº³W«ß ¦p¦ó¥i¥HÁY´îµ{¦¡½X
ÁÙ¦³¤@­Ó©µ¦ù°ÝÃD ·í©µ¦ù¨ì³Ì«á¤@­Ó¤ë®É¤£º¡3­Ó¤ë ¸Ó¦p¦ó©µ¦ù¦Ü¤U¤@¦~ªº²Ä¤@­Ó¤ë
½Ð«e½ú­Ì«ü±Ð µ{¦¡¯S§OÃlªø ¬Ý°_¨Ó¥i¯à·|¦³¨Ç¤£¤è«K ¤£¦n·N«ä ^^"

If TextBox2.Value = 1 Then A = Array(3, 5, 7, 15, 17, 19)
If TextBox2.Value = 2 Then A = Array(5, 7, 9, 17, 19, 21)
If TextBox2.Value = 3 Then A = Array(7, 9, 11, 19, 21, 23)
If TextBox2.Value = 4 Then A = Array(9, 11, 13, 21, 23, 25)
If TextBox2.Value = 5 Then A = Array(11, 13, 15, 23, 25)
If TextBox2.Value = 6 Then A = Array(13, 15, 17, 25)
If TextBox2.Value = 7 Then A = Array(15, 17, 19)
If TextBox2.Value = 8 Then A = Array(17, 19, 21)
If TextBox2.Value = 9 Then A = Array(19, 21, 23)
If TextBox2.Value = 10 Then A = Array(21, 23, 25)
If TextBox2.Value = 11 Then A = Array(23, 25)
If TextBox2.Value = 12 Then A = Array(25)
If TextBox2.Value = "" Then A = Array(3, 5, 7, 15, 17, 19)
·s¼W¸ê®Æ§¨.rar (109.11 KB)

´N¬O¤ë¥÷ªº³¡¤À  §ï¦¨¥i¥H¥Î¿é¤Jªí³æªº¤è¦¡ 2­Ó¤ë½ü¤@¦¸ ©ÎµÛ¬O 3­Ó¤ë½ü¤@¦¸

TOP

ÁÂÁ­㴣¤j¤j ©¹«á§Ú·|§ï±¼³o­ÓÃa²ßºDªº

TOP

ÁÂÁ n7822123 ¤j¤j  µ{¦¡¤Ö«Ü¦h¦æ¤F
·í©µ¦ù¨ì³Ì«á¤@­Ó¤ë®É¤£º¡3­Ó¤ë ¸Ó¦p¦ó©µ¦ù¦Ü¤U¤@¦~ªº²Ä¤@­Ó¤ë©O?

TOP

¦^­ã´£¤j¤j¨º­Ó§Ú¥u¬O·Q­n½m²ß¥Îªº¦]¬°§ÚÁÙ¬O·s¤â>"<    R1C4 ªº 2020 §ï¦¨2021 ¥i¥H©µ¦ùÅã¥Ü¤U¤@¦~¥÷
¯Z§O ½ü¯Z ªº³¡¤À ¬O¥i¥H©µ¦ù ¦ý¬O¨C­Ó¤ë¤Ñ¼Æ¤£¦P ­n°µ¨ì 2~3­Ó¤ëªº©µ¦ù´N¥d¦í¤F ^^"

TOP

ÁÂÁ n7822123 ¤j¤jªº«ü±Ð  §Ú·|¦n¦n¬ã¨s¾Ç²ß ÁÂÁ±z
­ì¥»·|³o¼Ë¼g¬O¦]¬° ¸ûª½Ä± ¦n²z¸Ñ ¦ý·|³y¦¨­pºâ©M®æ¦¡°ÝÃD
§Ú·|Å¥·Ç¤j¤jªº §ï±¼³o­ÓÃa²ßºDªº   
ªº½T¤é´ÁÂà¤å¦r·|³y¦¨«Ü¦h¤£¥²­nªº°ÝÃD  ¤]·|¦]¬°Âà¤å¦r ¦h¼g¤F«Ü¦h¦æ ¤p§Ì¨ü±Ð¤F

TOP

¥»©«³Ì«á¥Ñ °a¤ªºµ ©ó 2020-6-24 06:08 ½s¿è

n7822123¤j¤j §ÚÁÙ¬O¨S¿ìªk ¤£ª¾¹D¬°¬Æ»ò  ¤W±µ¤£¨ì¤U ¦³®É­Ô·|¶]¥¢±Ñ ¦pªG¥[©µ¿ð ¤SÅܱo«ÜºC
¤£¹L©µ¦ù¤U¤@¦~°ÝÃD ¸Ñ¨M¤F ¤w¸g¤£»Ý­n¥ý¦s¨ìÀx¦s®æ ³o¤èªk¬O¥i¥H¦Û°Ê©µ¦ù  ÁÂÁÂn7822123¤j¤j
Sub ¤é´Á½m²ß()
'Application.ScreenUpdating = False

Range(Cells(2, 1).End(xlToRight), Cells(2, 1).End(xlDown)).Clear

    S = 3
    E = 1
    For F = 1 To 12 '«Ø¥ß½d³ò
        For P = 1 To Day(DateSerial(Year(Now), F + 1, 0))
            Cells(S, E) = DateSerial(Year(Now), F, P)
            Cells(S - 1, E) = F & "¤ë" & P & "¤é" & WeekdayName(Weekday(P))
            E = E + 1
            If P = Day(DateSerial(Year(Now), F + 1, 0)) Then
            If F = 12 Then Exit For
               S = S + 2
               E = 1
            End If
        Next P
    Next F
   
    For E = ActiveWorkbook.Names.Count To 1 Step -1 '²M°£©w¸q¦WºÙ
       If ActiveWorkbook.Names(E).Name <> "«ü©w½d³ò" Then
          ActiveWorkbook.Names(E).Delete
       End If
    Next E
   
    Y = 65
    For i = 3 To Cells(3, 1).End(xlDown).Row Step 2 '©w¸q¦WºÙ
        ½d³ò¦WºÙ = Chr(Y)
        Names.Add Name:="²Ä" & ½d³ò¦WºÙ & "¶µ", RefersTo:=Range(Cells(i, 1), Cells(i, 1).End(xlToRight))
        Y = Y + 1
    Next i
    'Application.Wait Now + TimeValue("00:00:01")
   
    Set AWN = ActiveWorkbook.Names '¦X¨Ö
    For R = 1 To AWN.Count
       If R <> 1 Then
          K = Mid(AWN(R).RefersToR1C1Local, 2, Len(AWN(R))) & ","
       Else
          K = AWN(R).RefersToR1C1Local & ","
       End If
        u = u + K
    Next R
   
    Names.Add Name:="«ü©w½d³ò", RefersTo:=Mid(u, 1, Len(u) - 1)
   
    For E = ActiveWorkbook.Names.Count To 1 Step -1 '²M°£©w¸q¦WºÙ
       If ActiveWorkbook.Names(E).Name <> "«ü©w½d³ò" Then
          ActiveWorkbook.Names(E).Delete
       End If
    Next E

    For Each G In Range("«ü©w½d³ò")
        D = G.Offset
        Select Case DateAdd("d", -1, D) Mod 6 + 1
        Case 1 To 4
            G.Offset = "¤W¯Z"
            G.Offset.Font.Color = RGB(0, 0, 89)
            G.Interior.Color = RGB(150, 201, 123)
        Case 5 To 6
            G.Offset = "¥ð°²"
            G.Offset.Font.Color = RGB(114, 0, 55)
            G.Offset.Interior.Color = RGB(255, 255, 92)
        End Select
    Next G
   
'Application.ScreenUpdating = True
End Sub

TOP

¦pªG§â  G.Offset = "¤W¯Z" ©M G.Offset = "¥ð°²" Ãö±¼ ´N¤£·|¥X¿ù¤F ¦ý¬O ³o¼Ë´N¹F¤£¨ì¥Øªº¤F
¤£ª¾¹D¦³¨S¦³§Oªº¤è¦¡  
Select Case DateAdd("d", -1, D) Mod 6 + 1 ³o¬q¦³°ÝÃD  µ{¦¡¤w¸g¶]§¹¤F ¦ý¬OÁÙ¬O·|°±¦b³o¸Ì
»¡«¬ºA¤£²Å ¦ý§Úµ{¦¡¤w¸g¶]§¹¤F ¤£ª¾¹D¬°¬Æ»ò...
    For Each G In Range("«ü©w½d³ò")
        D = G.Offset
        Select Case DateAdd("d", -1, D) Mod 6 + 1
        Case 1 To 4
'            G.Offset = "¤W¯Z"
            G.Offset.Font.Color = RGB(0, 0, 89)
            G.Interior.Color = RGB(150, 201, 123)
        Case 5 To 6
'            G.Offset = "¥ð°²"
            G.Offset.Font.Color = RGB(114, 0, 55)
            G.Offset.Interior.Color = RGB(255, 255, 92)
        End Select
    Next G

TOP

­è¤~¦b³Ì«á ¦h¥[¤@­Ó°j°é §â¤å¦r¸òÃC¦â¤À¶} ´N¥i¥H¤F ¤S¬O®æ¦¡°ÝÃD
·PÁ    ·Ç¤j¤j ©M  n7822123 ¤j¤jªº«ü¾É
    For Each H In Range("«ü©w½d³ò")
        If H.Interior.Color = RGB(150, 201, 123) Then H.Offset = "¤W¯Z"
        If H.Offset.Interior.Color = RGB(255, 255, 92) Then H.Offset = "¥ð°²"
    Next H

TOP

¤l²Ó¬Ý¤F n7822123 ¤j¤jªº Sub ª±¤é´Á ()   µo²{
§â¥~­±¦A¥]¤@­Ó Int     D = Int(G.Offset)    ´N¥¿±`¤F¤£¥Î¦h¥[°j°é  >"<

TOP

        ÀR«ä¦Û¦b : ¬°¦Û¤v§äÂǤfªº¤H¥Ã»·¤£·|¶i¨B¡C
ªð¦^¦Cªí ¤W¤@¥DÃD