|
代码更新一下。。。
- Sub ykcbf() '//2024.5.24
- Dim arr, brr, s
- Set d = CreateObject("Scripting.Dictionary")
- Set d1 = CreateObject("Scripting.Dictionary")
- Set sh = ThisWorkbook.Sheets("奖金发放汇总表")
- For Each sht In Sheets
- If Val(sht.Name) Then
- With sht
- r = .Cells(Rows.Count, 3).End(3).Row
- arr = .Range("a6:n" & r)
- For i = 1 To UBound(arr)
- If arr(i, 3) <> Empty And arr(i, 3) <> "小计" Then
- s = Trim(arr(i, 1))
- d1(s) = ""
- s = .Name & "|" & arr(i, 1)
- d(s) = Application.WorksheetFunction.Round(arr(i, 13), 2)
- End If
- Next
- End With
- End If
- Next
- ReDim brr(1 To d.Count, 1 To 2)
- For Each k In d1.keys
- If k <> Empty Then
- m = m + 1
- brr(m, 1) = m
- brr(m, 2) = k
- End If
- Next
- With sh
- .UsedRange.Offset(4).Clear
- .[a3].Resize(m, 2) = brr
- arr = .[a1].Resize(m + 2, 15)
- For i = 3 To UBound(arr)
- For j = 2 To UBound(arr, 2) - 1
- s = arr(2, j) & "|" & arr(i, 2)
- If d.exists(s) Then
- .Cells(i, j) = d(s)
- End If
- Next
- .Cells(i, "o") = Application.WorksheetFunction.Sum(.Cells(i, 3).Resize(1, 12))
- Next
- .Cells(m + 3, 1) = "合计"
- .Cells(m + 3, 3).Resize(1, 13).FormulaR1C1 = "=SUM(R3C:R" & "[-1]C)"
- lr = 3
- r = .Cells(Rows.Count, 1).End(3).Row
- .Rows(lr & ":" & lr).Copy
- .Rows(lr + 1 & ":" & r).PasteSpecial Paste:=xlPasteFormats
- .Cells(m + 3, 1).Resize(1, 2).Merge
- .Cells(lr, 1).Select
- ActiveWindow.DisplayZeros = False
- End With
- Set d = Nothing
- Set d1 = Nothing
- Application.ScreenUpdating = False
- MsgBox "OK!"
- End Sub
复制代码
|
评分
-
1
查看全部评分
-
|