Subscript Out Of Range trying to Find a Worksheet

[makavity]

I have a spreadsheet that pulls from another source that creates different worksheets based on sizes. Some sizes will not be found from sheet to sheet, so I'm trying to write a macro (with my VERY LIMITED knowledge) to pull some infomation from a sheet.

I'm using For Next loops to go through the range of sizes, however since some sizes will not be created as a worksheet for that brand, I'm trying to write a "Does the worksheet exist" before trying to activate the sheet.

Here's the code I have so far:

For A% = 175 To 350 Step 5
For B% = 20 To 100 Step 5
For C% = 10 To 30

SheetName = (A% & "-" & B% & "-" & C%)
SheetExists = False
On Error GoTo NextSheet
If Len(Sheets(SheetName).name) > 0 Then
SheetExists = True
Else
GoTo NextSheet
End If

I don't have any input on how the original worksheet is pulled, and am only trying to get info from the sheet.

When I run the macro, the Error Goto NextSheet works, but on the second loop I get a "Runtime Error 9 Subscript Out Of Range" error at the line: If Len(Sheets(SheetName).name) > 0 Then

Any ideas?

 

[Zathras]

It has to do with the way VBA error handling works. After the first error takes you to the code at NextSheet: everything else is considered to be code that is handling the error until a Resume statement is found. The second error is therefore not able to be handled and the "hard" error is given.

Try adding a couple lines of code after the End If:

For A% = 175 To 350 Step 5
        For B% = 20 To 100 Step 5
            For C% = 10 To 30
                
                SheetName = (A% & "-" & B% & "-" & C%)
                SheetExists = False
                On Error GoTo NextSheet
                If Len(Sheets(SheetName).Name) > 0 Then
                    SheetExists = True
                Else
                    GoTo NextSheet
                End If
NextSheet:
                Resume Next

 

[makavity]

Thank you so much!

This was exactly the problem.