|
サンプル
Sub sample()
Dim dic As Object
Dim endRow As Long
Dim i As Long, j As Long
Dim v1(), v2(), v3()
With ThisWorkbook.Worksheets("Sheet1")
endRow = .Cells(.Rows.Count, 1).End(xlUp).Row
v1() = .Range("A1:A" & endRow).Value
v2() = .Range("Y1:IV" & endRow).Value
End With
Set dic = CreateObject("Scripting.Dictionary")
For i = 1 To endRow
If Not dic.exists(v1(i, 1)) Then
dic(v1(i, 1)) = i
End If
Next i
With ThisWorkbook.Worksheets("Sheet2")
endRow = .Cells(.Rows.Count, 1).End(xlUp).Row
v1() = .Range("A1:A" & endRow).Value
ReDim v3(1 To endRow, 1 To UBound(v2, 2))
For i = 1 To endRow
If dic.exists(v1(i, 1)) Then
For j = 1 To UBound(v3, 2)
v3(i, j) = v2(dic(v1(i, 1)), j)
Next j
End If
Next i
.Range("Y1").Resize(endRow, UBound(v3, 2)).Value = v3()
End With
Erase v1, v2, v3
Set dic = Nothing
End Sub
|
|