|
参与一下。。。- Sub ykcbf() '//2024.2.20
- Dim arr, d
- Application.ScreenUpdating = False
- Set d = CreateObject("Scripting.Dictionary")
- Set ws = ThisWorkbook
- Set Sh = ws.Sheets("考核汇总")
- For Each sht In ws.Sheets
- If sht.Name <> Sh.Name Then
- With sht
- r = .Cells(.Rows.Count, "a").End(xlUp).Row
- fn = .Name
- arr = .[a1].Resize(r, 5)
- End With
- p4 = IIf(InStr(fn, "自查"), "自查", "")
- For i = 3 To UBound(arr)
- s = arr(i, 2): ss = arr(i, 4)
- If Not d.exists(s) Then Set d(s) = CreateObject("Scripting.Dictionary")
- d(s)(ss) = Array(arr(i, 3), arr(i, 4), arr(i, 5), p4)
- Next
- End If
- Next
- With Sh
- arr = .UsedRange
- For i = 3 To UBound(arr) Step 5
- s = arr(i, 2)
- For Each k In d.keys
- x = 0
- If s = k Then
- For Each kk In d(k).keys
- x = x + 1
- .Cells(i + x - 1, 4) = kk
- .Cells(i + x - 1, 3) = d(k)(kk)(0)
- .Cells(i + x - 1, 5) = d(k)(kk)(2)
- .Cells(i + x - 1, 7) = d(k)(kk)(3)
- Next
- Else
- .Cells(i + x - 1, 4) = ""
- .Cells(i + x - 1, 3) = ""
- .Cells(i + x - 1, 5) = ""
- .Cells(i + x - 1, 7) = ""
- End If
- Next
- Next
- End With
- Set d = Nothing
- Application.ScreenUpdating = True
- MsgBox "OK!"
- End Sub
复制代码
|
|