本帖最后由 laoau138 于 2017-6-7 21:28 编辑
Sub 用VBA数组计算多列最大连续次数2()
Dim d As Object, arA, arB, i%, x%, s$, s1$
Set d = CreateObject("Scripting.Dictionary")
arA = [f25:f29]
arB = [g1:i22]
For x = 1 To UBound(arB, 2)
For i = 1 To UBound(arA)
d(arA(i, 1)) = arA(i, 1) & 1
Next
For i = 2 To UBound(arB)
s = Left(arB(i - 1, x), 1) & Mid(arB(i - 1, x), 3, 1)
s1 = Left(arB(i, x), 1) & Mid(arB(i, x), 3, 1)
If d.Exists(s1) And s = s1 Then d(s1) = s & Val(Mid(d(s1), 3, Len(d(s)))) + 1
Next
Cells(25, x + 6).Resize(d.Count, 1) = Application.Transpose(d.Items)
d.RemoveAll
Next
End Sub
'用VBA把字典改写数组 计算多列最大连续
你原来的代码是错的。
- Sub aaa()
- Dim arr, brr, i&, j&, k&, l&, n&, s$
- arr = [g1].CurrentRegion
- brr = [f25:f29]
- ReDim Preserve brr(1 To 5, 1 To UBound(arr, 2) + 1)
- For j = 1 To UBound(arr, 2)
- For i = 1 To UBound(arr) - 1
- For k = 1 To UBound(brr)
- s = Left(arr(i, j), 1) & Mid(arr(i, j), 3, 1)
- If brr(k, 1) = s Then
- n = 1
- For l = i + 1 To UBound(arr)
- If s = Left(arr(l, j), 1) & Mid(arr(l, j), 3, 1) Then n = n + 1 Else Exit For
- Next l
- If brr(k, j + 1) < n Then brr(k, j + 1) = n
- Exit For
- End If
- Next k
- i = l - 1
- Next i
- Next j
- For i = 1 To 5
- For j = 2 To UBound(brr, 2)
- brr(i, j) = brr(i, 1) & brr(i, j)
- Next j
- Next i
- [f25].Resize(5, UBound(brr, 2)) = brr
- End Sub
复制代码
|