This error occurs when the condition is not closed.
You must don’t forger close if conditions.
for example:
Public Sub start_r()
LastRow = SPT_DB.Range("D" & Rows.count).End(xlUp).Row
Dim i As Long
For i = 3 To 132
State = Cells(1, i)
Dim j As Long
For j = 2 To LastRow
m = SPT_DB.Cells(j, 4).Value
z = SPT_DB.Cells(j, 5).Value
n1 = SPT_DB.Cells(j, 6).Value
fc = SPT_DB.Cells(j, 7).Value
am = SPT_DB.Cells(j, 8).Value
sp = SPT_DB.Cells(j, 10).Value
sr = SPT_DB.Cells(j, 11).Value
liq = SPT_DB.Cells(j, 13).Value
num1 = Val(Left(State, 1))
num2 = Val(Mid(State, 3, 1))
num3 = Val(Mid(State, 5, 1))
num4 = Val(Mid(State, 7, 1))
num5 = Val(Mid(State, 9, 1))
Dim spt_class As spt_class
Set spt_class = New spt_class
Select Case num1
Case Is = 1: Call spt_class.rd_r1
Case Is = 2: Call spt_class.rd_r2
Case Is = 3: Call spt_class.rd_r3
Case Is = 4: Call spt_class.rd_r4
End Select
Select Case num2
Case Is = 1: Call spt_class.msf_r1
Case Is = 2: Call spt_class.msf_r2
Case Is = 3: Call spt_class.msf_r3
Case Is = 4: Call spt_class.msf_r4
Case Is = 5: Call spt_class.msf_r5
Case Is = 6: Call spt_class.msf_r6
End Select
Select Case num3
Case Is = 0:
Case Is = 1: Call spt_class.n1_cs_r1
Case Is = 2: Call spt_class.n1_cs_r2
Case Is = 3: Call spt_class.n1_cs_r3
End Select
Select Case num4
Case Is = 0:
Case Is = 1: Call spt_class.dr_r1
Case Is = 2: Call spt_class.dr_r2
Case Is = 3: Call spt_class.dr_r3
Case Is = 4: Call spt_class.dr_r4
End Select
Select Case num5
Case Is = 1: Call spt_class.crr_r1
Case Is = 2: Call spt_class.crr_r2
Case Is = 3: Call spt_class.crr_r3
Case Is = 4: Call spt_class.crr_r4
Case Is = 5: Call spt_class.crr_r5
Case Is = 6: Call spt_class.crr_r6
Case Is = 7: Call spt_class.crr_r7
Case Is = 8: Call spt_class.crr_r8
Case Is = 9: Call spt_class.crr_r9
End Select
Call spt_class.lvr_r
Next j
If cnt_f_1_all = 0 Then
Cells(4, i) = 0
Else
Cells(4, i) = cnt_f_1_liq * 100 / cnt_f_1_all
Cells(4, i).NumberFormat = "#,##0.00"
End If
If cnt_f_2_all = 0 Then
Cells(5, i) = 0
Else
Cells(5, i) = cnt_f_2_liq * 100 / cnt_f_2_all
Cells(5, i).NumberFormat = "#,##0.00"
End If
If cnt_f_3_all = 0 Then
Cells(6, i) = 0
Else
Cells(6, i) = cnt_f_3_liq * 100 / cnt_f_3_all
Cells(6, i).NumberFormat = "#,##0.00"
End If
If cnt_f_4_all = 0 Then
Cells(7, i) = 0
Else
Cells(7, i) = cnt_f_4_liq * 100 / cnt_f_4_all
Cells(7, i).NumberFormat = "#,##0.00"
End If
If cnt_f_n0_1_all = 0 Then
Cells(14, i) = 0
Else
Cells(14, i) = cnt_f_n0_1_liq * 100 / cnt_f_n0_1_all
Cells(14, i).NumberFormat = "#,##0.00"
End If
If cnt_f_n0_2_all = 0 Then
Cells(15, i) = 0
Else
Cells(15, i) = cnt_f_n0_2_liq * 100 / cnt_f_n0_2_all
Cells(15, i).NumberFormat = "#,##0.00"
End If
If cnt_f_n0_3_all = 0 Then
Cells(16, i) = 0
Else
Cells(16, i) = cnt_f_n0_3_liq * 100 / cnt_f_n0_3_all
Cells(16, i).NumberFormat = "#,##0.00"
End If
If cnt_f_n0_4_all = 0 Then
Cells(17, i) = 0
Else
Cells(17, i) = cnt_f_n0_4_liq * 100 / cnt_f_n0_4_all
Cells(17, i).NumberFormat = "#,##0.00"
End If
If cnt_f_n1_1_all = 0 Then
Cells(24, i) = 0
Else
Cells(24, i) = cnt_f_n1_1_liq * 100 / cnt_f_n1_1_all
Cells(24, i).NumberFormat = "#,##0.00"
End If
If cnt_f_n1_2_all = 0 Then
Cells(25, i) = 0
Else
Cells(25, i) = cnt_f_n1_2_liq * 100 / cnt_f_n1_2_all
Cells(25, i).NumberFormat = "#,##0.00"
End If
If cnt_f_n1_3_all = 0 Then
Cells(26, i) = 0
Else
Cells(26, i) = cnt_f_n1_3_liq * 100 / cnt_f_n1_3_all
Cells(26, i).NumberFormat = "#,##0.00"
End If
If cnt_f_n1_4_all = 0 Then
Cells(27, i) = 0
Else
Cells(27, i) = cnt_f_n1_4_liq * 100 / cnt_f_n1_4_all
Cells(27, i).NumberFormat = "#,##0.00"
End If
If cnt_f_n2_1_all = 0 Then
Cells(34, i) = 0
Else
Cells(34, i) = cnt_f_n2_1_liq * 100 / cnt_f_n2_1_all
Cells(34, i).NumberFormat = "#,##0.00"
End If
If cnt_f_n2_2_all = 0 Then
Cells(35, i) = 0
Else
Cells(35, i) = cnt_f_n2_2_liq * 100 / cnt_f_n2_2_all
Cells(35, i).NumberFormat = "#,##0.00"
End If
If cnt_f_n2_3_all = 0 Then
Cells(36, i) = 0
Else
Cells(36, i) = cnt_f_n2_3_liq * 100 / cnt_f_n2_3_all
Cells(36, i).NumberFormat = "#,##0.00"
End If
If cnt_f_n2_4_all = 0 Then
Cells(37, i) = 0
Else
Cells(37, i) = cnt_f_n2_4_liq * 100 / cnt_f_n2_4_all
Cells(37, i).NumberFormat = "#,##0.00"
End If
If cnt_f_n3_1_all = 0 Then
Cells(44, i) = 0
Else
Cells(44, i) = cnt_f_n3_1_liq * 100 / cnt_f_n3_1_all
Cells(44, i).NumberFormat = "#,##0.00"
End If
If cnt_f_n3_2_all = 0 Then
Cells(45, i) = 0
Else
Cells(45, i) = cnt_f_n3_2_liq * 100 / cnt_f_n3_2_all
Cells(45, i).NumberFormat = "#,##0.00"
End If
If cnt_f_n3_3_all = 0 Then
Cells(46, i) = 0
Else
Cells(46, i) = cnt_f_n3_3_liq * 100 / cnt_f_n3_3_all
Cells(46, i).NumberFormat = "#,##0.00"
End If
If cnt_f_n3_4_all = 0 Then
Cells(47, i) = 0
Else
Cells(47, i) = cnt_f_n3_4_liq * 100 / cnt_f_n3_4_all
Cells(47, i).NumberFormat = "#,##0.00"
End If
Next i
cnt_f_1_liq = 0
cnt_f_2_liq = 0
cnt_f_3_liq = 0
cnt_f_4_liq = 0
cnt_f_1_all = 0
cnt_f_2_all = 0
cnt_f_3_all = 0
cnt_f_4_all = 0
cnt_f_n0_1_liq = 0
cnt_f_n0_2_liq = 0
cnt_f_n0_3_liq = 0
cnt_f_n0_4_liq = 0
cnt_f_n0_1_all = 0
cnt_f_n0_2_all = 0
cnt_f_n0_3_all = 0
cnt_f_n0_4_all = 0
cnt_f_n1_1_liq = 0
cnt_f_n1_2_liq = 0
cnt_f_n1_3_liq = 0
cnt_f_n1_4_liq = 0
cnt_f_n1_1_all = 0
cnt_f_n1_2_all = 0
cnt_f_n1_3_all = 0
cnt_f_n1_4_all = 0
cnt_f_n2_1_liq = 0
cnt_f_n2_2_liq = 0
cnt_f_n2_3_liq = 0
cnt_f_n2_4_liq = 0
cnt_f_n2_1_all = 0
cnt_f_n2_2_all = 0
cnt_f_n2_3_all = 0
cnt_f_n2_4_all = 0
cnt_f_n3_1_liq = 0
cnt_f_n3_2_liq = 0
cnt_f_n3_3_liq = 0
cnt_f_n3_4_liq = 0
cnt_f_n3_1_all = 0
cnt_f_n3_2_all = 0
cnt_f_n3_3_all = 0
cnt_f_n3_4_all = 0
End Sub
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 SubEvery 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 SubExample 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 SubHere, the End If statement is not placed correctly causing overlapping as shown below:
For
If
Next
End IfThe 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 SubExample 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 SubJust 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 SubExample 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 SubNote: 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 SubOR
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 SubThe 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
Nextto
Next counterThis 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
Пользовательское соглашение
Политика конфиденциальности
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
Техническая поддержка сайта
ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |
I am receiving a next without for error from:
Sub CTLines()
Dim iVal As Integer
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim rng As Range
Set ws1 = Worksheets("INCIDENTS")
Set ws2 = Worksheets("INCDB")
iVal = Application.WorksheetFunction.CountIf(Range("AO5:AO999"), "Yes")
Dim i
For i = 1 To iVal
With Sheets("INCDB")
.Range("5:5").Insert Shift:=x1Down
Next i
End Sub
I’ve tried changing the variable, the indent, many things and I’ve not been successful.
All I want to do is to count how many rows contain Yes
in the AO column and add as many rows in the INCDB spreadsheet.
TylerH
20.8k66 gold badges76 silver badges101 bronze badges
asked Oct 22, 2015 at 17:19
2
Change the code to this, near the bottom:
For i = 1 To iVal
With Sheets("INCDB")
.Range("5:5").Insert Shift:=xlDown
End With
Next i
The VBA compiler is not good at reporting what is wrong, when it encounters code that has one or more lines that are missing a matching termination line.
In your case you never terminated the With
statement.
BigBen
46.6k7 gold badges24 silver badges41 bronze badges
answered Oct 22, 2015 at 17:21
Excel HeroExcel Hero
14.3k4 gold badges33 silver badges40 bronze badges
4
Макрос останавливается с ошибкой Next без For |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |