|
发表于 2015-1-6 15:43
|
显示全部楼层
本楼为最佳答案
本帖最后由 香川群子 于 2015-1-6 22:23 编辑
- Sub test()
- Dim ar, br&(), cr&(), i&, j&, k&, m&, n&, r&, t&, cnt&, tms#
- tms = Timer
-
- ar = Sheet1.[a1].CurrentRegion
- m = UBound(ar): n = UBound(ar, 2)
-
- ReDim br&(1 To m, 1 To n)
- For j = 1 To n
- ReDim cr&(99, m)
- For i = 3 To m
- t = ar(i, j): k = cr(t, 0) + 1: cr(t, 0) = k: cr(t, k) = i
- Next
-
- cnt = 0
- For i = 0 To 99
- If cr(i, 0) > 1 Then
- For k = 1 To cr(i, 0)
- cnt = cnt + 1
- t = cr(i, k)
- 'br(cnt, j) = ar(t - 2, j) & ar(t - 1, j) & ar(t, j)
- '……1位数时会产生漏0拼接错误 如 19 26 6 成为 019266 而不是期望的192606
- br(cnt, j) = ar(t - 2, j) * 10000 + ar(t - 1, j) * 100 + ar(t, j) '改成这样计算就没问题了。
- Next
- End If
- Next
- If cnt > r Then r = cnt
- Next
-
- Sheet2.Activate
- [a1].CurrentRegion = ""
- [a1].Resize(r, n).NumberFormat = "000000"
- [a1].Resize(r, n) = br
- MsgBox Format(Timer - tms, "0.000s")
- End Sub
复制代码 |
评分
-
查看全部评分
|