Share

“Next Without For” Compile Error in Excel VBA – What Does it Mean and How do You Fix it?

“Next Without For” Compile Error is a very common compile-time error in Excel VBA. It implies that a Next statement must always have a preceding For statement that matches. If a Next statement is used without a corresponding For statement, this error is generated.

Let us look at some most common causes of the error and way to fix and avoid them.

Example 1: If statement without a corresponding “End If” statement

Sub noEndIf()
    Dim rng As Range
    Dim cell As Range
    
    Set rng = ActiveSheet.Range("B1:B10")
    For Each cell In rng
    If cell.Value = 0 Then
    cell.Interior.color = vbRed
    Else
    cell.Interior.color = vbGreen
    Next cell
    
End Sub

Every If statement (and If Else Statement) must have a corresponding End If statement along with it. As you can see in the above code, End If is missing after the Else block, causing the error. The right way to do it is

Sub withEndIf()
    Dim rng As Range
    Dim cell As Range
    
    Set rng = ActiveSheet.Range("B1:B10")
    For Each cell In rng
        If cell.Value = 0 Then
            cell.Interior.color = vbRed
        Else
            cell.Interior.color = vbGreen
        End If
    Next cell
    
End Sub

Example 2: Incorrect sequence of End If and Next statements

Sub incorrectEndIf()
    Dim rng As Range
    Dim cell As Range
    
    Set rng = ActiveSheet.Range("B1:B10")
    For Each cell In rng
    If cell.Value = 0 Then
    cell.Interior.color = vbRed
    Else
    cell.Interior.color = vbGreen
    Next cell
    End If
End Sub

Here, the End If statement is not placed correctly causing overlapping as shown below:

For
If
Next
End If

The entire If statement (including, If, Else and End If statements), must be placed withing the For…Next block as shown below

Sub correctEndIf()
    Dim rng As Range
    Dim cell As Range
    
    Set rng = ActiveSheet.Range("B1:B10")
    For Each cell In rng
        If cell.Value = 0 Then
            cell.Interior.color = vbRed
        Else
            cell.Interior.color = vbGreen
        End If
    Next cell
    
End Sub

Example 3: With statement has a corresponding End With Statement missing

Sub noEndWith()

    Dim counter As Integer
    Dim lastRow As Integer
    Dim fName As String, lName As String, fullName As String
    
    lastRow = 10
    
    For counter = 1 To lastRow
    
    With ActiveSheet
    fName = .Cells(counter, 1).Value
    lName = .Cells(counter, 2).Value
    
    fullName = fName & " " & lName
    'Further processing here
        
    Next counter

End Sub

Just like an If statement, the With statement should also have a corresponding End With statement, without which error will be thrown. The working example:

Sub withEndWith()
    Dim counter As Integer
    Dim lastRow As Integer
    Dim fName As String, lName As String, fullName As String
    
    lastRow = 10
    
    For counter = 1 To lastRow
    
        With ActiveSheet
            fName = .Cells(counter, 1).Value
            lName = .Cells(counter, 2).Value
        End With
        
        fullName = fName & " " & lName
        'Further processing here
        
    Next counter

End Sub

Example 4: Overlapping For and If Else statement

Say, in the example below, you want to do some processing only if a condition is false. Else you want to continue with the next counter of the For loop

Sub overlapping()
    Dim counter As Integer
    For counter = 1 To 10
        If Cells(counter, 1).Value = 0 Then
            Next counter
        Else
            'Do the processing here
        End If
    Next counter

End Sub

Note: as in other programming languages, VBA does not have a continue option for a loop. When the control of the program reaches the first “Next counter” statement after the If statement — it finds that there is a Next statement within the If statement. However, there is no corresponding For statement within this If Block. Hence, the error.

So, you can use one of the two solutions below:

Simply remove the “next” statement after If

Sub solution1()
    Dim counter As Integer
    For counter = 1 To 10
        If Cells(counter, 1).Value = 0 Then
            'Simply don't do anything here
        Else
            'Do the processing here
        End If
    Next counter

End Sub

OR

Not the if condition and place your code there. Else condition is not required at all

Sub solution2()
    Dim counter As Integer
    For counter = 1 To 10
        If Not Cells(counter, 1).Value = 0 Then
            'Not the if condition and
            'Do the processing here
        End If
    Next counter
End Sub

The bottom line is that the “If, Else, End If statement block” must be completely within the For loop.

Avoiding the Next without For error by using standard coding practices

The best way to avoid this error is to follow some standard practices while coding.

1. Code indentation: Indenting your code not only makes it more readable, but it helps you identify if a loop / if statement / with statement are not closed properly or if they are overlapping. Each of your If statements should align with an End If, each For statement with a Next, each With statement with an End With and each Select statement with an End Select

2. Use variable name with Next: Though the loop variable name is not needed with a next statement, it is a good practice to mention it with the Next statement.

So, change

 Next 

to

 Next counter 

This is particularly useful when you have a large number of nested for Loops.

3. As soon as you start a loop, write the corresponding end statement immediately. After that you can code the remaining statements within these two start and end statements (after increasing the indentation by one level).

If you follow these best practices, it is possible to completely and very easily avoid this error in most cases.

Leave a Reply

Your email address will not be published. Required fields are marked *