习惯性动作,上传代码:
- Sub 字典循环汇总数据()
- Dim d As Object, mRow As Long, arr, ZR(), i As Long, XR()
- Dim m As Long: m = 0: Dim h As Long: h = 0
- Set d = CreateObject("Scripting.Dictionary")
- With Sheet1
- .Activate
- mRow = .Range("A1048576").End(3).Row
- arr = .Range("A2:C" & mRow).Value
- ReDim ZR(1 To UBound(arr), 1 To 2)
- For i = 1 To UBound(arr)
- If arr(i, 1) <> "" Then
- If InStr(arr(i, 2), "卖出") Then arr(i, 3) = Val(arr(i, 3)) * -1
- If d.Exists(arr(i, 1)) Then
- h = d(arr(i, 1))
- ZR(h, 2) = ZR(h, 2) + Val(arr(i, 3))
- Else
- m = m + 1
- d(arr(i, 1)) = m
- ZR(m, 1) = arr(i, 1)
- ZR(m, 2) = arr(i, 3)
- End If
- End If
- Next
- ReDim XR(1 To UBound(ZR), 1 To 2)
- m = 0
- For i = 1 To UBound(ZR)
- If Val(ZR(i, 2)) <> 0 Then
- m = m + 1
- XR(m, 1) = ZR(i, 1)
- XR(m, 2) = ZR(i, 2)
- End If
- Next
- With .Range("H2")
- .Resize(4 ^ 8, 2).ClearContents
- .Resize(d.Count, 2).Value = XR
- End With
- End With
- Set d = Nothing: Erase arr: Erase ZR: Erase XR
- End Sub
复制代码
|