|
原代码基本可用。小改了一下,用数组储存显示结果后一次性输出。- Sub test()
- Application.DisplayAlerts = False
- Application.ScreenUpdating = False
- Set dic = CreateObject("scripting.dictionary")
- Set dic1 = CreateObject("scripting.dictionary")
- Set dic2 = CreateObject("scripting.dictionary")
-
- Rng = Sheets(1).[a1].CurrentRegion
-
- For c = UBound(Rng, 2) - 3 To 1 Step -4
- For r = UBound(Rng) To 2 Step -1
- y = Rng(r, c + 1) & "," & Rng(r, c + 2)
- dic(y) = ""
- yy = y & "," & Rng(r, c + 3)
- dic1(yy) = dic1(yy) + 1
- dic2(yy) = dic2(yy) & Rng(r, c) & ","
- Next r
- Next c
-
-
- orng = Sheets(2).[a1].CurrentRegion
- Sheets(2).[a1].CurrentRegion.Replace " 点评", ""
- Rng = Sheets(2).[a1].CurrentRegion
- Sheets(2).[a1].CurrentRegion = orng
-
- For r = 3 To UBound(Rng)
- y = Rng(r, 2) & "," & Rng(r, 3)
- dic(y) = ""
-
- For c = 6 To UBound(Rng, 2)
- yy = y & "," & Rng(1, c) & Rng(2, c)
- dic1(yy) = Rng(r, c)
- Next c
- Next r
-
-
- Sheets(4).Select
- Rows("2:1048576").ClearContents
- otrng = Range("a1:ah1")
- Range("a1:ah1").Replace "重复次数", ""
- Range("a1:ah1").Replace "的时间", ""
- trng = Range("a1:ah1")
- Range("a1:ah1") = otrng
- k = dic.keys
- Dim brr(1 To 10000, 1 To 34) '结果数组
- tr = 1
-
- For i = 0 To dic.Count - 1 '表一内容:重复次数+重复时间
- maxtr = 1
- brr(tr, 1) = Split(k(i), ",")(0) '代码
- brr(tr, 2) = Split(k(i), ",")(1) '名称
- For c = 3 To 20 Step 2
- yy = k(i) & "," & trng(1, c)
- brr(tr, c) = dic1(yy) '重复次数
- w = Split(dic2(yy), ",")
- If dic1(yy) = 1 Then '重复时间,按重复次数逐行往下填充
- brr(tr, c + 1) = w(0)
- Else
- If dic1(yy) > maxtr Then maxtr = dic1(yy)
- For ii = 0 To UBound(w)
- brr(tr + ii, c + 1) = w(ii)
- Next ii
- End If
- Next c
- For c = 21 To 34 '表2内容
- yy = k(i) & "," & trng(1, c)
- brr(tr, c) = dic1(yy)
- Next c
- tr = tr + maxtr '下一股票填写行
- Next i
-
- [a2].Resize(tr, 34) = brr
-
- Application.DisplayAlerts = True
- Application.ScreenUpdating = True
- End Sub
复制代码 |
|