EXCEL VBA这个代码怎么写的?
把每行大于0的数,相连 数量最多的返回出来.
请问这个用快速的返回的办法怎么解决?
请大师们看一下. 展开
都以9至13行为例
一、普通循环方法
Sub Cycle()
Dim i As Long
Dim j As Long
Dim MaxCnt As Long
Dim arr As Variant
Dim Cell As Range
For Each Cell In Range("G9:G13")
arr = Application.Transpose(Application.Transpose(Cell.Resize(1, 8)))
Cells(Cell.Row, "C").Clear
For i = LBound(arr) To UBound(arr)
MaxCnt = 0
For j = i To UBound(arr)
If arr(j) > 0 Then
MaxCnt = MaxCnt + 1
Else
Exit For
End If
Next j
Cells(Cell.Row, "C") = Application.Max(MaxCnt, Cells(Cell.Row, "C"))
Next i
Next Cell
End Sub
二、递归方法
Sub TrasfRecursion()
Dim Cell As Range
For Each Cell In Range("G9:G13")
Cells(Cell.Row, "C").Clear
Call Recursion(Cell.Resize(1, 8))
Next
End Sub
Sub Recursion(Rng As Range)
Dim Cntius As Long
Dim arr As Variant
Dim i As Long
If Rng.Count = 1 Then
arr = Array(Rng.Value)
Else
arr = Application.Transpose(Application.Transpose(Rng))
End If
For i = LBound(arr) To UBound(arr)
If arr(i) <> 0 Then
Cntius = Cntius + 1
Else
Exit For
End If
Next
Cells(Rng.Row, "C") = Application.Max(Cntius, Cells(Rng.Row, "C"))
If Rng.Count > 1 Then
Recursion Rng.Offset(0, 1).Resize(1, Rng.Columns.Count - 1)
End If
End Sub
二者运行截图
没有使用几十万行数据测试，不知道哪种快。
示例数据：
程序代码：
程序文本：
Option Explicit
Sub 宏1()
Dim arrC%(), arrG, n&, i&, j&, s$
n = Cells(Rows.Count, "G").End(xlUp).Row - 8 'G列从9行开始的行数
ReDim arrC%(1 To n, 1 To 1)
arrG = Range("g9").Resize(n, 8)
For i = 1 To n
s = ""
For j = 1 To UBound(arrG, 2)
s = s & IIf(arrG(i, j) > 0, "1", "0")
Next j
arrC(i, 1) = maxN(s)
Next i
Range("c9").Resize(n, 1) = arrC
End Sub
Function maxN(s$) As Integer
Dim arr, i&, n&
arr = Split(s, "0")
For i = LBound(arr) To UBound(arr)
If Len(arr(i)) > n Then n = Len(arr(i))
Next i
maxN = n
End Function
相当可以,老师.
就是如果G列为空值时候,就不需要返回了,这个应该怎么修改一下?是不是在哪里加个判断?
因为最大n有公式,但是空值
希望一次性说清楚，下面的语句：
For i = 1 To n
s = ""
For j = 1 To UBound(arrG, 2)
s = s & IIf(arrG(i, j) > 0, "1", "0")
Next j
arrC(i, 1) = maxN(s)
Next i
修改为：
For i = 1 To n
if arrG(i,1) "" then '这里的不等于好像百度会吃
s = ""
For j = 1 To UBound(arrG, 2)
s = s & IIf(arrG(i, j) > 0, "1", "0")
Next j
arrC(i, 1) = maxN(s)
end if
Next i
