|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
2024.2.23代码更新。- Sub ykcbf() '//2024.2.23
- Dim arr, d, zr
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- Set d = CreateObject("Scripting.Dictionary")
- Set d1 = CreateObject("Scripting.Dictionary")
- Set ws = ThisWorkbook
- Set sh = ws.Sheets("考核汇总")
- With sh
- r = .Cells(Rows.Count, "j").End(3).Row
- zrr = .Range("j2:j" & r)
- End With
- For i = 1 To UBound(zrr)
- k = zrr(i, 1)
- d1(k) = ""
- Next
- On Error Resume Next
- For Each sht In ws.Sheets
- If sht.Name <> sh.Name Then
- If IsArray(sht.UsedRange) Then
- With sht
- fn = .Name
- arr = .UsedRange
- c = .Rows(2).Find("小计", , , , , 1).Column
- End With
- p4 = IIf(InStr(fn, "自查"), "自查", "")
- For i = 3 To UBound(arr)
- If d1.exists(arr(i, 2)) Then
- s = arr(i, 2) & "|" & arr(i, 3) & "|" & arr(i, 4) & "|" & arr(i, 5) & "|" & p4
- d(s) = arr(i, c)
- End If
- Next
- End If
- End If
- Next
- ReDim brr(1 To d.Count, 1 To 8)
- For Each k In d.keys
- m = m + 1
- b = Split(k, "|")
- brr(m, 1) = m
- brr(m, 2) = b(0)
- brr(m, 3) = b(1)
- brr(m, 4) = b(2)
- brr(m, 5) = b(3)
- brr(m, 6) = d(k)
- brr(m, 8) = b(4)
- Next
- With sh
- .[a3:h10000].UnMerge
- .[a3:h10000].Clear
- With .[a3].Resize(m, 8)
- .Value = brr
- .Borders.LineStyle = 1
- .HorizontalAlignment = xlCenter
- .VerticalAlignment = xlCenter
- End With
- .[a3].Resize(m, 8).Sort .[b3], 1
- zr = Array(1, 2, 7)
- Call hb(3, 2, zr)
- Call hbsum(3, 4, 6, 7)
- End With
- Set d = Nothing
- Application.ScreenUpdating = True
- MsgBox "OK!"
- End Sub
复制代码
|
|