|
发表于 2016-5-18 22:26
|
显示全部楼层
本楼为最佳答案
lhj323323 发表于 2016-5-18 22:00
谢谢老师
就是按股票代码提取不重复值,这些数据的时间归属是2016年的。
仔细看了一下,原来在加入字典的时候,前期的原值没有赋值给数组,所以导致有的数据没有汇总,在上面的代码添加三行代码即可- Sub 交割()
- Dim Myr%, x, k
- Dim arr, ayy()
- Application.ScreenUpdating = False
- Set d = CreateObject("scripting.dictionary")
- With Sheet14
- Myr = .Range("C65536").End(xlUp).Row
- arr = .Range("A2:L" & Myr)
- End With
- ReDim ayy(1 To UBound(arr), 0 To 5)
-
- For x = 1 To UBound(arr)
- If arr(x, 1) = 2016 Then
- If Not d.exists(arr(x, 3) * 1) Then '提取不重复股票代码
- k = k + 1
- ayy(k, 0) = arr(x, 3) '股票代码
- ayy(k, 1) = arr(x, 4) '股票名称
- ayy(k, 2) = arr(x, 7)
- ayy(k, 3) = arr(x, 12)
- ayy(k, 4) = 1
- d.Add arr(x, 3) * 1, k '将股票代码纳入字典管理
- Else
- ayy(d(arr(x, 3) * 1), 2) = ayy(d(arr(x, 3) * 1), 2) + arr(x, 7) '股数
- ayy(d(arr(x, 3) * 1), 3) = ayy(d(arr(x, 3) * 1), 3) + arr(x, 12) '发生金额,含税费
- ayy(d(arr(x, 3) * 1), 4) = ayy(d(arr(x, 3) * 1), 4) + 1 '交易次数
- End If
- End If
-
- Next x
-
- With Sheet15 '结果表
- .[s:w].Clear 'Contents
- .Columns("s:s").NumberFormatLocal = "000000"
- .Range("s2").Resize(k, UBound(ayy, 2)) = ayy
- End With
- Myr = 0
- Erase arr: Erase ayy
- x = 0: k = 0
- d.RemoveAll
- Application.ScreenUpdating = True
- End Sub
复制代码 |
|