0

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. The data is not sorted based on the Strings so I might have String a, String b, String a, in a column and I want both String a's to be a part of the same new sheet. Before I added a few lines of code to account for this everything was working fine, but now I'm getting an application defined or object defined error at an if statement that shouldn't be related to the added code. Here it is:

Sub FilterByClass()
Dim i As Long
Dim j As Long
Dim sheetName As String
Dim sheet As Worksheet
Dim book As Workbook
Dim k As Integer

ActiveSheet.Name = "AllClasses"
sheetName = Worksheets("AllClasses").Cells(2, 1).Value
Worksheets.Add
ActiveSheet.Name = sheetName
Worksheets("AllClasses").Activate
ActiveSheet.Rows("1:2").Copy
Worksheets(sheetName).Paste
j = 3
k = 0
For i = 3 To Rows.Count
    If Worksheets("AllClasses").Cells(i, 1).Value <> Worksheets("AllClasses").Cells(i - 1, 1).Value     Then        //site of error
        Worksheets("AllClasses").Range("1:1," & j & ":" & (i - 1)).Copy
        Worksheets(Worksheets("AllClasses").Cells((i - 1), 1).Value).Paste
        j = i
        sheetName = Worksheets("AllClasses").Cells(i, 1).Value
        For Each sheet In ActiveWorkbook         //new added code block
            If sheetName = sheet.Name Then k = 1
            Next sheet
         If k = 1 Then k = 0
         Else
             Worksheets.Add
             ActiveSheet.Name = sheetName
             Worksheets("AllClasses").Activate
         End If
  Next i
  End Sub

Any help would be greatly appreciated. ¸

Community
  • 1
  • 1
djar
  • 69
  • 1
  • 1
  • 9
  • Your end goal is very similar to the challenge presented here, which includes multiple recommendations from many contributors: http://stackoverflow.com/questions/23713289/splitting-data-into-different-sheets-by-column-values/23794531#23794531 – Dan Wagner Jul 17 '14 at 19:34
  • If the value of `i` is 1, then `Worksheets("AllClasses").Cells(i - 1, 1)` is undefined/invalid, which will raise that error. – David Zemens Jul 17 '14 at 19:43
  • But I define the value of i as 3 right away so shouldn't that avoid the error? – djar Jul 17 '14 at 19:59
  • Can either of you help me understand why this error would occur only after adding the indicated new code block? – djar Jul 17 '14 at 20:10
  • OK so this happens in the first iteration of the loop? Does `Worksheets("AllClasses").Cells(i, 1).offset(-1,0).Value` give the same error? – David Zemens Jul 17 '14 at 20:20
  • Still getting the same error and yes in the first iteration of the loop – djar Jul 17 '14 at 20:37
  • When I tried copying the exact same code and running it from my personal macro workbook I got an error in the Else statement by `ActiveSheet.Name = sheetName` saying that the sheet name was already in use so now I'm really confused – djar Jul 17 '14 at 20:51

1 Answers1

0

I've noticed a few things wrong with your code that are easier to point out in an answer rather than a comment.

1. Code not valid   --Ignore this--

You seem to be missing 2 End Ifs in the code you posted. I can only assume it's just a copy-paste error, so I'll swiftly move on.

2. Line with comment "new added code block"

Your code says:

For Each sheet In ActiveWorkbook

you should replace that with this:

For Each sheet In ActiveWorkbook.Worksheets

The Workbook is not a collection of sheets, the workbook's .Worksheets function is.

3. Termination of the outer For loop

In the comments, you said that the original error happens in the first iteration of your loop inside the If statement, but I'm not convinced. Having had a quick play with your code, I think the error you're seeing ('1004' : "Application-defined or object-defined error") is a result of a different problem.

In my run-through of your code, this line in the Else block of If k = 1:

ActiveSheet.Name = sheetName

caused the error. That is because this line:

sheetName = Worksheets("AllClasses").Cells(i, 1).Value

returned sheetName = "".

This situation happens when a cell in position Cells(i, 1) is empty, which is entirely possible in your code since your outer For loop is iterating over all rows in the "AllClasses" sheet -- all 1048576 of them (in Excel 2007 and later versions). Unless you've got a value in every single row's column 1 (which I doubt), then at some point you'll meet a cell that is blank. Assigning that blank string to ActiveSheet.Name will cause the error you're seeing.

You can either hardcode the value of your outer For loop's terminating condition or you can use the various "tricks" to dynamically determine that value, e.g. Sheet.UsedRange.Rows.Count or Sheet.Cells(1048576, col).End(xlUp).row.

Community
  • 1
  • 1
djikay
  • 10,450
  • 8
  • 41
  • 52
  • I've fixed points 2 and 3 and thanks for that but point 1 was actually not a copy and paste error, and I'm still getting the same error. The VBA compiler gives me an error when I try to add more end If statements saying that I need a matching if block. From what I saw online I was under the impression that an if statement without an else doesn't require an end if. But I'm noticing now that the result of my code before the error seems to be pairing my first if statement with that else statement at the very end. Excuse my ignorance on such a basic issue, but syntactically how do I fix this? – djar Jul 18 '14 at 03:58
  • I think my first point was invalid. I was misled by the single-line `If` statements. Normally, I like to separate my `If`s across multiple lines and match then with an `End If` for clarity, but it's not a requirement. Edited my answer to avoid confusion. – djikay Jul 18 '14 at 07:13
  • About why you're still getting an error, it's hard to say for sure, as I don't have your data so I can only approximate the behaviour of your code. However if you ensure all your `If`s have an associated `End If`, then you might figure it out. Looking at your code, with my limited understanding about what it does, I think the line `If k = 1 Then k = 0` should be matched with the `Else` that follows it *but it currently isn't* (it matches the `If` you highlighted as the error, just below your outer `For` loop). So, "if k = 1 then set k to 0, ELSE Worksheets.Add, etc.". – djikay Jul 18 '14 at 07:31