error collecting, sorting data in Arrays in Excel

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

VB Code:

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

Welcome to VBForums! :wave:

The line with the error is a problem because you arent adding a new worksheet - you are simply trying to activate it. To add a new one (which should be added just before that line), you can do this:

VB Code:

With Workbooks("aegis_reporting.xls")
      .WorkSheets.Add
      .WorkSheets(.WorkSheets.Count).Name = myReportName & "_Sort"
    End With

As to removing Activecell, here's an example. You can change this:

VB Code:

Range(colParamArray(1) & mysteryRow).Select
            myRow = ActiveCell.Value

To this:

VB Code:

myRow = Range(colParamArray(1) & mysteryRow).Value

Note that as you are joining strings, this would be (slightly) more efficient like this:

VB Code:

myRow = Cells(mysteryRow, colParamArray(1)).Value

Rather than using mysteryRow (and the ActiveCell) to get the row, why arent you using your loop variable (rowControl) instead?

Si,
Thanks so much for the help. It creates worksheet just fine, though now It's also creating several other sheets: Sheet1, Sheet2, Sheet3 in addition to myReportName & "_Sort". (they are blank)

Also, when I tried to delete it at a later time with:

VB Code:

With Workbooks("aegis_reporting-TEST.xls")
          .Worksheets(myReportName & "_Sort").Delete
    End With

it prompts users with a msg box on wether they want to delete a sheet that might have data in it. Any suggestions to disable the msgbox.

Thanks again for your -- I'm actually thinking now with the amount of reports that I have to create I might restart this project in Access -- I will ask opinions on design in another post.
Thx :bigyello:

You can disable alerts before you delete the sheet, just make sure you re-enable them after the delete.

VB Code:

Application.DisplayAlerts = False
With Workbooks("aegis_reporting-TEST.xls")
    .Worksheets(myReportName & "_Sort").Delete
End With
Application.DisplayAlerts = True