daily_report_generator

Option Explicit

'searchRangeの中からsearchWordを値として持つセルを返す
Public Function getTargetCell(searchWord As String, searchRange As Variant) As Range
    Dim targetCell As Range
    If searchWord <> "" Then
        '対応する作業の行を取得
        Set targetCell = searchRange.Find(What:=searchWord)
        If Not targetCell Is Nothing Then
            Set getTargetCell = targetCell
            Exit Function
        End If
    End If
End Function

Public Sub Test()
    'ソースシートの予定開始時間を書く列
    Dim srcPlanStartTimeCol As Integer
    srcPlanStartTimeCol = 3

    '日報のシート
    Dim nippouSheet As Worksheet
    Set nippouSheet = Worksheets("PJ日報")

    'ソースシートの作業名が書かれている範囲
    Dim srcWorkNameRange As Range
    Set srcWorkNameRange = Range("B1:B100")
    
    '日報の作業名が書かれている範囲
    Dim dstWorkNameRange As Variant
    Set dstWorkNameRange = nippouSheet.Range("A2:A200")
    
    'ソースシートのメンバー名が書かれている範囲
    Dim srcMemberNameRange As Range
    Set srcMemberNameRange = Range("A1:A10")
    
    '日報のメンバー名が書かれている範囲
    Dim dstMemberNameRange As Range
    Set dstMemberNameRange = nippouSheet.Range("A1:J1")
    
    ' -------- ここから実際の処理 --------
    
    Dim i As Integer
    For i = 2 To 199
       '作業名が合致するセルを取得
        Dim srcWorkNameCell As Range
        Set srcWorkNameCell = srcWorkNameRange(i, 1)
        Dim workCell As Range
        Set workCell = getTargetCell(srcWorkNameCell.Value, dstWorkNameRange)

        'メンバー名が合致するセルを取得
        Dim srcMemberNameCell As Range
        Set srcMemberNameCell = srcMemberNameRange(i, 1)
        Dim memberCell As Range
        Set memberCell = getTargetCell(srcMemberNameCell.Value, dstMemberNameRange)
        
        If Not workCell Is Nothing Then
            If Not memberCell Is Nothing Then
                Debug.Print "work name: " & workCell.Value
                Debug.Print "member name: " & memberCell.Value
            
                'ソースシートの予定開始時間を取得
                Dim srcPlanStartTime As Range
                Set srcPlanStartTime = Cells(i, srcPlanStartTimeCol)
                
                '各種時間を日報に記入
                Dim j As Integer
                For j = 0 To 3
                    Dim timeStr As String
                    timeStr = Format(srcPlanStartTime.Offset(0, j).Value, "h:mm")
                    Debug.Print "time str: " & timeStr
                    nippouSheet.Cells(workCell.Row, memberCell.Column).Offset(j, 0).Value = timeStr
                Next
            End If
        End If
    Next
    Debug.Print "finish!"
End Sub