Program goes straight to else statement, then produces Run time error '9' subscript out of range

Question

I'm new to VBA and am trying to design a program that will go through a column with Strings in it and for every unique String name create a new worksheet object with that String value as its name and then copy and paste the values in that row to the new sheet. All identical Strings should then also have the values in their row copied over to the new sheet. I'm not even sure if the program itself works, but before I can check I keep getting an error that I haven't been able to fix.

The error is run time error '9' subscript out of range.

The thing is the new sheet is getting created but is not getting filled up with any data. It's as if the program goes straight to the else statement and then finds an error that I'm not sure how to fix even though it should be going through the If statement at least once because I know that the String in A3 is the same as that in A2. Here's the full code:

Sub FilterByClass()
Dim i As Long
Dim j As Long  
Dim sheetName As String

ActiveSheet.Name = "AllClasses"
sheetName = Worksheets("AllClasses").Cells(2, 1).Value
Worksheets.Add
ActiveSheet.Name = sheetName
Worksheets("AllClasses").Activate
Worksheets(sheetName).Rows(1) = ActiveSheet.Rows(1)
Worksheets(sheetName).Rows(2) = ActiveSheet.Rows(2)
j = 3
For i = 3 To Rows.Count
    If Cells(i, 1).Value = Cells(i - 1, 1).Value Then
        Worksheets(Cells(i, 1).Value).Rows(j) = ActiveSheet.Rows(i)
        j = j + 1
    Else
        Worksheets.Add
        ActiveSheet.Name = ThisWorkbook.Sheets(sheetName).Cells(i, 1).Value
        Worksheets("AllClasses").Activate
        j = 1
        Worksheets(Cells(i, 1).Value).Rows(j) = ActiveSheet.Rows(1)
        j = j + 1
        Worksheets(Cells(i, 1).Value).Rows(j) = ActiveSheet.Rows(i)
    End If
Next i 
End Sub

Any help would be appreciated. And if you see anything in the rest of the code that looks like it clearly won't work as intended please point it out as well. Thanks

Are you certain that worksheet `AllClasses` does not already exist? It may be a hidden or very hidden worksheet, so you would not see it. I'm going to revise your Q slightly because the second error can't happen *before* the first error, yet that is what your code is implying, which makes it kind of confusing to try and help. — David Zemens, Jul 16 '14 at 19:07
also what is the value in the cell `ActiveSheet.Cells(2,1)`? — David Zemens, Jul 16 '14 at 19:09
I believe so, and I tried to change the name to AllClassesjkl just to test if that would produce the same error and it did. And the value in Cells(2,1) is simply 1st Grade. — djar, Jul 16 '14 at 19:14

score 1 · Answer 1 · answered Jul 16 '14 at 19:28

1

Before you name a worksheet, check if the sheet exists like David mentioned in the comments.

Here is my favorite way of checking if the sheet exists

Sub Sample()
    If DoesSheetExist("AllClasses") Then
        MsgBox "Sheet Already Exists"
    Else
        ActiveSheet.Name = "AllClasses"
    End If
End Sub

Function DoesSheetExist(Sh As String) As Boolean
    Dim ws As Worksheet

    On Error Resume Next
    Set ws = ThisWorkbook.Sheets(Sh)
    On Error GoTo 0

    If Not ws Is Nothing Then DoesSheetExist = True
End Function

Also if the sheet doesn't exist then it may be possible that the workbook is protected. To check if that is the case, you can use the below code

If ThisWorkbook.ProtectStructure = True Then
    MsgBox "Workbook structure is protected"
Else
    MsgBox "Workbook structure is not protected"
End If

answered Jul 16 '14 at 19:28

Siddharth Rout

147,039
17
206
250

Strange, when I ran Sub Sample() I received that same "This sheet name is already in use" error even though your code should have prevented that. Also my workbook is not protected – djar Jul 16 '14 at 19:34
No. That's what doesn't make sense. I should have gotten that, but instead got the same error of "This sheet name is already in use." – djar Jul 16 '14 at 19:36
Can you step through the code? ALSO how many workbooks are opened? – Siddharth Rout Jul 16 '14 at 19:36
Just quit excel and tried running the code again. The first error is not appearing anymore, possibly because I closed the worksheet I could not see that already had that name assigned, but now the second error is still a problem – djar Jul 16 '14 at 19:43
To clarify: "The code" was referring to the code I had written, NOT the code you had provided to check if the worksheet was already in existence – djar Jul 16 '14 at 19:43
Just before the line `ActiveSheet.Name = sheetName`, type this `Msgbox len(sheetName) & " " & sheetName` What do you get when you run the code? – Siddharth Rout Jul 16 '14 at 19:46
20 and then the string inside the cell. In this case FirstGradeEnglish1. After that I get the same old error – djar Jul 16 '14 at 19:50
`20 and then the string inside the cell. In this case FirstGradeEnglish1. After that I get the same old error – djar 3 mins ago` The length of `FirstGradeEnglish1` should be `18` and not `20` so your cell has some unacceptable characters. Remove that and try again. – Siddharth Rout Jul 16 '14 at 19:53
My mistake, the String was First Grade English1. Although when I ran the code agin this time the application defined error appeared at this line in the code: – djar Jul 16 '14 at 19:58
ActiveSheet.Name = Cells(i, 1).Value – djar Jul 16 '14 at 19:59
I think it is because you are using `Worksheets.Add` and hence `Cells(i, 1).Value` is referring to the new worksheet and not the old one? Fully qualify the cells. For example `Sheets("SheetName").Cells(i, 1).Value` – Siddharth Rout Jul 16 '14 at 20:02
Still getting the same error. Is there perhaps a simpler and not error-filled way to code what I'm attempting to? – djar Jul 16 '14 at 20:10
What is the exact code that you are using in lieu of `ActiveSheet.Name = Cells(i, 1).Value`. I believe `ActiveSheet.Name = Thisworkbook.Sheets(sheetName).Cells(i, 1).Value` should work – Siddharth Rout Jul 16 '14 at 20:13
Using that exact code I got a run time error '9' subscript out of range error – djar Jul 16 '14 at 20:17
I'm sorry it's confidential. I would think the actual data doesn't really make a difference though... – djar Jul 16 '14 at 20:53
You shouldn't be getting the error. We have almost covered everything so I guess the error is not with the code then. – Siddharth Rout Jul 16 '14 at 20:58
If it helps at all, no data is getting put into the the new sheet getting created and the String in A3 is the same as the string in A2 yet the error takes place in the else statement which shouldn't even be getting reached yet – djar Jul 16 '14 at 21:07
Try this for me. See [THIS](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros/10718179#10718179). Fully qualify your objects like sheets/cells etc and then try again. Do remember to update your question with the new code so that I can see what are you using :) And then we will take it form there? – Siddharth Rout Jul 16 '14 at 21:13
Do you have a hidden sheet? – Jul 17 '14 at 09:59
Not that I know of. I've tried quitting excel then rerunning the code and I've tried unhiding workbooks but the only hidden workbook I have is my personal macro workbook. – djar Jul 17 '14 at 13:49
Changed the code significantly, dealing with new error now. Will post new question – djar Jul 17 '14 at 16:43

Program goes straight to else statement, then produces Run time error '9' subscript out of range

1 Answers1