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