Home > Mobile >  VBA Looping through an array but getting runtime error 9 subscript out of range
VBA Looping through an array but getting runtime error 9 subscript out of range

Time:09-05

I have some experience with C/PHP but am not that fluent in VBA. I have a list of 30 items in excel, each with particular specs and I am just going down the list, comparing each item's specs pairwise against those below it. If there's a match, I put a 1 in the column next to the item. But I'm getting a runtime error 9 subscript out of range (I don't think I'm exceeding line 30 or column 5 at any point). It occurs at the line where I check if the conditions are fulfilled:

If data(rowi, 1) = data(rowj, 1) And data(rowi, 2) = data(rowj, 2) And Abs(data(rowi, 5) = data(rowj, 5)) = 2 Then

Sub test()
    Dim data As Variant
    Dim rowi As Integer
    Dim rowj As Integer
    
    data = Range("B2:F31").Value
        For rowi = 0 To 28
        For rowj = rowi   1 To 29
            If data(rowi, 1) = data(rowj, 1) And data(rowi, 2) = data(rowj, 2) And Abs(data(rowi, 5) = data(rowj, 5)) = 2 Then
                    Cells(rowi, 11).Value = 1
            End If
        Next rowj
    Next rowi
End Sub

CodePudding user response:

Find Matches Below

Option Explicit

Sub test()
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    Dim rg As Range: Set rg = ws.Range("B2:F31")
    Dim rCount As Long: rCount = rg.Rows.Count
    Dim rOffset As Long: rOffset = rg.Row - 1
    
    Dim Data() As Variant: Data = rg.Value
    
    Dim ri As Long
    Dim rj As Long
    
    For ri = 1 To rCount - 1
        For rj = ri   1 To rCount
            If Data(ri, 1) = Data(rj, 1) _
                    And Data(ri, 2) = Data(rj, 2) _
                    And Abs(Data(ri, 5) - Data(rj, 5)) = 2 Then
                ws.Cells(ri   rOffset, 11).Value = 1
                Exit For
            End If
        Next rj
    Next ri

End Sub
  • Related