|
Sub 字典汇总()
Dim d As Object
Set d = CreateObject("scripting.dictionary")
With Sheets("得到的新表样式")
.UsedRange.Offset(3) = Empty
y = .Cells(3, Columns.Count).End(xlToLeft).Column
ar = Range(.Cells(1, 1), .Cells(5000, y))
For j = 4 To UBound(ar, 2)
If Trim(ar(1, j)) = "" Then
ar(1, j) = ar(1, j - 1)
End If
Next j
For j = 4 To UBound(ar, 2)
s = Trim(ar(1, j)) & "|" & Trim(ar(3, j))
d(s) = j
Next j
k = 3
For Each sh In Sheets
If sh.Name <> "需求" And sh.Name <> "得到的新表样式" Then
br = sh.[a1].CurrentRegion
For j = 1 To UBound(br, 2)
If Trim(br(1, j)) = "" Then
br(1, j) = br(1, j - 1)
End If
Next j
For i = 4 To UBound(br)
If Trim(br(i, 2)) <> "" And Trim(br(i, 3)) <> "" Then
s_1 = Trim(br(i, 2)) & "|" & Trim(br(i, 3))
t = d(s_1)
If t = "" Then
k = k + 1
d(s_1) = k
t = k
ar(k, 1) = k
ar(k, 2) = br(i, 2)
ar(k, 3) = br(i, 3)
For j = 4 To UBound(br, 2)
If Trim(br(3, j)) <> "" Then
m = d(Trim(br(1, j)) & "|" & Trim(br(3, j)))
If m <> "" Then
ar(k, m) = br(i, j)
End If
End If
Next j
End If
End If
Next i
End If
Next sh
.[a1].Resize(k, UBound(ar, 2)) = ar
End With
End Sub
|
|