针对问题,不建议使用嵌套,下面代码供参考。
- Sub Test()
- Dim nRow%, Arr(), Brr(), cTxt$
- Dim d As Object, m%, n%
- With Sheets("Sheet1")
- nRow = .Range("a1").End(xlDown).Row
- Arr = .Range("A1:d" & nRow).Value
- Set d = CreateObject("scripting.dictionary")
- ReDim Brr(1 To nRow, 1 To 5)
- For i = 2 To nRow
- cTxt = Arr(i, 1) & "," & Arr(i, 2) & "," & Arr(i, 3)
- If Not d.exists(cTxt) Then
- m = m + 1
- d(cTxt) = m
- Brr(m, 1) = Arr(i, 1)
- Brr(m, 2) = Arr(i, 2)
- Brr(m, 3) = Arr(i, 3)
- Brr(m, 4) = Arr(i, 4)
- Brr(m, 5) = 1
- Else
- n = d(cTxt)
- Brr(n, 4) = Brr(n, 4) + Arr(i, 4)
- Brr(n, 5) = Brr(n, 5) + 1
- End If
- Next
- nRow = .Range("h1048576").End(xlUp).Row
- If nRow > 1 Then
- .Range("h2:l" & nRow).ClearContents
- End If
- .Range("h2:l" & m + 1).Value = Brr
- End With
- End Sub
复制代码
|