Runtime error subscript out of range

[SBF12345]

Greetings,

I am running a chunk of code that uses a loop.

Dim A As WorksheetDim B As String


For Each A In Sheets(Array("sheet1", "sheet2"))


    B = A.Name

I am receiving the error in the title on the For Each loop line.

Is the For Each loop structured correctly, or might I just have an error in the names as they appear in the array as compared to the workbooks. The worksheet names are different than they appear in the code above.

 

[Fazza]

maybe

Dim i As Long
Dim A As Worksheet
Dim B As String
Dim arWksNames As Variant


arWksNames = Array("sheet1", "sheet2")


For i = LBound(arWksNames) To UBound(arWksNames)
    Debug.Print i, arWksNames(i), Worksheets(arWksNames(i)).Name
    
    Set A = Worksheets(arWksNames(i))
    With A
        'do something with the worksheet
    End With
    
Next i
Set A = Nothing

 

[Caleeco]

An alternative bit of code to try.

Sub Loop_Sheets()
    Dim ws As Worksheet
    Dim B As String
    
    For Each ws In Worksheets
        B = ws.Name
        If B = "Sheet1" Or B = "Sheet2" Then
            'Run some code
        End If
    Next ws
End Sub

Regards
Caleeco

 

[Norie]

If you don't have 2 sheets with the tab names 'Sheet1' and 'Sheet2' in the active workbook you'll get the error you describe.

 

[Fazza]

maybe best to explain what is being done.
as there may be a better approach

such as if you want to loop through worksheets,

for each wks in worksheets
debug.print wks.name
next wks

 

[SBF12345]

Why are you choosing a structure that uses a long counter as the basis of the loop? is there an advantage to this structure rather than an approach that uses a For Each loop and string as the basis for the loop?

 

[Fazza]

Why are you choosing a structure that uses a long counter as the basis of the loop? is there an advantage to this structure rather than an approach that uses a For Each loop and string as the basis for the loop?

for looping through an array, expect
for i = lbound(...) to ubound(...)
next i

whereas "for each" loop is for objects, like
for each wks in worksheets
for each shp in shapes
for each onecell in range
for each onerow in range.rows
for each onecol in range.columns
for each onearea in range.areas
for each pt in pivottables

the original string idea is unusual

but, best approach depends on what you want to do...

PS
alternative to "for each", not preferred, can be
for i = 1 to worksheets.count
'do something to worksheets(i)
next i

 

[Fazza]

alternative to "for each", not preferred, can be
for i = 1 to worksheets.count
'do something to worksheets(i)
next i

a further thought on that.
some collections are not indexed from 1 to the count, but 0 to count - 1
such as

for i = 1 to recordset.fields.count
debug.print recordset.fields(i - 1).name
next i

another reason to prefer for each for collections
for each fld in recordset.fields