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
This article is a Private article. Only a writer and users who know the URL can access it.
Please change open range to public in publish setting if you want to share this article with other users.
More than 1 year has passed since last update.
0
Go to list of comments
Register as a new user and use Qiita more conveniently
- You get articles that match your needs
- You can efficiently read back useful information
- You can use dark theme