方法10
此方案明显是方法9的数组版。方法8、9、10的思路基本一致,差别在于分别用字典、单元格操作及数组实现。
Public Sub p10()
Dim rng As Range, i, j, max, ar, br(), cr, n, r
[d:e] = ""
Set rng = Range([a2], [a65536].End(3))
ar = Range([a1], [b65536].End(3))
max = Evaluate("MAX(COUNTIF(" & rng.Address & "," & rng.Address & "))")
ReDim br(1 To max * 5, 1 To 2)
For i = 1 To max ‘通过双层循环将组序号与1234的连接字符串读入数组第一列
n = n + 5
For j = 4 To 1 Step -1
br(n - j, 1) = n / 5 & "," & 5 - j
Next
Next
cr = Application.Index(br, , 1)
n = 1
For i = 2 To UBound(ar)
If ar(i, 1) <> (Val(ar(i - 1, 1)) + 1) Then n = n + 1 ‘如果数组相邻值不连接,则组序号加1
r = Application.Match(n & "," & ar(i, 1), cr, 0) ‘用MATCH查找确定行号,查找值为组序号与1234的连接字符串
br(r, 2) = ar(i, 2)
Next
[d1].Resize(UBound(br), 2) = br
[d:d].Replace "*,", "", 2 ‘用替换删除组序号
End Sub
|