|
参与一下,看看是不是这个意思。用到dic.remove语句。- Sub 汇总()
- Application.ScreenUpdating = False
- Set dic = CreateObject("scripting.dictionary")
- arr = Sheets("数据源").Range("a1").CurrentRegion
- For i = 2 To UBound(arr)
- If Trim(arr(i, 5)) <> "" Then
- Key = Trim(arr(i, 5)) & "," & Trim(arr(i, 6)) & "," & Trim(arr(i, 7)) _
- & "," & Trim(arr(i, 10)) & "," & Trim(arr(i, 22))
- If Not dic.exists(Key) Then
- dic(Key) = Array(arr(i, 5), arr(i, 6), arr(i, 7), arr(i, 10), Val(arr(i, 18)), Val(arr(i, 19)), arr(i, 22), arr(i, 14))
- Else
- k1 = dic(Key)(4): k2 = dic(Key)(5): k3 = dic(Key)(7)
- dic(Key) = Array(arr(i, 5), arr(i, 6), arr(i, 7), arr(i, 10), k1 + Val(arr(i, 18)), k2 + Val(arr(i, 19)), arr(i, 22), k3 & ";" & arr(i, 14))
- End If
- End If
- Next
- For Each ss In dic.keys
- If dic(ss)(4) = 0 Then
- dic.Remove (ss)
- End If
- Next
- With Sheets("汇总2")
- .[a1].CurrentRegion.Offset(1) = Empty
- .[a2].Resize(dic.Count, 8) = Application.Rept(dic.items, 1)
- End With
- End Sub
复制代码
|
|