Skip to content

Instantly share code, notes, and snippets.

@mpppk

mpppk/daily_report_generator

Created July 1, 2015 17:11
Show Gist options
  • Save mpppk/9486fc3611dee755ae19 to your computer and use it in GitHub Desktop.
Save mpppk/9486fc3611dee755ae19 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. mpppk created this gist Jul 1, 2015.
    78 changes: 78 additions & 0 deletions daily_report_generator
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,78 @@
    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