代码之家 › 专栏 › 技术社区 › Error 1004

将数组从UBound循环到LBound并检查值

vba excel

Error 1004 · 技术社区 · 7 年前

我正在尝试创建一个数组,从 UBound 到 LBound 并使用以下代码检查值。

我在第行收到一个错误:

 If arrPart(i) = strResult Then

运行时错误9

我尝试在数组中导入的范围:

代码:

    Option Explicit

    Sub ArrayTest()

        Dim LastColumn As Long, CounterPart As Long, i As Long
        Dim arrPart As Variant
        Dim strResult As String

        With ThisWorkbook.Worksheets("Sheet1")

            LastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column

            strResult = "N"

            'Set as an array the 4 last matches
            arrPart = .Range(Cells(1, LastColumn - 3), Cells(1, LastColumn))

            CounterPart = 0

            For i = UBound(arrPart) To LBound(arrPart) Step -1

                If arrPart(i) = strResult Then
                    CounterPart = CounterPart + 1
                Else
                    Exit For
                End If

            Next

        End With

    End Sub

有什么建议吗?

2 回复 | 直到 7 年前

Scott Craner 7 年前

根据以上所有评论:

Option Explicit

Sub ArrayTest()

    Dim LastColumn As Long, CounterPart As Long, i As Long
    Dim arrPart As Variant
    Dim strResult As String

    With ThisWorkbook.Worksheets("Sheet1")

        LastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column

        strResult = "N"

        'Set as an array the 4 last matches
        arrPart = .Range(.Cells(1, 1), .Cells(1, LastColumn))

        CounterPart = 0

        For i = UBound(arrPart, 2) To LBound(arrPart, 2) Step -1

            If arrPart(1, i) = strResult Then
                CounterPart = CounterPart + 1
            Else
                Exit For
            End If

        Next

    End With

    Debug.Print CounterPart
End Sub

John Alexiou 7 年前

B4 .

这就是如何找出表的大小,将值传递到数组中,并对它们进行迭代。

Public Sub ArrayTest()

    Dim r_start As Range
    Set r_start = Range("B4")

    Dim i As Long, n As Long
    n = Range(r_start, r_start.End(xlToRight)).Columns.Count

    Dim arrPart() As Variant
    arrPart = r_start.Resize(1, n).Value

    Dim strResult As String
    strResult = "N"

    Dim counter As Long
    counter = 0

    For i = 1 To n
        If arrPart(1, i) = strResult Then
            counter = counter + 1
        Else
            Exit For
        End If
    Next i

    Debug.Print counter

End Sub

推荐文章