Option Explicit
 
Public Sub 関数設定()
    Dim ws As Worksheet
    Dim dd As Long
    Dim gp As Long
    Dim lastday As Long
    Dim yyyy As Long
    Dim mm As Long
    Dim wrow As Long
    Dim wcol As Long
    Dim wstr As String
    Set ws = ActiveSheet
    If GetVal(ws.Cells(2, "B").Value, 2000, 2099, yyyy) = False Then
        MsgBox ("年が不正です。")
        Exit Sub
    End If
    If GetVal(ws.Cells(3, "B").Value, 1, 12, mm) = False Then
        MsgBox ("月が不正です。")
        Exit Sub
    End If
    lastday = GetLastDay(yyyy, mm)
    For dd = 1 To lastday
        wrow = 3 + (dd - 1) * 3
        For gp = 1 To 4
            wcol = 17 + (gp - 1) * 5
            Call set_func(ws, wrow, wcol, wcol + 1, 1, "F", "Ⅰ棟")
            Call set_func(ws, wrow, wcol, wcol + 2, 2, "I", "Ⅱ棟-2F")
            Call set_func(ws, wrow, wcol, wcol + 3, 2, "I", "Ⅱ棟-3F")
            Call set_func(ws, wrow, wcol, wcol + 4, 3, "O", "Ⅲ棟")
        Next
    Next
    MsgBox ("完了")
End Sub
'関数設定
Private Sub set_func(ByVal ws As Worksheet, ByVal trg_row As Long, ByVal ref_col As Long, ByVal trg_col As Long, ByVal count As Long, ByVal HCOL As String, ByVal POS As String)
    Dim rfcol As String
    Dim fmt As String
    Dim res As String
    Dim row1 As String
    Dim row2 As String
    Dim row3 As String
 
    Const base As String = "=IFS(OR($JROW1=""午前①"",$JROW1=""午前②""),IF($HROW1>=COUNT,""出勤"","""")," & _
                            "OR($JROW1=""午後①"",$JROW1=""午後②""),IF($HROW2>=COUNT,""出勤"","""")," & _
                            "OR($JROW1=""夜間①"",$JROW1=""夜間②""),IF($HROW3>=COUNT,""出勤"","""")," & _
                            "OR($JROW1=""待機①"",$JROW1=""待機②""),"""")"
    If IsError(ws.Cells(trg_row, trg_col).Value) = False Then
        If ws.Cells(trg_row, trg_col).Value <> "" Then
            If ws.Cells(trg_row, trg_col).HasFormula = False Then Exit Sub
        End If
    End If
    rfcol = ConvertToLetter(ref_col)
    row1 = trg_row
    row2 = trg_row + 1
    row3 = trg_row + 2
    fmt = base
    fmt = Replace(fmt, "H", HCOL)
    fmt = Replace(fmt, "出勤", POS)
    fmt = Replace(fmt, "J", rfcol)
    fmt = Replace(fmt, "ROW1", trg_row)
    fmt = Replace(fmt, "ROW2", trg_row + 1)
    fmt = Replace(fmt, "ROW3", trg_row + 2)
    fmt = Replace(fmt, "COUNT", count)
    ws.Cells(trg_row, trg_col).Formula = fmt
End Sub
'年／月取得
Private Function GetVal(ByVal str As String, ByVal low_limit As Long, ByVal high_limit As Long, ByRef val As Long) As Boolean
    GetVal = False
    If IsNumeric(str) = False Then Exit Function
    val = CLng(str)
    If val < low_limit Then Exit Function
    If val > high_limit Then Exit Function
    GetVal = True
End Function
 
 
'列番号をA～Zの文字に変換
Function ConvertToLetter(iCol As Long) As String
    Dim a As Long
    Dim b As Long
    a = iCol
    ConvertToLetter = ""
    Do While iCol > 0
        a = Int((iCol - 1) / 26)
        b = (iCol - 1) Mod 26
        ConvertToLetter = Chr(b + 65) & ConvertToLetter
        iCol = a
    Loop
End Function
 
'月末日取得
Private Function GetLastDay(ByVal yyyy As Long, ByVal mm As Long) As Long
    Dim wday As Date
    mm = mm + 1
    If mm > 12 Then
        yyyy = yyyy + 1
        mm = 1
    End If
    wday = DateSerial(yyyy, mm, 1)
    wday = wday - 1
    GetLastDay = day(wday)
End Function
 

T3B0aW9uIEV4cGxpY2l0CgpQdWJsaWMgU3ViIOmWouaVsOioreWumigpCiAgICBEaW0gd3MgQXMgV29ya3NoZWV0CiAgICBEaW0gZGQgQXMgTG9uZwogICAgRGltIGdwIEFzIExvbmcKICAgIERpbSBsYXN0ZGF5IEFzIExvbmcKICAgIERpbSB5eXl5IEFzIExvbmcKICAgIERpbSBtbSBBcyBMb25nCiAgICBEaW0gd3JvdyBBcyBMb25nCiAgICBEaW0gd2NvbCBBcyBMb25nCiAgICBEaW0gd3N0ciBBcyBTdHJpbmcKICAgIFNldCB3cyA9IEFjdGl2ZVNoZWV0CiAgICBJZiBHZXRWYWwod3MuQ2VsbHMoMiwgIkIiKS5WYWx1ZSwgMjAwMCwgMjA5OSwgeXl5eSkgPSBGYWxzZSBUaGVuCiAgICAgICAgTXNnQm94ICgi5bm044GM5LiN5q2j44Gn44GZ44CCIikKICAgICAgICBFeGl0IFN1YgogICAgRW5kIElmCiAgICBJZiBHZXRWYWwod3MuQ2VsbHMoMywgIkIiKS5WYWx1ZSwgMSwgMTIsIG1tKSA9IEZhbHNlIFRoZW4KICAgICAgICBNc2dCb3ggKCLmnIjjgYzkuI3mraPjgafjgZnjgIIiKQogICAgICAgIEV4aXQgU3ViCiAgICBFbmQgSWYKICAgIGxhc3RkYXkgPSBHZXRMYXN0RGF5KHl5eXksIG1tKQogICAgRm9yIGRkID0gMSBUbyBsYXN0ZGF5CiAgICAgICAgd3JvdyA9IDMgKyAoZGQgLSAxKSAqIDMKICAgICAgICBGb3IgZ3AgPSAxIFRvIDQKICAgICAgICAgICAgd2NvbCA9IDE3ICsgKGdwIC0gMSkgKiA1CiAgICAgICAgICAgIENhbGwgc2V0X2Z1bmMod3MsIHdyb3csIHdjb2wsIHdjb2wgKyAxLCAxLCAiRiIsICLihaDmo58iKQogICAgICAgICAgICBDYWxsIHNldF9mdW5jKHdzLCB3cm93LCB3Y29sLCB3Y29sICsgMiwgMiwgIkkiLCAi4oWh5qOfLTJGIikKICAgICAgICAgICAgQ2FsbCBzZXRfZnVuYyh3cywgd3Jvdywgd2NvbCwgd2NvbCArIDMsIDIsICJJIiwgIuKFoeajny0zRiIpCiAgICAgICAgICAgIENhbGwgc2V0X2Z1bmMod3MsIHdyb3csIHdjb2wsIHdjb2wgKyA0LCAzLCAiTyIsICLihaLmo58iKQogICAgICAgIE5leHQKICAgIE5leHQKICAgIE1zZ0JveCAoIuWujOS6hiIpCkVuZCBTdWIKJ+mWouaVsOioreWumgpQcml2YXRlIFN1YiBzZXRfZnVuYyhCeVZhbCB3cyBBcyBXb3Jrc2hlZXQsIEJ5VmFsIHRyZ19yb3cgQXMgTG9uZywgQnlWYWwgcmVmX2NvbCBBcyBMb25nLCBCeVZhbCB0cmdfY29sIEFzIExvbmcsIEJ5VmFsIGNvdW50IEFzIExvbmcsIEJ5VmFsIEhDT0wgQXMgU3RyaW5nLCBCeVZhbCBQT1MgQXMgU3RyaW5nKQogICAgRGltIHJmY29sIEFzIFN0cmluZwogICAgRGltIGZtdCBBcyBTdHJpbmcKICAgIERpbSByZXMgQXMgU3RyaW5nCiAgICBEaW0gcm93MSBBcyBTdHJpbmcKICAgIERpbSByb3cyIEFzIFN0cmluZwogICAgRGltIHJvdzMgQXMgU3RyaW5nCiAgICAKICAgIENvbnN0IGJhc2UgQXMgU3RyaW5nID0gIj1JRlMoT1IoJEpST1cxPSIi5Y2I5YmN4pGgIiIsJEpST1cxPSIi5Y2I5YmN4pGhIiIpLElGKCRIUk9XMT49Q09VTlQsIiLlh7rli6QiIiwiIiIiKSwiICYgXwogICAgICAgICAgICAgICAgICAgICAgICAgICAgIk9SKCRKUk9XMT0iIuWNiOW+jOKRoCIiLCRKUk9XMT0iIuWNiOW+jOKRoSIiKSxJRigkSFJPVzI+PUNPVU5ULCIi5Ye65YukIiIsIiIiIiksIiAmIF8KICAgICAgICAgICAgICAgICAgICAgICAgICAgICJPUigkSlJPVzE9IiLlpJzplpPikaAiIiwkSlJPVzE9IiLlpJzplpPikaEiIiksSUYoJEhST1czPj1DT1VOVCwiIuWHuuWLpCIiLCIiIiIpLCIgJiBfCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAiT1IoJEpST1cxPSIi5b6F5qmf4pGgIiIsJEpST1cxPSIi5b6F5qmf4pGhIiIpLCIiIiIpIgogICAgSWYgSXNFcnJvcih3cy5DZWxscyh0cmdfcm93LCB0cmdfY29sKS5WYWx1ZSkgPSBGYWxzZSBUaGVuCiAgICAgICAgSWYgd3MuQ2VsbHModHJnX3JvdywgdHJnX2NvbCkuVmFsdWUgPD4gIiIgVGhlbgogICAgICAgICAgICBJZiB3cy5DZWxscyh0cmdfcm93LCB0cmdfY29sKS5IYXNGb3JtdWxhID0gRmFsc2UgVGhlbiBFeGl0IFN1YgogICAgICAgIEVuZCBJZgogICAgRW5kIElmCiAgICByZmNvbCA9IENvbnZlcnRUb0xldHRlcihyZWZfY29sKQogICAgcm93MSA9IHRyZ19yb3cKICAgIHJvdzIgPSB0cmdfcm93ICsgMQogICAgcm93MyA9IHRyZ19yb3cgKyAyCiAgICBmbXQgPSBiYXNlCiAgICBmbXQgPSBSZXBsYWNlKGZtdCwgIkgiLCBIQ09MKQogICAgZm10ID0gUmVwbGFjZShmbXQsICLlh7rli6QiLCBQT1MpCiAgICBmbXQgPSBSZXBsYWNlKGZtdCwgIkoiLCByZmNvbCkKICAgIGZtdCA9IFJlcGxhY2UoZm10LCAiUk9XMSIsIHRyZ19yb3cpCiAgICBmbXQgPSBSZXBsYWNlKGZtdCwgIlJPVzIiLCB0cmdfcm93ICsgMSkKICAgIGZtdCA9IFJlcGxhY2UoZm10LCAiUk9XMyIsIHRyZ19yb3cgKyAyKQogICAgZm10ID0gUmVwbGFjZShmbXQsICJDT1VOVCIsIGNvdW50KQogICAgd3MuQ2VsbHModHJnX3JvdywgdHJnX2NvbCkuRm9ybXVsYSA9IGZtdApFbmQgU3ViCiflubTvvI/mnIjlj5blvpcKUHJpdmF0ZSBGdW5jdGlvbiBHZXRWYWwoQnlWYWwgc3RyIEFzIFN0cmluZywgQnlWYWwgbG93X2xpbWl0IEFzIExvbmcsIEJ5VmFsIGhpZ2hfbGltaXQgQXMgTG9uZywgQnlSZWYgdmFsIEFzIExvbmcpIEFzIEJvb2xlYW4KICAgIEdldFZhbCA9IEZhbHNlCiAgICBJZiBJc051bWVyaWMoc3RyKSA9IEZhbHNlIFRoZW4gRXhpdCBGdW5jdGlvbgogICAgdmFsID0gQ0xuZyhzdHIpCiAgICBJZiB2YWwgPCBsb3dfbGltaXQgVGhlbiBFeGl0IEZ1bmN0aW9uCiAgICBJZiB2YWwgPiBoaWdoX2xpbWl0IFRoZW4gRXhpdCBGdW5jdGlvbgogICAgR2V0VmFsID0gVHJ1ZQpFbmQgRnVuY3Rpb24KCgon5YiX55Wq5Y+344KSQe+9nlrjga7mloflrZfjgavlpInmj5sKRnVuY3Rpb24gQ29udmVydFRvTGV0dGVyKGlDb2wgQXMgTG9uZykgQXMgU3RyaW5nCiAgICBEaW0gYSBBcyBMb25nCiAgICBEaW0gYiBBcyBMb25nCiAgICBhID0gaUNvbAogICAgQ29udmVydFRvTGV0dGVyID0gIiIKICAgIERvIFdoaWxlIGlDb2wgPiAwCiAgICAgICAgYSA9IEludCgoaUNvbCAtIDEpIC8gMjYpCiAgICAgICAgYiA9IChpQ29sIC0gMSkgTW9kIDI2CiAgICAgICAgQ29udmVydFRvTGV0dGVyID0gQ2hyKGIgKyA2NSkgJiBDb252ZXJ0VG9MZXR0ZXIKICAgICAgICBpQ29sID0gYQogICAgTG9vcApFbmQgRnVuY3Rpb24KCifmnIjmnKvml6Xlj5blvpcKUHJpdmF0ZSBGdW5jdGlvbiBHZXRMYXN0RGF5KEJ5VmFsIHl5eXkgQXMgTG9uZywgQnlWYWwgbW0gQXMgTG9uZykgQXMgTG9uZwogICAgRGltIHdkYXkgQXMgRGF0ZQogICAgbW0gPSBtbSArIDEKICAgIElmIG1tID4gMTIgVGhlbgogICAgICAgIHl5eXkgPSB5eXl5ICsgMQogICAgICAgIG1tID0gMQogICAgRW5kIElmCiAgICB3ZGF5ID0gRGF0ZVNlcmlhbCh5eXl5LCBtbSwgMSkKICAgIHdkYXkgPSB3ZGF5IC0gMQogICAgR2V0TGFzdERheSA9IGRheSh3ZGF5KQpFbmQgRnVuY3Rpb24K