|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
- Sub test()
- Dim r%, i%
- Dim arr, brr
- Dim d As Object
- Set d = CreateObject("scripting.dictionary")
- With Worksheets("data")
- r = .Cells(.Rows.Count, 1).End(xlUp).Row
- arr = .Range("a2:c" & r)
- For i = 1 To UBound(arr)
- If Not d.exists(arr(i, 1)) Then
- m = 1
- ReDim brr(1 To 2, 1 To m)
- Else
- brr = d(arr(i, 1))
- m = UBound(brr, 2) + 1
- ReDim Preserve brr(1 To 2, 1 To m)
- End If
- brr(1, m) = arr(i, 2)
- brr(2, m) = arr(i, 3)
- d(arr(i, 1)) = brr
- Next
- End With
- With Worksheets("程序")
- ls = .Range("g2")
- .UsedRange.Offset(4, 0).Clear
- m = 8
- n = 2
- rmax = 0
- For Each aa In d.keys
- arr = d(aa)
- ReDim brr(1 To UBound(arr, 2), 1 To UBound(arr))
- For i = 1 To UBound(arr)
- For j = 1 To UBound(arr, 2)
- brr(j, i) = arr(i, j)
- Next
- Next
- If rmax < UBound(brr) Then
- rmax = UBound(brr)
- End If
- With .Cells(m, n)
- .Value = aa
- .Resize(1, 2).Merge
- .Interior.Color = 10192433
- .NumberFormatLocal = "m月d日"
- End With
- .Cells(m + 1, n) = "员工号"
- .Cells(m + 1, n + 1) = "姓名"
- With .Cells(m + 1, n).Resize(1, 2)
- .Interior.Color = 15261367
- End With
- .Cells(m + 2, n).Resize(UBound(brr), UBound(brr, 2)) = brr
- With .Cells(m, n).Resize(UBound(brr) + 2, 2)
- .Borders.LineStyle = xlContinuous
- .BorderAround LineStyle:=xlContinuous, Weight:=xlMedium
- End With
- n = n + 3
- If n > 14 Then
- n = 2
- m = m + rmax + 4
- rmax = 0
- End If
- Next
- With .UsedRange
- .HorizontalAlignment = xlCenter
- .VerticalAlignment = xlCenter
- End With
- End With
- End Sub
复制代码 |
|