|
参与一下。。。
- Sub ykcbf() '//2024.6.4
- Set d = CreateObject("Scripting.Dictionary")
- Set d1 = CreateObject("Scripting.Dictionary")
- Set d2 = CreateObject("Scripting.Dictionary")
- Set sh = ThisWorkbook.Sheets("汇总")
- ReDim brr(1 To 10000, 1 To 4)
- For Each sht In Sheets
- If sht.Name <> sh.Name Then
- With sht
- r = .Cells(Rows.Count, 1).End(3).Row
- c = .UsedRange.Columns.Count
- arr = .[a1].Resize(r, c)
- End With
- For j = 5 To UBound(arr, 2) - 6
- s = arr(1, j)
- d1(s) = ""
- Next
- For i = 2 To UBound(arr)
- s = arr(i, 2)
- If Not d2.exists(s) Then
- m = m + 1
- d2(s) = m
- For j = 1 To 4
- brr(m, j) = arr(i, j)
- Next
- End If
- For j = 5 To UBound(arr, 2)
- If arr(1, j) <> Empty Then
- s = arr(i, 2) & "|" & arr(1, j)
- d(s) = d(s) + arr(i, j)
- End If
- Next
- Next
- End If
- Next
- With sh
- .UsedRange.Offset(1).Clear
- c = .UsedRange.Columns.Count
- .[e1:r1] = ""
- .[e1].Resize(, d1.Count) = d1.keys
- .[a2].Resize(m, 4) = brr
- With .[a2].Resize(m, c)
- .Borders.LineStyle = 1
- .HorizontalAlignment = xlCenter
- .VerticalAlignment = xlCenter
- End With
- arr = .[a1].Resize(m + 1, c)
- For i = 2 To UBound(arr)
- For j = 5 To UBound(arr, 2)
- If arr(1, j) <> Empty Then
- s = arr(i, 2) & "|" & arr(1, j)
- If d.exists(s) Then
- .Cells(i, j) = d(s)
- End If
- End If
- Next
- Next
- ActiveWindow.DisplayZeros = False
- End With
- End Sub
复制代码
|
|