Subscript out of range error?

[inkbird1]

Hi all, getting the error " Runtime error 9 - Subscript out of range"


Private Sub Worksheet_Activate()
Dim arrIncidents As Variant
Dim arrActive As Variant
Dim lngRow As Long
Dim cnt As Long

arrIncidents = Sheets("Incidents").Range("A1").CurrentRegion.Value

ReDim arrActive(1 To 6, 1 To UBound(arrIncidents, 1))

For lngRow = 2 To UBound(arrIncidents, 1)

If arrIncidents(lngRow, 1) = Range("B3").Value Then
cnt = cnt + 1
arrActive(1, cnt) = arrIncidents(lngRow, 1)
arrActive(2, cnt) = arrIncidents(lngRow, 2)
arrActive(3, cnt) = arrIncidents(lngRow, 3)
arrActive(4, cnt) = arrIncidents(lngRow, 4)
arrActive(5, cnt) = arrIncidents(lngRow, 5)
arrActive(6, cnt) = arrIncidents(lngRow, 6)
End If

Next lngRow

ReDim Preserve arrActive(1 To 6, 1 To cnt)

With ListBox100
.ColumnCount = 6
.Column = arrActive
.ColumnWidths = "100,100,150,200,200,300"


End With

End Sub

 

[Michael M]

Is it this line ?

arrIncidents = Sheets("Incidents").Range("A1").CurrentRegion.Value

Try

arrIncidents = Sheets("Incidents").Range("A1").Value

Also, make sure the sheet name is correct, both in syntax AND speeling....no leading or trailling spaces either

 

[inkbird1]

VBA pointing to ReDim Preserve arrActive(1 To 6, 1 To cnt)

 

[Trebor76]

I'd say the long cnt variable is zero as there are no records in Col. A of the Incidents sheet that match what's in cell B3 of the active sheet.

 

[inkbird1]

I'd say the long cnt variable is zero as there are no records in Col. A of the Incidents sheet that match what's in cell B3 of the active sheet.

This is correct this error is occuring when there is no data in Column A -
How can i overcome this?

 

[Trebor76]

As long as there's no other issue(s) try this:

Private Sub Worksheet_Activate()
    
    Dim arrIncidents As Variant
    Dim arrActive As Variant
    Dim lngRow As Long
    Dim cnt As Long
    
    arrIncidents = Sheets("Incidents").Range("A1").CurrentRegion.Value
    
    ReDim arrActive(1 To 6, 1 To UBound(arrIncidents, 1))
    
    For lngRow = 2 To UBound(arrIncidents, 1)
        If arrIncidents(lngRow, 1) = Range("B3").Value Then
            cnt = cnt + 1
            arrActive(1, cnt) = arrIncidents(lngRow, 1)
            arrActive(2, cnt) = arrIncidents(lngRow, 2)
            arrActive(3, cnt) = arrIncidents(lngRow, 3)
            arrActive(4, cnt) = arrIncidents(lngRow, 4)
            arrActive(5, cnt) = arrIncidents(lngRow, 5)
            arrActive(6, cnt) = arrIncidents(lngRow, 6)
        End If
    Next lngRow
    
    If cnt > 0 Then
        ReDim Preserve arrActive(1 To 6, 1 To cnt)
        With ListBox100
            .ColumnCount = 6
            .Column = arrActive
            .ColumnWidths = "100,100,150,200,200,300"
        End With
    End If

End Sub