error collecting, sorting data in Arrays in Excel

[freddy231]

Hi there,
I'm a newbie with some prog exp but I inherited this code and can't get it to run -- seems like something with the contents of the arrays, or maybe i'm missing code to "close" the arrays, if such a thing is needed - -everything runs fine until the second to last line, when I try and create a new worksheet where I can dump all the data I've accumulated in arrays, in order to sort and filter it. (Gives me runtime error 9 - subscript out of range)
The way this prog works - it has a wksheet with reports listed along with checkboxes - If a report is checked, prog will go to another column on that row and pick up the necessary columns it will need to look up in a datadump worksheet. It will go to that worksheet and place all records with "TRV"
in a column and pick up 3 pieces of data in other colums and save the results to TRVsecurities(TRVcount) = myRow.
Sorry for the long email -- any help would be greatly appreciated.
BTW any help with getting rid of these Active.cell statements or better/easier design would greatly be appreciated.
Fred

Sub runReport8(ByVal rowIndx As Integer)
 
    Workbooks("aegis_reporting.xls").Activate
    Worksheets("Reports").Activate
    Range("A" & rowIndx).Select
    
    Dim rowControl2 As Integer
    Dim leftOffOn As Integer
    
    Dim myReportName As String
    myReportName = ActiveCell.Value
    
    Dim mySheetName As String
    Range("G" & rowIndx).Select
    mySheetName = ActiveCell.Value
    
    Dim columnParameters As String
    Dim colParamArray() As String
    Range("H" & rowIndx).Select
    columnParameters = ActiveCell.Value
    colParamArray = Split(columnParameters, ",")
    'B,C,J,P,Q
    '0,1,2,3,4
    
    MsgBox columnParameters
    
    Worksheets(mySheetName).Activate
    Range("B2").Select
    
    Dim lastRow As Integer
    
    ActiveCell.End(xlDown).Select
    lastRow = ActiveCell.Row
    
    Range(colParamArray(0) & "2").Select
    
    Dim rowControl, mysteryRow As Integer
    Dim USCPAcount, MMFcount, TRVcount As Integer
    
    Dim TRVsecurities(1000) As String
    Dim myRow As String
    
    For rowControl = 2 To lastRow
        mysteryRow = ActiveCell.Row
        
        If ActiveCell.Text = "TRV" Then
           ' Range(colParamArray(2) & ActiveCell.Row).Value - _
           ' Range(colParamArray(3) & ActiveCell.Row).Value
            
            myRow = ActiveCell.Value
            Debug.Print myRow
            TRVcount = TRVcount + 1
            Range(colParamArray(1) & mysteryRow).Select
            myRow = ActiveCell.Value
            Debug.Print myRow
            Range(colParamArray(2) & mysteryRow).Select
            myRow = myRow & ":" & ActiveCell.Value
            Debug.Print myRow
            Range(colParamArray(3) & mysteryRow).Select
            myRow = myRow & ":" & ActiveCell.Value
            Debug.Print myRow
            TRVsecurities(TRVcount) = myRow
            Debug.Print myRow
            Range(colParamArray(0) & mysteryRow).Select
        End If
   
        ActiveCell.Offset(1, 0).Select
    Next rowControl
    
    'START TRV block
    
    Workbooks("aegis_reporting.xls").Activate
[COLOR=DarkGreen]    Worksheets(myReportName & "_Sort").Activate[/COLOR]
    ' Gives me runtime error 9 - subscript out of range 
    Call clearSheet