Sub 連想配列Currentregionを使う()
'************事前準備*******************
'参照設定：Microsoft Scripting Runtime
'***************************************
'***************************************
'タイマーをセット
'***************************************
Dim startTime As Single, endTime As Single
startTime = Timer
'***************************************
'Keyになる集計表の列を配列に格納
'***************************************
Dim XX As Long
Dim arrSyukei As Variant
Set Smg = Sheets("Sheet2")
Set Myedge = Sheets("Sheet1")
XX = Smg.Cells(Rows.Count, 1).End(xlUp).Row
arrSyukei = Smg.Range("A11:A" & XX).Value
'***************************************
'データシート全体を配列に格納
'***************************************
Dim YY As Long
Dim arrEdge As Variant
YY = Myedge.Cells(Rows.Count, 1).End(xlUp).Row
'注意 データ範囲内を指定すること
arrEdge = Myedge.Range("A2").CurrentRegion.Value
'***************************************
'連想配列Dictionaryを使って処理
'***************************************
Dim arrHit() As Variant
ReDim arrHit(1 To UBound(arrSyukei), 1 To 1)
Dim Dicedge As Dictionary
Set Dicedge = New Dictionary
Dim AA As Long, BB As Long
For BB = LBound(arrEdge) To UBound(arrEdge)
Dicedge.Add arrEdge(BB, 1), arrEdge(BB, 2) 'keyにする列、出力したい列
Next BB
Dim Syukeikey As Variant
For AA = LBound(arrSyukei) To UBound(arrSyukei)
Syukeikey = arrSyukei(AA, 1) 'keyで結び付ける
If Dicedge.Exists(Syukeikey) Then
arrHit(AA, 1) = Dicedge(Syukeikey)
Else
arrHit(AA, 1) = ""
End If
Next AA
'***************************************
'連想配列の要素を集計表に転記
'***************************************
Smg.Range("E11:E" & XX) = arrHit '出力する列
'***************************************
'処理時間を表示
'***************************************
endTime = Timer
MsgBox endTime - startTime
End Sub
