simple_excel_macro

maibo.vb

Sub UpdateNaboTable()
    Dim wsNabo As Worksheet, wsMaster As Worksheet
    Dim lastRowNabo As Long, lastRowMaster As Long
    Dim i As Long, j As Long
    Dim masterPrefecture As String, masterAge As Long, masterNotes As String
    Dim naboPrefecture As String, naboBirthday As Date
    Dim currentYear As Long
    Dim colNaboPrefecture As Range, colNaboBirthday As Range, colNaboNotes As Range
    Dim colMasterPrefecture As Range, colMasterAge As Range, colMasterNotes As Range

    ' シートを設定
    Set wsNabo = ThisWorkbook.Sheets("名簿")
    Set wsMaster = ThisWorkbook.Sheets("マスタ")

    ' 名簿テーブルとマスタテーブルの最終行を取得
    lastRowNabo = wsNabo.Cells(wsNabo.Rows.Count, 1).End(xlUp).Row
    lastRowMaster = wsMaster.Cells(wsMaster.Rows.Count, 1).End(xlUp).Row

    ' カラムの設定
    Set colNaboPrefecture = wsNabo.Columns("D")
    Set colNaboBirthday = wsNabo.Columns("C")
    Set colNaboNotes = wsNabo.Columns("E")
    Set colMasterPrefecture = wsMaster.Columns("A")
    Set colMasterAge = wsMaster.Columns("B")
    Set colMasterNotes = wsMaster.Columns("C")

    ' 現在の年を取得
    currentYear = Year(Date)

    ' マスタテーブルをループ
    For i = 2 To lastRowMaster
        masterPrefecture = colMasterPrefecture.Cells(i, 1).Value ' 都道府県
        masterAge = colMasterAge.Cells(i, 1).Value               ' 年齢
        masterNotes = colMasterNotes.Cells(i, 1).Value           ' 備考

        ' 名簿テーブルをループ
        For j = 2 To lastRowNabo
            naboPrefecture = colNaboPrefecture.Cells(j, 1).Value  ' 都道府県
            naboBirthday = colNaboBirthday.Cells(j, 1).Value     ' 誕生日

            ' 条件に一致する場合に備考を更新
            If naboPrefecture = masterPrefecture And (currentYear - Year(naboBirthday)) >= masterAge Then
                colNaboNotes.Cells(j, 1).Value = masterNotes
            End If
        Next j
    Next i

    MsgBox "備考の更新が完了しました。"
End Sub

名簿とマスタの2つのシートにそれぞれテーブルがある。
マスタテーブルの条件にそって、名簿テーブルを変換する例。