Q&A回答用(ExcelVBAで簿記的な勘定科目を転記したい)

Option Explicit

Sub 勘定科目転記()

    Const startRow = 4
    Const targetColumn = 5
    Const accountColumn = 1
    Const updateColumn = 5

    Dim maxRow As Long
    Dim i As Long
    Dim dataSheet As Worksheet: Set dataSheet = ThisWorkbook.Sheets("明細")
    Dim masterSheet As Worksheet: Set masterSheet = ThisWorkbook.Sheets("main")

    maxRow = dataSheet.Cells(Rows.Count, 1).End(xlUp).Row

    Dim accountDic As Object
    Set accountDic = CreateObject("Scripting.Dictionary")
    Call getMaster(accountDic, masterSheet, 4, 2)

    For i = 4 To maxRow
        dataSheet.Cells(i, targetColumn) = accountDic.Item(dataSheet.Cells(i, accountColumn).Value)
    Next i

End Sub

Function getMaster(dictionary As Object, targetSheet As Worksheet, startRow As Long, keyColumn As Long)

    Dim i: i = startRow

    Do
        If targetSheet.Cells(i, keyColumn) = "" Then Exit Do
        dictionary.Add targetSheet.Cells(i, keyColumn).Value, targetSheet.Cells(i, keyColumn + 1).Value
        i = i + 1
    Loop

End Function