CODEWITHSUNDEEP
excelvba
Error 1004
Error 1004

Reputation: 8220

Loop Array from UBound to LBound and check values

I'm trying to create an array, loop from UBound to LBound and check values with the below code.

I 'm receiving an error on line:

 If arrPart(i) = strResult Then

Run time error 9

The range I try to import in array:

enter image description here

Code:

    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

any suggestions?

Upvotes: 2

Views: 3393

Answers (2)

John Alexiou
John Alexiou

Reputation: 29244

Suppose you have a table of cells starting from B4.

scren

This is how you find out the size of the table, transfer the values into an array and iterate through them.

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

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152505

Per all the comments above:

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

Upvotes: 2

Related Questions