|
重新编了一下代码,各汇总表里的年班自动生成。原来是根据已有的年班汇总的,有疏漏。- Sub 汇总()
- Dim arr, x&, dk, k%
- Dim sh As Worksheet
- Dim d As Object
- Set d = CreateObject("scripting.dictionary")
- For Each sh In Worksheets
- If Not sh.Name Like "*汇总*" Then
- r = sh.[c65536].End(3).Row
- arr = sh.Range("a1:e" & r)
- For x = 1 To UBound(arr)
- If arr(x, 1) Like "*年*" Then bj = arr(x, 1) '班级名
- If Len(arr(x, 5)) > 0 And IsNumeric(arr(x, 5)) Then '判断第5列为数值,则累计加1
- xkey = sh.Name & bj '字典key为学校+年班
- d(xkey) = d(xkey) + 1
- End If
- Next
- End If
- Next
-
- dk = d.keys: dt = d.items
- ReDim crr(1 To d.Count, 1 To 5)
- For Each sh In Worksheets
- If sh.Name Like "*汇总*" Then
- sh.Range("a2:e1000").ClearContents
- n = 0
- For i = 0 To UBound(dk)
- xkey = dk(i)
- If Left(sh.Name, 2) = Mid(xkey, 3, 2) Then
- n = n + 1
- crr(n, 1) = xkey
- crr(n, 2) = Left(xkey, 2)
- crr(n, 3) = Mid(xkey, 3)
- crr(n, 4) = dt(i)
- crr(n, 5) = crr(n, 4) - Round(crr(n, 4) * 0.1 + 0.49999, 0)
- End If
- Next
- sh.[a2].Resize(n, 5) = crr
- End If
- Next
- End Sub
复制代码 |
|