VBA compiler can't see For statement? (1 Viewer)

Philocthetes

Has a great deal to learn
Local time
Today, 16:01
Joined
Dec 19, 2017
Messages
28
Hi folks,

When I try to step through the code below, I see get "Compile error: Next without For." But I have a For line. What am I or the compiler missing?

Code:
Dim Titles
Dim Counter As Long

    Range("A1").Select
    ActiveCell.Columns("A:A").EntireColumn.Select

For Counter = 1 To 941
    
    Selection.Find(What:="Title", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Range("A1:A2").Select
    Set Titles = Cells.Find(What:="Subtitle", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
If Titles Is Nothing Then
    ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "Subtitle"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "None"
    ActiveCell.Offset(1, -1).Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Else: Next Counter
 

jdraw

Super Moderator
Staff member
Local time
Today, 19:01
Joined
Jan 23, 2006
Messages
15,364
In many cases the error may show on the line before the error. I don't see an End IF in your code, but that may be it.
 

Philocthetes

Has a great deal to learn
Local time
Today, 16:01
Joined
Dec 19, 2017
Messages
28
Thanks, jdraw.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:01
Joined
Feb 19, 2002
Messages
42,976
1. putting code on the same line as the If or the Else just makes the code harder to read. It doesn't save anything.
2. The Next needs to be OUTSIDE the If statement.
 

MarkK

bit cruncher
Local time
Today, 16:01
Joined
Mar 17, 2004
Messages
8,178
Just tidying up your code commonly helps find errors. Consider a more consistent indenting scheme, like...
Code:
    Dim Titles
    Dim Counter As Long

    Range("A1").Select
    ActiveCell.Columns("A:A").EntireColumn.Select

    For Counter = 1 To 941
        Selection.Find(What:="Title", After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate
        ActiveCell.Range("A1:A2").Select
        Set Titles = Cells.Find(What:="Subtitle", After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate
        
        If Titles Is Nothing Then
            ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
            Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            ActiveCell.Select
            ActiveCell.FormulaR1C1 = "Subtitle"
            ActiveCell.Offset(0, 1).Range("A1").Select
            ActiveCell.FormulaR1C1 = "None"
            ActiveCell.Offset(1, -1).Range("A1").Select
        Else
        
[COLOR="Green"]        'there is obviously a problem here
[/COLOR]        
    Next Counter
hth
Mark
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 18:01
Joined
Feb 28, 2001
Messages
27,001
I think Mark has shown you the error, but I'll explain it. You have a "scope" error. The FOR statement and its corresponding NEXT statement must be in the same scope. Scope can sometimes refer to the visibility of global variables and code entry points. But it can ALSO mean "in the same block of code" where a block of code is defined by things that act to isolate it.

For instance, in this code example, look carefully at the block numbers.

Code:
...
[COLOR="Red"]bunch of code in block 0[/COLOR]
...
IF X>Y THEN
   [COLOR="SeaGreen"]bunch of code in block 1[/COLOR]
ELSE
   ...
   [COLOR="RoyalBlue"]bunch of code in block 2[/COLOR]
   ...
   IF W>Z THEN
      [COLOR="Purple"]bunch of code in block 3[/COLOR]
   ELSE
      [COLOR="Sienna"]bunch of code in block 4[/COLOR]
   END IF

   ...
   [COLOR="RoyalBlue"]More code in block 2[/COLOR]
   ...
END IF
...
[COLOR="Red"]More code in block 0[/COLOR]

There are other ways to establish blocks. For example the code between CASE clauses within a SELECT CASE / END SELECT grouping are individual blocks.

In general, the compiler doesn't know and doesn't care whether a NEXT statement is in a block that will be executed. In essence, it ALWAYS assumes the worst - that it WON'T be executed and thus the corresponding FOR is deemed to be unmatched within its block.

So what you have is a NEXT in a different block than its corresponding FOR and that is why you had trouble.
 

Users who are viewing this thread

Top Bottom