|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
Sub hz()
Application.ScreenUpdating = False
Set sh = ThisWorkbook.Worksheets("汇总")
Dim d As Object
Set d = CreateObject("scripting.dictionary")
ws = sh.Cells(Rows.Count, 2).End(xlUp).Row
sh.Range("e3:t" & ws) = Empty
ar = sh.Range("b2:t" & ws)
For i = 2 To UBound(ar)
If Trim(ar(i, 2)) <> "" And Trim(ar(i, 3)) <> "" Then
s = Trim(ar(i, 2)) & "|" & Trim(ar(i, 3))
d(s) = i
End If
Next i
f = Dir(ThisWorkbook.Path & "\*.xls*")
Do While f <> ""
If f <> ThisWorkbook.Name Then
Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & f, 0)
With wb.Worksheets(1)
rs = .Cells(Rows.Count, 2).End(xlUp).Row
br = .Range("b2:t" & rs)
For i = 2 To UBound(br)
s_1 = Trim(br(i, 2)) & "|" & Trim(br(i, 3))
m = d(s_1)
If m <> "" Then
For j = 4 To UBound(br, 2)
ar(m, j) = br(i, j)
Next j
End If
Next i
End With
wb.Close False
End If
f = Dir
Loop
sh.Range("b2:s" & ws) = ar
Application.ScreenUpdating = True
MsgBox "ok!"
End Sub
|
|