|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
Sub 查询()
Dim i%, lr%
Dim ar, br, cr
Dim d
Application.ScreenUpdating = False
Set d = CreateObject("Scripting.Dictionary")
With Sheets("总表")
lr = .[a65536].End(3).Row
ar = .Range("a1:e" & lr)
For i = 2 To UBound(ar)
d(ar(i, 1)) = i
Next i
End With
With Sheets("查询")
lr = .[a65536].End(3).Row
br = .[a1].Resize(lr)
ReDim cr(1 To lr - 1, 1 To 4)
For i = 1 To UBound(br) - 1
If d.exists(br(i + 1, 1)) Then
cr(i, 1) = ar(d(br(i + 1, 1)), 2): cr(i, 2) = ar(d(br(i + 1, 1)), 3)
cr(i, 3) = ar(d(br(i + 1, 1)), 4): cr(i, 4) = ar(d(br(i + 1, 1)), 5)
End If
Next i
.[b2:e65536].ClearContents
.[b2].Resize(UBound(cr), 4) = cr
End With
Application.ScreenUpdating = True
End Sub |
|