|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
- Sub test()
- Dim r%, i%
- Dim arr, brr
- Dim d As Object
- Set d = CreateObject("scripting.dictionary")
- With Worksheets("sheet1")
- r = .Cells(.Rows.Count, 1).End(xlUp).Row
- arr = .Range("a4:i" & r)
- End With
- For i = 1 To UBound(arr)
- If Not d.exists(arr(i, 7)) Then
- Set d(arr(i, 7)) = CreateObject("scripting.dictionary")
- End If
- xm = Left(arr(i, 3), 1)
- If xm = "李" Or xm = "王" Then
- If Not d(arr(i, 7)).exists(xm) Then
- m = 1
- ReDim brr(1 To UBound(arr, 2), 1 To m)
- Else
- brr = d(arr(i, 7))(xm)
- m = UBound(brr, 2) + 1
- ReDim Preserve brr(1 To UBound(arr, 2), 1 To m)
- End If
- For j = 1 To UBound(arr, 2)
- brr(j, m) = arr(i, j)
- Next
- d(arr(i, 7))(xm) = brr
- End If
- Next
- With Worksheets("sheet2")
- .UsedRange.Offset(3, 0).Clear
- m = 4
- For Each aa In d.keys
- If d(aa).Count = 2 Then
- For Each bb In d(aa).keys
- brr = d(aa)(bb)
- ReDim crr(1 To UBound(brr, 2), 1 To UBound(brr))
- For i = 1 To UBound(brr)
- For j = 1 To UBound(brr, 2)
- crr(j, i) = brr(i, j)
- Next
- Next
- .Cells(m, 1).Resize(UBound(crr), UBound(crr, 2)) = crr
- m = m + UBound(crr)
- Next
- End If
- Next
- End With
- End Sub
复制代码 |
|