- Sub test()
- Sheets("汇总").Activate
- Dim d, sh As Worksheet, arr, brr(1 To 1000, 1 To 100), i&, m&, n&, s$, w
- Set d = CreateObject("Scripting.Dictionary")
- m = 1: n = 2
- brr(1, 1) = "工号": brr(1, 2) = "姓名"
- For Each sh In Worksheets
- If sh.Name <> "汇总" Then
- n = n + 1
- brr(1, n) = sh.Name
- arr = sh.Range("c1", sh.[a65536].End(3)).Value
- For i = 2 To UBound(arr)
- s = arr(i, 1) & arr(i, 2)
- w = IIf(arr(i, 3) = "视频", 1, 2.5)
- If Not d.exists(s) Then
- m = m + 1
- d(s) = m
- brr(m, 1) = arr(i, 1)
- brr(m, 2) = arr(i, 2)
- brr(m, n) = w
- Else
- brr(d(s), n) = w
- End If
- Next
- End If
- Next
- [g1].Resize(m, n) = brr
- End Sub
复制代码 |