EXCEL VBA这个代码怎么写的?

Question

C列是统计G:N列范围内,每行大于0的数 相连 的情况.
把每行大于0的数,相连 数量最多的返回出来.
请问这个用快速的返回的办法怎么解决?
请大师们看一下.

Answer 1

都以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

二者运行截图

没有使用几十万行数据测试，不知道哪种快。

Answer 2

示例数据：

程序代码：

程序文本：

---

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

本回答被提问者采纳

Answer 3

上文件好调试。