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

[si_the_geek]

Welcome to VBForums!

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:

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:

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

To this:

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

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

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?

[freddy231]

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:

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

[DKenny]

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

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