- Sub test()
- Dim arr, brr(), d, i, n, x, y, z
- Set d = CreateObject("scripting.dictionary")
- arr = Sheets("总表").Range("a3:o" & Range("a65536").End(xlUp).Row)
- ar = Sheets("总表").Range("a1:k5")
- For i = 4 To UBound(arr)
- If Not d.exists(arr(i, 3)) Then
- d(arr(i, 3)) = ""
- For n = 4 To UBound(arr)
- If arr(n, 3) = arr(i, 3) Then
- x = x + 1
- y = Range("a4:o4").Find(arr(i, 3)).Column
- ReDim Preserve brr(1 To 11, 1 To x)
- For z = 1 To 10
- brr(z, x) = arr(n, z)
- Next
- If x > 1 Then
- brr(11, x) = brr(11, x - 1) + arr(n, 4) + arr(n, 5) + arr(n, 6) + arr(n, 7) + arr(n, 8) + arr(n, 9)
- Else
- brr(11, 1) = arr(3, y)
- brr(11, x) = brr(11, x) + arr(n, 4) + arr(n, 5) + arr(n, 6) + arr(n, 7) + arr(n, 8) + arr(n, 9)
- End If
- End If
- Next
- Set ws = Worksheets.Add(after:=Sheets(Sheets.Count))
- With ws
- .Name = arr(i, 3)
- .Range("a1").Resize(5, UBound(ar, 2)) = ar
- .Range("k4") = arr(2, y)
- .Range("k5") = arr(3, y)
- .Range("a1:k1").Merge
- .Range("a2:k2").Merge
- For x = 1 To 10
- .Range(.Cells(3, x), .Cells(4, x)).Merge
- Next
- .Range("a1:k4").HorizontalAlignment = xlCenter
- .Range("a6").Resize(UBound(brr, 2), 11) = Application.Transpose(brr)
- .Range("a:a").NumberFormatLocal = "yyyy-m-d"
- x = .Range("a65536").End(xlUp).Row + 1
- .Range("a" & x) = "合计"
- .Range(.Cells(x, 1), .Cells(x, 6)).Merge
- End With
- Erase brr
- x = 0
- End If
- Next
- End Sub
复制代码 合计那里自己用函数算一下好了,VBA不太会搞 |