■

Sub 表の作成()
    Dim myDate As Date
    Dim myDate2 As Date
    Dim myMonth As String
    Dim i As Integer
    Dim check As Boolean
    Dim check2 As Boolean
    Dim lastSun As Integer
    
    If Selection.Column <> 1 Then Exit Sub
    If IsDate(Selection.Value) Then
        myDate = Selection.Value
    Else
        Exit Sub
    End If
    
    myMonth = Month(myDate)
    
    '第５土曜日があるかどうかを判別
    For i = 29 To 31
        myDate2 = DateSerial(Year(myDate), Month(myDate), i)
        If Weekday(myDate2) = 7 And Month(myDate2) = myMonth Then
            check = True
            Exit For
        End If
    Next i
    
    '第５日曜日があるかどうかを判別
    For i = 29 To 31
        myDate2 = DateSerial(Year(myDate), Month(myDate), i)
        If Weekday(myDate2) = 1 And Month(myDate2) = myMonth Then
            check2 = True
            Exit For
        End If
    Next i
    If check2 Then
        lastSun = 5
    Else
        lastSun = 4
    End If

    i = 0
    While Month(myDate) = myMonth
        Selection.Offset(i, 0).Value = myDate
        If Weekday(myDate) <> 1 Or (Weekday(myDate) = 1 And check = False And _
            Int((Day(myDate) - 1) / 7) + 1 = lastSun Or Int((Day(myDate) - 1) / 7) + 1 = 2) Then    '変更③
            i = i + myMacro(Selection.Offset(i, 0))
        End If
        myDate = myDate + 1
    Wend
End Sub


Function myMacro(Target As Range) As Integer
    Dim myDate As Date
    Dim myDate2 As Date
    Dim res As Integer
    Dim check As Boolean
    
    res = 1
    myDate = Target.Value
    
    
    '曜日で分岐
    Select Case Application.WorksheetFunction.Weekday(myDate)
        Case 1
            If Int((Day(myDate) - 1) / 7) + 1 = 2 Then
                '-----------------------------------------------------------------日曜第２週    変更③
                Target.Offset(0, 1).Value = "日"
                Target.Offset(0, 2).Value = "13:00"
                Target.Offset(0, 3).Value = "16:00"
                Target.Offset(0, 4).FormulaR1C1 = "=(RC[-1]-RC[-2])*24"
                Target.Offset(0, 5).Value = "移動介助"
                Target.Offset(0, 6).Value = "みのり様"
                Target.Offset(0, 9).Value = "移動支援"
                Target.Offset(0, 10).Value = "移動支援みのり様"
            Else
                '-----------------------------------------------------------------最終日曜
                Target.Offset(0, 1).Value = "日"
                Target.Offset(0, 2).Value = "14:00"
                Target.Offset(0, 3).Value = "19:00"
                Target.Offset(0, 4).FormulaR1C1 = "=(RC[-1]-RC[-2])*24"                     '変更①
                Target.Offset(0, 5).Value = "移動介助"
                Target.Offset(0, 6).Value = "みのり様"
                Target.Offset(0, 9).Value = "移動支援"
                Target.Offset(0, 10).Value = "移動支援みのり様"
            End If
        Case 2
            '-----------------------------------------------------------------月曜１回目
            Target.Offset(0, 1).Value = "月"
            Target.Offset(0, 2).Value = "20:00"
            Target.Offset(0, 3).Value = "20:30"
            Target.Offset(0, 4).FormulaR1C1 = "=(RC[-1]-RC[-2])*24"
            Target.Offset(0, 5).Value = "移動介助"
            Target.Offset(0, 6).Value = "みのり様"
            Target.Offset(0, 7).Value = "レンタルショップ"
            Target.Offset(0, 8).Value = "徒歩"
            Target.Offset(0, 9).Value = "移動支援"
            Target.Offset(0, 10).Value = "移動支援みのり様"
            
            
            '-----------------------------------------------------------------月曜２回目
            Target.Offset(1, 0).Value = Target.Value
            Target.Offset(1, 1).Value = "月"
            Target.Offset(1, 2).Value = "20:30"
            Target.Offset(1, 3).Value = "21:00"
            Target.Offset(1, 4).FormulaR1C1 = "=(RC[-1]-RC[-2])*24"
            Target.Offset(1, 5).Value = "身体介護"
            Target.Offset(1, 6).Value = "みのり様"
            Target.Offset(1, 9).Value = "居宅介護"
            Target.Offset(1, 10).Value = "居宅介護みのり様"
            res = 2
        Case 3
            '-----------------------------------------------------------------火曜
            Target.Offset(0, 1).Value = "火"
            Target.Offset(0, 2).Value = "19:00"
            Target.Offset(0, 3).Value = "20:00"
            Target.Offset(0, 4).FormulaR1C1 = "=(RC[-1]-RC[-2])*24"
            Target.Offset(0, 5).Value = "身体介護"
            Target.Offset(0, 6).Value = "森田ケアーズ蔵前様"
            Target.Offset(0, 9).Value = "居宅介護"
            Target.Offset(0, 10).Value = "居宅介護森田ケアーズ蔵前様"
        Case 4
            '-----------------------------------------------------------------水曜
            Target.Offset(0, 1).Value = "水"
            Target.Offset(0, 2).Value = "19:30"
            Target.Offset(0, 3).Value = "20:30"
            Target.Offset(0, 4).FormulaR1C1 = "=(RC[-1]-RC[-2])*24"
            Target.Offset(0, 5).Value = "身体介護"
            Target.Offset(0, 6).Value = "あやの実ヘルパーステーション様"
            Target.Offset(0, 9).Value = "居宅介護"
            Target.Offset(0, 10).Value = "居宅介護あやの実ヘルパーステーション様"
        Case 5
            '-----------------------------------------------------------------木曜
            Target.Offset(0, 1).Value = "木"
            Target.Offset(0, 2).Value = "19:15"
            Target.Offset(0, 3).Value = "20:15"
            Target.Offset(0, 4).FormulaR1C1 = "=(RC[-1]-RC[-2])*24"
            Target.Offset(0, 5).Value = "身体介護"
            Target.Offset(0, 6).Value = "支援センター様"
            Target.Offset(0, 9).Value = "居宅介護"
            Target.Offset(0, 10).Value = "居宅介護支援センター様"
        Case 6
            '-----------------------------------------------------------------金曜１回目
            Target.Offset(0, 1).Value = "金"
            Target.Offset(0, 2).Value = "18:30"
            Target.Offset(0, 3).Value = "20:00"
            Target.Offset(0, 4).FormulaR1C1 = "=(RC[-1]-RC[-2])*24"
            Target.Offset(0, 5).Value = "移動介助"
            Target.Offset(0, 6).Value = "森田ケアーズ蔵前様"
            Target.Offset(0, 7).Value = "墨田点字勉強会"
            Target.Offset(0, 8).Value = "徒歩"
            Target.Offset(0, 9).Value = "移動支援"
            Target.Offset(0, 10).Value = "移動支援森田ケアーズ蔵前様"
            
            
            '-----------------------------------------------------------------金曜２回目
            Target.Offset(1, 0).Value = Target.Value
            Target.Offset(1, 1).Value = "金"
            Target.Offset(1, 2).Value = "22:00"
            Target.Offset(1, 3).Value = "23:30"
            Target.Offset(1, 4).FormulaR1C1 = "=(RC[-1]-RC[-2])*24"
            Target.Offset(1, 5).Value = "移動介助"
            Target.Offset(1, 6).Value = "森田ケアーズ蔵前様"
            Target.Offset(1, 7).Value = "ジョナサン"
            Target.Offset(1, 8).Value = "徒歩"
            Target.Offset(1, 9).Value = "移動支援"
            Target.Offset(1, 10).Value = "移動支援森田ケアーズ蔵前様"
            res = 2
        Case 7
            '-----------------------------------------------------------------土曜共通１回目
            Target.Offset(0, 1).Value = "土"
            Target.Offset(0, 2).Value = "13:00"
            Target.Offset(0, 3).Value = "15:00"
            Target.Offset(0, 4).FormulaR1C1 = "=(RC[-1]-RC[-2])*24"
            Target.Offset(0, 5).Value = "身体介護"
            Target.Offset(0, 6).Value = "あやの実ヘルパーステーション様"
            Target.Offset(0, 9).Value = "居宅介護"
            Target.Offset(0, 10).Value = "居宅介護あやの実ヘルパーステーション様"
            
            
            '第何週かで分岐
            Select Case Int((Day(myDate) - 1) / 7) + 1
                Case 1
                    '-----------------------------------------------------------------土曜第１週２回目
                    Target.Offset(1, 0).Value = Target.Value
                    Target.Offset(1, 1).Value = "土"
                    Target.Offset(1, 2).Value = "16:30"
                    Target.Offset(1, 3).Value = "19:00"
                    Target.Offset(1, 4).FormulaR1C1 = "=(RC[-1]-RC[-2])*24"
                    Target.Offset(1, 5).Value = "移動介助"
                    Target.Offset(1, 6).Value = "森田ケアーズ蔵前様"
                    Target.Offset(1, 9).Value = "移動支援"
                    Target.Offset(1, 10).Value = "移動支援森田ケアーズ蔵前様"
                    
                    
                    
                    '-----------------------------------------------------------------土曜第１週３回目
                    Target.Offset(2, 0).Value = Target.Value
                    Target.Offset(2, 1).Value = "土"
                    Target.Offset(2, 2).Value = "21:00"
                    Target.Offset(2, 3).Value = "23:30"
                    Target.Offset(2, 4).FormulaR1C1 = "=(RC[-1]-RC[-2])*24"
                    Target.Offset(2, 5).Value = "移動介助"
                    Target.Offset(2, 6).Value = "森田ケアーズ蔵前様"
                    Target.Offset(2, 9).Value = "移動支援"
                    Target.Offset(2, 10).Value = "移動支援森田ケアーズ蔵前様"
                    res = 3
                Case 2
                    '-----------------------------------------------------------------土曜第２週２回目
                    Target.Offset(1, 0).Value = Target.Value
                    Target.Offset(1, 1).Value = "土"
                    Target.Offset(1, 2).Value = "16:00"
                    Target.Offset(1, 3).Value = "17:30"
                    Target.Offset(1, 4).FormulaR1C1 = "=(RC[-1]-RC[-2])*24"
                    Target.Offset(1, 5).Value = "移動介助"
                    Target.Offset(1, 6).Value = "森田ケアーズ蔵前様"
                    Target.Offset(1, 7).Value = "手話サークル"
                    Target.Offset(1, 8).Value = "日比谷線千代田線"
                    Target.Offset(1, 9).Value = "移動支援"
                    Target.Offset(1, 10).Value = "移動支援森田ケアーズ蔵前様"
                    
                    
                    
                    '-----------------------------------------------------------------土曜第２週３回目
                    Target.Offset(2, 0).Value = Target.Value
                    Target.Offset(2, 1).Value = "土"
                    Target.Offset(2, 2).Value = "19:30"
                    Target.Offset(2, 3).Value = "21:30"
                    Target.Offset(2, 4).FormulaR1C1 = "=(RC[-1]-RC[-2])*24"
                    Target.Offset(2, 5).Value = "移動介助"
                    Target.Offset(2, 6).Value = "森田ケアーズ蔵前様"
                    Target.Offset(2, 7).Value = "手話サークル"
                    Target.Offset(2, 8).Value = "日比谷線千代田線"
                    Target.Offset(2, 9).Value = "移動支援"
                    Target.Offset(2, 10).Value = "移動支援森田ケアーズ蔵前様"
                    
                    
                    '-----------------------------------------------------------------土曜第２週の翌日
                    Target.Offset(3, 0).Value = Target.Value + 1
                    Target.Offset(3, 1).Value = "日"
                    Target.Offset(3, 2).Value = "21:30"
                    Target.Offset(3, 3).Value = "23:00"
                    Target.Offset(3, 4).FormulaR1C1 = "=(RC[-1]-RC[-2])*24"
                    Target.Offset(3, 5).Value = "移動介助"
                    Target.Offset(3, 6).Value = "森田ケアーズ蔵前様"
                    Target.Offset(3, 9).Value = "移動支援"
                    Target.Offset(3, 10).Value = "移動支援森田ケアーズ蔵前様"
                    res = 4
                Case 3, 4
                    '-----------------------------------------------------------------土曜第３・４週２回目
                    Target.Offset(1, 0).Value = Target.Value
                    Target.Offset(1, 1).Value = "土"
                    Target.Offset(1, 2).Value = "16:30"
                    Target.Offset(1, 3).Value = "18:30"
                    Target.Offset(1, 4).FormulaR1C1 = "=(RC[-1]-RC[-2])*24"
                    Target.Offset(1, 5).Value = "移動介助"
                    Target.Offset(1, 6).Value = "森田ケアーズ蔵前様"
                    Target.Offset(1, 9).Value = "移動支援"
                    Target.Offset(1, 10).Value = "移動支援森田ケアーズ蔵前様"
                    
                    
                    '-----------------------------------------------------------------土曜第３・４週３回目
                    Target.Offset(2, 0).Value = Target.Value
                    Target.Offset(2, 1).Value = "土"
                    Target.Offset(2, 2).Value = "20:30"
                    Target.Offset(2, 3).Value = "23:00"
                    Target.Offset(2, 4).FormulaR1C1 = "=(RC[-1]-RC[-2])*24"
                    Target.Offset(2, 5).Value = "移動介助"
                    Target.Offset(2, 6).Value = "森田ケアーズ蔵前様"
                    Target.Offset(2, 9).Value = "移動支援"
                    Target.Offset(2, 10).Value = "移動支援森田ケアーズ蔵前様"
                    
                    
                    '-----------------------------------------------------------------土曜第３・４週の翌日
                    Target.Offset(3, 0).Value = Target.Value + 1
                    Target.Offset(3, 1).Value = "日"
                    Target.Offset(3, 2).Value = "21:00"
                    Target.Offset(3, 3).Value = "23:00"
                    Target.Offset(3, 4).FormulaR1C1 = "=(RC[-1]-RC[-2])*24"
                    Target.Offset(3, 5).Value = "移動介助"
                    Target.Offset(3, 6).Value = "森田ケアーズ蔵前様"
                    Target.Offset(3, 9).Value = "移動支援"
                    Target.Offset(3, 10).Value = "移動支援森田ケアーズ蔵前様"
                    res = 4
                Case 5
                    '-----------------------------------------------------------------土曜第５週２回目
                    Target.Offset(1, 0).Value = Target.Value
                    Target.Offset(1, 1).Value = "土"
                    Target.Offset(1, 2).Value = "16:30"
                    Target.Offset(1, 3).Value = "21:30"
                    Target.Offset(1, 4).FormulaR1C1 = "=(RC[-1]-RC[-2])*24"
                    Target.Offset(1, 5).Value = "移動介助"
                    Target.Offset(1, 6).Value = "みのり様"
                    Target.Offset(1, 9).Value = "移動支援"
                    Target.Offset(1, 10).Value = "移動支援みのり様"
                    
                    '第５土曜だけは前行を上書き
                    Target.Offset(0, 6).Value = "みのり様"
                    res = 2
            End Select
    End Select
    
    Range("E:E").NumberFormatLocal = "0.0_ "        '変更②
    
    myMacro = res

End Function