Subscript out of range when trying to loop through array to read values

Question

I have a string of predefined worksheets, that I need to run specific code for. I get a compile error.

The code is set up to copy data from one sheet to another.
How do I do the same for multiple sheets?

When I step through the code sht is showing the MHP60,MHP61,MHP62 and not just MHP60.

I get a subscript out of range error.

Sub Prepare_CYTD_Report()
    Dim addresses() As String
    Dim addresses2() As String
    Dim SheetNames() As String
    Dim SheetNames2() As String
    Dim wb1 As Workbook, wb2 As Workbook
    Dim my_Filename
    
    'Declare variables for MHP60, MHP61, MHP62 Trial Balance Values
    Dim i, lastcol As Long
    Dim tabNames, cell As Range
    Dim tabName As String
    Dim sht As Variant
  
    addresses = Strings.Split("A9,A12:A26,A32:A38,A42:A58,A62:A70,A73:A76,A83:A90", ",") 'Trial Balance string values
    addresses2 = Strings.Split("G9,G12:G26,G32:G38,G42:G58,G62:G70,G73:G76,G83:G90", ",")  'Prior Month string values
    SheetNames = Strings.Split("MHP60,MHP61,MHP62")
    'SheetNames2 = Strings.Split("MHP60-CYTDprior,MHP61-CYTDprior,MHP62-CYTDprior")
    
    Set wb1 = ActiveWorkbook    'Revenue & Expenditure Summary Workbook
    
    '*****************************Open CYTD files
    my_Filename = Application.GetOpenFilename(fileFilter:="Excel Files,*.xl*;*.xm*", Title:="Select File to create CYTD Reports")

    If my_Filename = False Then
        Exit Sub
    End If

    Application.ScreenUpdating = False
    Set wb2 = Workbooks.Open(my_Filename)
    
    '*****************************Load Column Header Strings & Copy Data
    For Each sht In SheetNames
        lastcol = wb1.Sheets(sht).Cells(5, Columns.Count).End(xlToLeft).Column
    
        On Error Resume Next
        Set tabNames = wb1.Sheets(sht).Cells(4, 3).Resize(1, lastcol - 2).SpecialCells(xlCellTypeConstants)
        'actual non-formula text values on row 4 from column C up to column lastCol'
        On Error GoTo 0
        If Err.Number <> 0 Then
            MsgBox "No headers were found on row 4 of MHP60", vbCritical
            Exit Sub
        End If
    
        For Each cell In tabNames
        tabName = Strings.Trim(cell.Value2)
        'dedicated variable in case of requirement for further parsing (space/comma elimination?)'
            If CStr(wb1.Sheets(sht).Evaluate("ISREF('[" & wb2.Name & "]" & tabName & "'!$A$1)")) = "True" Then
            'If wb2 has a tab named for the value in tabName
                For i = 0 To UBound(addresses)
                    wb2.Sheets(tabName).Range(addresses(i)).Value2 = wb1.Sheets(sht).Range(addresses(i)).Offset(0, cell.Column - 1).Value2
                    'Debug.Print "data for " & wb2.Sheets(tabName).Range(addresses(i)).Address(, , , True) & " copied from " & wb1.Sheets("MHP60").Range(addresses(i)).Offset(0, cell.Column - 1).Address(, , , True)
                Next i
            Else
                Debug.Print "A tab " & tabName & " was not found in " & wb2.Name
            End If
        Next cell
    Next sht
      
    MsgBox "CYTD Report Creation Complete", vbOKOnly
        
    Application.ScreenUpdating = True
End Sub

The default delimiter parameter for Split is the space: (" "). "MHP60,MHP61,MHP62" has no spaces in it. You forgot to specify the comma delimiter. — BigBen
– BigBen, Commented May 19, 2022 at 14:47
Thanks for catching that. I'm updating code now to see if it fixes the error. — smrmodel78
– smrmodel78, Commented May 19, 2022 at 16:09

Pᴇʜ · Accepted Answer · 2022-05-19 14:30:16Z

1

Split by what?

SheetNames = Strings.Split("MHP60,MHP61,MHP62")

Split by comma? Then use the following instead:

SheetNames = Strings.Split("MHP60,MHP61,MHP62", ",")

Alternative

Dim SheetNames() As Variant  ' needs to be Variant to work with Array()
SheetNames = Array("MHP60", "MHP61", "MHP62")

This should be quicker as your macro does not need to split the string and has it as array directly.

answered May 19, 2022 at 14:30

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Excel Hero Over a year ago

I'd like to see some timing data to back that up. Array() is a function and not a compile-time value, like CONST. I doubt it is any faster, especially with a short list of data.

Pᴇʜ Over a year ago

@ExcelHero Well, it was just an assumption. But I tested it, theres no significant difference between both methods if repeated 100.000 times.

Collectives™ on Stack Overflow

Subscript out of range when trying to loop through array to read values

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related