Option Explicit
 
Public Sub 単価表設定()
    Dim ws As Worksheet     '作業シート
    Dim wrow As Long        '処理行
    Dim wcol As Long        '
    Dim trg_col As Long     '○のある列
    Dim trg_row As Long     '○のある列に一致する祝日の行（カレンダー内）
    Dim hol_col As Long     '○のある列に一致する祝日の列（I,J,Kの何れかの列）
    Dim result As Boolean
    Set ws = ActiveSheet
    For wrow = 8 To 9
        result = find_maru(ws, wrow, trg_col)
        If result = False Then
            MsgBox (ws.Cells(wrow, "A").Value & "に○がありません")
            GoTo NEXT99
        End If
        result = find_holiday(ws, trg_col, trg_row, hol_col)
        If result = False Then
            MsgBox (ws.Cells(wrow, "A").Value & "の○のある列に一致する祝日がありません")
            GoTo NEXT99
        End If
        result = set_tanka(ws, wrow, trg_col, trg_row, hol_col)
        If result = False Then
            MsgBox (ws.Cells(wrow, "A").Value & "の単価設定は失敗しました")
        End If
NEXT99:
    Next
    MsgBox ("完了")
End Sub
 
'指定行の○を探す
Private Function find_maru(ByVal ws As Worksheet, ByVal wrow As Long, trg_col As Long) As Boolean
    Dim wcol As Long
    find_maru = False
    'B～H列まで検索
    For wcol = 2 To 8
        If ws.Cells(wrow, wcol).Value = "○" Then
            trg_col = wcol
            find_maru = True
            Exit Function
        End If
    Next
End Function
 
'指定列の休日を探す
Private Function find_holiday(ByVal ws As Worksheet, ByVal trg_col As Long, trg_row As Long, hol_col As Long) As Boolean
    Dim wrow As Long
    Dim hcol As Long
    find_holiday = False
    For wrow = 3 To 7
        If ws.Cells(wrow, trg_col).Value <> "" Then
            For hcol = 9 To 11
                If ws.Cells(wrow, trg_col).Value = ws.Cells(3, hcol).Value Then
                    trg_row = wrow
                    hol_col = hcol
                    find_holiday = True
                    Exit Function
                End If
            Next
        End If
    Next
End Function
 
'指定列、指定行の次の日から月末まで単価A*を探し、設定する。（祝日はスキップする）
Private Function set_tanka(ByVal ws As Worksheet, ByVal mst_row As Long, ByVal trg_col As Long, ByVal trg_row As Long, ByVal hol_col As Long) As Boolean
    Dim flag As Boolean
    Dim result As Boolean
    Dim hcol As Long
    set_tanka = False
    result = next_day(ws, trg_col, trg_row)
    Do While result = True
        flag = False
        For hcol = 9 To 11
            If ws.Cells(trg_row, trg_col).Value = ws.Cells(3, hcol).Value Then
                flag = True
            End If
        Next
        If flag = False And Left(ws.Cells(mst_row, trg_col).Value, 1) = "A" Then
            ws.Cells(mst_row, hol_col) = ws.Cells(mst_row, trg_col).Value
            set_tanka = True
            Exit Function
        End If
        result = next_day(ws, trg_col, trg_row)
    Loop
End Function
 
'指定列、指定行の次の日から単価A*を探し、設定する
Private Function next_day(ByVal ws As Worksheet, trg_col As Long, trg_row As Long) As Boolean
    next_day = False
    trg_col = trg_col + 1
    If trg_col > 8 Then
        trg_col = 2
        trg_row = trg_row + 1
    End If
    If trg_row > 7 Then Exit Function
    If ws.Cells(trg_row, trg_col).Value = "" Then Exit Function
    next_day = True
End Function
 
 
 

T3B0aW9uIEV4cGxpY2l0CgpQdWJsaWMgU3ViIOWNmOS+oeihqOioreWumigpCiAgICBEaW0gd3MgQXMgV29ya3NoZWV0ICAgICAn5L2c5qWt44K344O844OICiAgICBEaW0gd3JvdyBBcyBMb25nICAgICAgICAn5Yem55CG6KGMCiAgICBEaW0gd2NvbCBBcyBMb25nICAgICAgICAnCiAgICBEaW0gdHJnX2NvbCBBcyBMb25nICAgICAn4peL44Gu44GC44KL5YiXCiAgICBEaW0gdHJnX3JvdyBBcyBMb25nICAgICAn4peL44Gu44GC44KL5YiX44Gr5LiA6Ie044GZ44KL56Wd5pel44Gu6KGM77yI44Kr44Os44Oz44OA44O85YaF77yJCiAgICBEaW0gaG9sX2NvbCBBcyBMb25nICAgICAn4peL44Gu44GC44KL5YiX44Gr5LiA6Ie044GZ44KL56Wd5pel44Gu5YiX77yISSxKLEvjga7kvZXjgozjgYvjga7liJfvvIkKICAgIERpbSByZXN1bHQgQXMgQm9vbGVhbgogICAgU2V0IHdzID0gQWN0aXZlU2hlZXQKICAgIEZvciB3cm93ID0gOCBUbyA5CiAgICAgICAgcmVzdWx0ID0gZmluZF9tYXJ1KHdzLCB3cm93LCB0cmdfY29sKQogICAgICAgIElmIHJlc3VsdCA9IEZhbHNlIFRoZW4KICAgICAgICAgICAgTXNnQm94ICh3cy5DZWxscyh3cm93LCAiQSIpLlZhbHVlICYgIuOBq+KXi+OBjOOBguOCiuOBvuOBm+OCkyIpCiAgICAgICAgICAgIEdvVG8gTkVYVDk5CiAgICAgICAgRW5kIElmCiAgICAgICAgcmVzdWx0ID0gZmluZF9ob2xpZGF5KHdzLCB0cmdfY29sLCB0cmdfcm93LCBob2xfY29sKQogICAgICAgIElmIHJlc3VsdCA9IEZhbHNlIFRoZW4KICAgICAgICAgICAgTXNnQm94ICh3cy5DZWxscyh3cm93LCAiQSIpLlZhbHVlICYgIuOBruKXi+OBruOBguOCi+WIl+OBq+S4gOiHtOOBmeOCi+elneaXpeOBjOOBguOCiuOBvuOBm+OCkyIpCiAgICAgICAgICAgIEdvVG8gTkVYVDk5CiAgICAgICAgRW5kIElmCiAgICAgICAgcmVzdWx0ID0gc2V0X3RhbmthKHdzLCB3cm93LCB0cmdfY29sLCB0cmdfcm93LCBob2xfY29sKQogICAgICAgIElmIHJlc3VsdCA9IEZhbHNlIFRoZW4KICAgICAgICAgICAgTXNnQm94ICh3cy5DZWxscyh3cm93LCAiQSIpLlZhbHVlICYgIuOBruWNmOS+oeioreWumuOBr+WkseaVl+OBl+OBvuOBl+OBnyIpCiAgICAgICAgRW5kIElmCk5FWFQ5OToKICAgIE5leHQKICAgIE1zZ0JveCAoIuWujOS6hiIpCkVuZCBTdWIKCifmjIflrprooYzjga7il4vjgpLmjqLjgZkKUHJpdmF0ZSBGdW5jdGlvbiBmaW5kX21hcnUoQnlWYWwgd3MgQXMgV29ya3NoZWV0LCBCeVZhbCB3cm93IEFzIExvbmcsIHRyZ19jb2wgQXMgTG9uZykgQXMgQm9vbGVhbgogICAgRGltIHdjb2wgQXMgTG9uZwogICAgZmluZF9tYXJ1ID0gRmFsc2UKICAgICdC772eSOWIl+OBvuOBp+aknOe0ogogICAgRm9yIHdjb2wgPSAyIFRvIDgKICAgICAgICBJZiB3cy5DZWxscyh3cm93LCB3Y29sKS5WYWx1ZSA9ICLil4siIFRoZW4KICAgICAgICAgICAgdHJnX2NvbCA9IHdjb2wKICAgICAgICAgICAgZmluZF9tYXJ1ID0gVHJ1ZQogICAgICAgICAgICBFeGl0IEZ1bmN0aW9uCiAgICAgICAgRW5kIElmCiAgICBOZXh0CkVuZCBGdW5jdGlvbgoKJ+aMh+WumuWIl+OBruS8keaXpeOCkuaOouOBmQpQcml2YXRlIEZ1bmN0aW9uIGZpbmRfaG9saWRheShCeVZhbCB3cyBBcyBXb3Jrc2hlZXQsIEJ5VmFsIHRyZ19jb2wgQXMgTG9uZywgdHJnX3JvdyBBcyBMb25nLCBob2xfY29sIEFzIExvbmcpIEFzIEJvb2xlYW4KICAgIERpbSB3cm93IEFzIExvbmcKICAgIERpbSBoY29sIEFzIExvbmcKICAgIGZpbmRfaG9saWRheSA9IEZhbHNlCiAgICBGb3Igd3JvdyA9IDMgVG8gNwogICAgICAgIElmIHdzLkNlbGxzKHdyb3csIHRyZ19jb2wpLlZhbHVlIDw+ICIiIFRoZW4KICAgICAgICAgICAgRm9yIGhjb2wgPSA5IFRvIDExCiAgICAgICAgICAgICAgICBJZiB3cy5DZWxscyh3cm93LCB0cmdfY29sKS5WYWx1ZSA9IHdzLkNlbGxzKDMsIGhjb2wpLlZhbHVlIFRoZW4KICAgICAgICAgICAgICAgICAgICB0cmdfcm93ID0gd3JvdwogICAgICAgICAgICAgICAgICAgIGhvbF9jb2wgPSBoY29sCiAgICAgICAgICAgICAgICAgICAgZmluZF9ob2xpZGF5ID0gVHJ1ZQogICAgICAgICAgICAgICAgICAgIEV4aXQgRnVuY3Rpb24KICAgICAgICAgICAgICAgIEVuZCBJZgogICAgICAgICAgICBOZXh0CiAgICAgICAgRW5kIElmCiAgICBOZXh0CkVuZCBGdW5jdGlvbgoKJ+aMh+WumuWIl+OAgeaMh+WumuihjOOBruasoeOBruaXpeOBi+OCieaciOacq+OBvuOBp+WNmOS+oUEq44KS5o6i44GX44CB6Kit5a6a44GZ44KL44CC77yI56Wd5pel44Gv44K544Kt44OD44OX44GZ44KL77yJClByaXZhdGUgRnVuY3Rpb24gc2V0X3RhbmthKEJ5VmFsIHdzIEFzIFdvcmtzaGVldCwgQnlWYWwgbXN0X3JvdyBBcyBMb25nLCBCeVZhbCB0cmdfY29sIEFzIExvbmcsIEJ5VmFsIHRyZ19yb3cgQXMgTG9uZywgQnlWYWwgaG9sX2NvbCBBcyBMb25nKSBBcyBCb29sZWFuCiAgICBEaW0gZmxhZyBBcyBCb29sZWFuCiAgICBEaW0gcmVzdWx0IEFzIEJvb2xlYW4KICAgIERpbSBoY29sIEFzIExvbmcKICAgIHNldF90YW5rYSA9IEZhbHNlCiAgICByZXN1bHQgPSBuZXh0X2RheSh3cywgdHJnX2NvbCwgdHJnX3JvdykKICAgIERvIFdoaWxlIHJlc3VsdCA9IFRydWUKICAgICAgICBmbGFnID0gRmFsc2UKICAgICAgICBGb3IgaGNvbCA9IDkgVG8gMTEKICAgICAgICAgICAgSWYgd3MuQ2VsbHModHJnX3JvdywgdHJnX2NvbCkuVmFsdWUgPSB3cy5DZWxscygzLCBoY29sKS5WYWx1ZSBUaGVuCiAgICAgICAgICAgICAgICBmbGFnID0gVHJ1ZQogICAgICAgICAgICBFbmQgSWYKICAgICAgICBOZXh0CiAgICAgICAgSWYgZmxhZyA9IEZhbHNlIEFuZCBMZWZ0KHdzLkNlbGxzKG1zdF9yb3csIHRyZ19jb2wpLlZhbHVlLCAxKSA9ICJBIiBUaGVuCiAgICAgICAgICAgIHdzLkNlbGxzKG1zdF9yb3csIGhvbF9jb2wpID0gd3MuQ2VsbHMobXN0X3JvdywgdHJnX2NvbCkuVmFsdWUKICAgICAgICAgICAgc2V0X3RhbmthID0gVHJ1ZQogICAgICAgICAgICBFeGl0IEZ1bmN0aW9uCiAgICAgICAgRW5kIElmCiAgICAgICAgcmVzdWx0ID0gbmV4dF9kYXkod3MsIHRyZ19jb2wsIHRyZ19yb3cpCiAgICBMb29wCkVuZCBGdW5jdGlvbgoKJ+aMh+WumuWIl+OAgeaMh+WumuihjOOBruasoeOBruaXpeOBi+OCieWNmOS+oUEq44KS5o6i44GX44CB6Kit5a6a44GZ44KLClByaXZhdGUgRnVuY3Rpb24gbmV4dF9kYXkoQnlWYWwgd3MgQXMgV29ya3NoZWV0LCB0cmdfY29sIEFzIExvbmcsIHRyZ19yb3cgQXMgTG9uZykgQXMgQm9vbGVhbgogICAgbmV4dF9kYXkgPSBGYWxzZQogICAgdHJnX2NvbCA9IHRyZ19jb2wgKyAxCiAgICBJZiB0cmdfY29sID4gOCBUaGVuCiAgICAgICAgdHJnX2NvbCA9IDIKICAgICAgICB0cmdfcm93ID0gdHJnX3JvdyArIDEKICAgIEVuZCBJZgogICAgSWYgdHJnX3JvdyA+IDcgVGhlbiBFeGl0IEZ1bmN0aW9uCiAgICBJZiB3cy5DZWxscyh0cmdfcm93LCB0cmdfY29sKS5WYWx1ZSA9ICIiIFRoZW4gRXhpdCBGdW5jdGlvbgogICAgbmV4dF9kYXkgPSBUcnVlCkVuZCBGdW5jdGlvbgoKCg==