|
楼主 |
发表于 2017-8-8 20:48
|
显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
Sub test1()
Application.ScreenUpdating = False: Application.DisplayAlerts = False: Set d = CreateObject("scripting.dictionary")
r = Cells(Rows.Count, 9).End(xlUp).Row: arr = Range("i20:j" & r)
For i = 1 To UBound(arr)
xm = CStr(arr(i, 2))
If Not d.exists(xm) Then
Set d(xm) = CreateObject("scripting.dictionary")
End If
d(xm)(arr(i, 1)) = i
Next
For ii = 1 To 2
Set ws = Sheets(ii + 1): ReDim brr(1 To UBound(arr), 1 To 1): With ws
r = .Cells(.Rows.Count, 1).End(xlUp).Row: crr = .Range("a1:e" & r)
For i = 2 To UBound(crr)
xm = CStr(crr(i, 1))
If d.exists(xm) Then
For j = 2 To UBound(crr, 2)
If d(xm).exists(crr(1, j)) Then
m = d(xm)(crr(1, j)): brr(m, 1) = crr(i, j)
End If
Next
End If
Next
End With
Cells(20, 14 + ii).Resize(UBound(brr), 1) = brr
Next
End Sub
补充内容 (2017-12-14 14:20):
第一段要调整2处
|
|