Compile error next without for

The “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 it. 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.
See also: Compile Error: Expected End of Statement

ТРЕНИНГИ

   Быстрый старт
   Расширенный Excel
   Мастер Формул
   Прогнозирование
   Визуализация
   Макросы на VBA

КНИГИ

   Готовые решения
   Мастер Формул
   Скульптор данных

ВИДЕОУРОКИ

ОНЛАЙН-КУРСЫ

ФОРУМ

   Excel
   Работа
   PLEX

© Николай Павлов, Planetaexcel, 2006-2022
info@planetaexcel.ru

Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.

Техническая поддержка сайта

1. 09-29-2021, 11:13 AM

   
   

   #1

   

   
   Registered User
   

   

   ---

   Compile Error: Next without For
   

   Hi All,

   Can’t seem to figure out why my code isn’t working! The code is suppose to cycle through sheets 2 to 5, and first check if there is anything in cell D14. If nothing is populated in cell D14, then it should move on to the next sheet. If there IS something populated in D14, then the code should continue to populate the simply Vlookup formula in column AN. Thoughts?

   Thanks!

   ‘ Sales_Stage_Definition_Formula Macro
   ‘

   Application.ScreenUpdating = False

   With ActiveWorkbook
   For i = 2 To 5
   Sheets(i).Activate

   If Range(«D14») = «» Then

   Next i
   End If

   Range(«AN14»).Select

   ActiveCell.FormulaR1C1 = «=VLOOKUP(RC[-1],’Sales Stage’!R2C2:R12C5,3,FALSE)»
   Selection.AutoFill Destination:=Range(«AN14:AN» & Range(«D» & Rows.Count).End(xlUp).Row)
   Range(Selection, Selection.End(xlDown)).Select

   Next i

   End With

   Worksheets(«Instructions»).Activate

   Application.ScreenUpdating = True

   End Sub

   Last edited by knykil; 09-29-2021 at 12:03 PM.

   ---

2. 09-29-2021, 11:20 AM

   
   

   #2

   Re: Compile Error: Next without For
   

   Untested as no sample file…

   Good Luck
   I don’t presume to know what I am doing, however, just like you, I too started somewhere…
   One-day, One-problem at a time!!!
   If you feel I have helped, please click on the star to left of post [Add Reputation]
   Also….add a comment if you like!!!!
   And remember…Mark Thread as Solved.
   Excel Forum Rocks!!!
   

   ---

3. 09-29-2021, 11:55 AM

   
   

   #3

   Re: Compile Error: Next without For
   

   ---

4. 09-29-2021, 11:58 AM

   
   

   #4

   Re: Compile Error: Next without For
   

   You have one For i = 2 To 5 but two Next i statements. Hence one of them is missing a For. You can’t have two statements jumping back to the same For.

   Trevor Shuttleworth — Excel Aid

   I dream of a better world where chickens can cross the road without having their motives questioned

   ‘Being unapologetic means never having to say you’re sorry’ John Cooper Clarke

   

   ---

5. 09-29-2021, 12:05 PM

   
   

   #5

   

   
   Registered User
   

   

   ---

   Re: Compile Error: Next without For
   

   All — Thank you for your feedback. I’ve since added an example file. The idea is that the ‘J MFI’ tab should be skipped, while tabs 2, 3 and 5 should have the formula populated in column AN. Being relatively new to VBA, i’m not exactly sure where to put the second FOR statement.

   Thanks.

   ---

6. 09-29-2021, 12:37 PM

   
   

   #6

   Re: Compile Error: Next without For
   

   Surely sales stage should be skipped as well

   ---

7. 09-29-2021, 01:12 PM

   
   

   #7

   

   
   Registered User
   

   

   ---

   Re: Compile Error: Next without For
   

   Originally Posted by sintek
   

   Surely sales stage should be skipped as well

   Sintek — Thanks for the response! Unfortunately, this won’t apply to my situation. The example file is a slim down version of the master file. Each month, there could be different tabs that don’t have any data. Therefore, I cannot specify which tabs to skip over. It needs to run dynamically. Ultimately it will run through tabs 2-26 in the master file. Thoughts?

   ---

8. 09-29-2021, 01:55 PM

   
   

   #8

   

   
   Registered User
   

   

   ---

   Re: Compile Error: Next without For
   

   Originally Posted by TMS
   

   You have one For i = 2 To 5 but two Next i statements. Hence one of them is missing a For. You can’t have two statements jumping back to the same For.

   TMS, i’ve since added an example file. I’m not too familiar with VBA, can you spell it out?

   ---

9. 09-29-2021, 01:56 PM

   
   

   #9

   

   
   Registered User
   

   

   ---

   Re: Compile Error: Next without For
   

   Originally Posted by rorya
   

   Rorya — I’ve since added an excel example. I’m new to VBA, could you spell it out for me?

   ---

10. 09-29-2021, 03:11 PM

    
    

    #10

    Re: Compile Error: Next without For
    

    So then back to initial solution provided skipping «J MFI» tab

    ---

11. 09-30-2021, 07:39 AM

    
    

    #11

    

    
    Registered User
    

    

    ---

    Re: Compile Error: Next without For
    

    Originally Posted by sintek
    

    So then back to initial solution provided skipping «J MFI» tab

    Sintek — Thanks again for the response. I really do appreciate your help. However, with the skip tabs changing each month, I can not call out a specific tab in the code because the next month the J MFI tab could very well have data in it. Thoughts?

    ---

12. 09-30-2021, 07:45 AM

    
    

    #12

    Re: Compile Error: Next without For
    

    You are not being transparent with your requirement…What sheets are you actually wanting to loop and what are you actually wanting to skip?
    You first stated you want to loop shhets and only if D14 has no value then do something…
    Then you say that you want to skip Tab «J MFI»
    Now you say, you don’t know what the tab is going to be…

    Ultimately it will run through tabs 2-26 in the master file

    So just loop 2 to 26????

    Also…No need to quote entire posts…it just clutters the thread…

    Last edited by sintek; 09-30-2021 at 07:49 AM.

    ---

13. 09-30-2021, 07:56 AM

    
    

    #13

    Re: Compile Error: Next without For
    

    ---

14. 09-30-2021, 08:06 AM

    
    

    #14

    

    
    Registered User
    

    

    ---

    Re: Compile Error: Next without For
    

    Rorya — spot on! Thank you so much for your help! Cheers!

    ---

15. 09-30-2021, 08:08 AM

    
    

    #15

    

    
    Registered User
    

    

    ---

    Re: Compile Error: Next without For
    

    Sintek — I apologize for the confusion. I suppose truncating the file did insert ambiguity into my explanation/description. I appreciate your patience and assistance. It seems that Rorya was able to provide to desired solution. Cheers.

    ---

16. 09-30-2021, 08:13 AM

    
    

    #16

    Re: Compile Error: Next without For
    

    Oh my Gosh…I give up…Post 2 does just that…
    Time for a Forum Break…Happy Coding Guys…I’m outta here…

    ---

17. 09-30-2021, 08:42 AM

    
    

    #17

    Re: Compile Error: Next without For
    

    Recommended to ease the mental pain — «2020 Arabella Merlot» wish it was readily available in the UK at SA prices (�3.40/bottle)

    Torachan,

    Mission statement; Promote the use of Tables, Outlaw the use of ‘merged cells’ and ‘RowSource’.

    ---

18. 09-30-2021, 09:04 AM

    
    

    #18

    Re: Compile Error: Next without For
    

    ---