本帖最后由 独奏 于 2014-2-23 19:33 编辑
- Sub test()
- Dim arr, brr(), d, i, n, x, y, z
- Set d = CreateObject("scripting.dictionary")
- arr = Sheet1.Range("a3:o" & Range("a65536").End(xlUp).Row)
- ar = Sheet1.Range("a3:j4")
- 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(2, UBound(ar, 2)) = ar
- .Range("a3").Resize(UBound(brr, 2), 11) = Application.Transpose(brr)
- .Range("a:a").NumberFormatLocal = "yyyy-m-d"
- End With
- Erase brr
- x = 0
- End If
- Next
- End Sub
复制代码 |