excelVBAで連想配列

excelvba 
Sub 支社別集計()
    Dim Sheet1, Sheet2 As Worksheet
    Const COL店舗CD = 1 ' 店舗CDの列
    Const COL分類CD = 3 ' 分類CDの列
    Const COL売上額 = 10 ' 売上額
    Dim MaxRow As Long ' 最終行
    Dim key As String ' 検索キー
    Dim c, r As Long
    
    Set dicT = CreateObject("Scripting.Dictionary") ' 連想配列の定義
    
    Set Sheet1 = Worksheets("売上明細") ' 明細シート
    Set Sheet2 = Worksheets("売上集計") ' 集計シート
    
    MaxRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row ' 最終行を求める
    
    ' 売上明細を連想配列へ読み込む
    With Sheet1
        For r = 2 To MaxRow ' 最終行
            key = .Cells(r, COL店舗CD).Value & .Cells(r, COL分類CD).Value ' 店舗CD & 分類CD
            dicT(key) = dicT(key) + .Cells(r, COL売上額).Value ' 売上額
        Next
    End With
        
    ' 集計シートへの書き出し
    With Sheet2
        For c = 3 To 12 ' 列
            For r = 4 To 12 ' 行
                key = .Cells(2, c).Value & .Cells(r, 1).Value ' 店舗CD & 分類CD
                .Cells(r, c) = dicT(key)
            Next
        Next
    End With
End Sub

Dictionaryオブジェクトのリファレンスはリンク先で


ネタ元