|
Sub 字典合并数据()
Dim ar As Variant
Dim d As Object, dc As Object
Set d = CreateObject("scripting.dictionary")
With Sheets("提取数据")
y = .Cells(1, Columns.Count).End(xlToLeft).Column
.[a1].CurrentRegion.Offset(1) = Empty
ar = .Range(.Cells(1, 1), .Cells(100000, y))
For j = 2 To UBound(ar, 2)
d(ar(1, j)) = j
Next j
k = 1
For Each sh In Sheets
If sh.Index > 1 Then
br = sh.[a1].CurrentRegion
For i = 2 To UBound(br)
If br(i, 2) <> "" Then
k = k + 1
ar(k, 1) = sh.Name
For j = 1 To UBound(br, 2)
lh = d(br(1, j))
If lh <> "" Then
ar(k, lh) = br(i, j)
End If
Next j
End If
Next i
End If
Next sh
.[a1].Resize(k, UBound(ar, 2)) = ar
End With
MsgBox "ok!"
End Sub
|
|