|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
Sub 字典取数()
Dim aData, aRes, i%, j%
Dim endRow
Dim Dic As Object
Set Dic = CreateObject("scripting.dictionary")
aData = Sheets("原始数据").[A1].CurrentRegion
For i = 2 To UBound(aData)
For j = 3 To UBound(aData, 2)
Dic(aData(i, 2) & "-" & aData(1, j)) = aData(i, j)
Next j
Next i
With Sheets("需求")
endRow = .Cells(Rows.Count, 1).End(xlUp).Row
.Range("b2:j" & endRow) = Empty
aRes = .[A1].CurrentRegion
For i = 2 To UBound(aRes)
For j = 2 To UBound(aRes, 2)
aRes(i, j) = Dic(aRes(i, 1) & "-" & aRes(1, j))
Next j
Next i
.Range("a1").Resize(UBound(aRes), UBound(aRes, 2)) = aRes
End With
End Sub
|
|