Next Without For / For Without Next / Loop Without Do / End If Without Block If Errors

end if without block if vba
compile error for without next
else without if error vba
vba if statement
for loop vba
invalid next control variable reference
subscript out of range
vba loop if then next

I'm trying to print formulas to cells in specific worksheets whose names are in the predefined arrays. I have the following code:

Private Sub bngComm_Click()
Dim i As Integer, j As Integer, k As Integer
Dim colref1 As String, colref2 As String, colref3 As String

    For i = 13 To 134
    Do While IsInArray(Worksheets(i).Name, arrBingol)
        For j = 131 To 144
            If j = 136 Then

            Else
                For k = 2 To 13
                    If k = 2 Then
                        colref1 = "A"
                        colref2 = "E"
                        colref3 = "B"
                    ElseIf k = 3 Then
                        colref1 = "B"
                        colref2 = "F"
                        colref3 = "C"
                    ElseIf k = 4 Then
                        colref1 = "C"
                        colref2 = "G"
                        colref3 = "D"
                    ElseIf k = 5 Then
                        colref1 = "D"
                        colref2 = "H"
                        colref3 = "E"
                    ElseIf k = 6 Then
                        colref1 = "E"
                        colref2 = "I"
                        colref3 = "F"
                    ElseIf k = 7 Then
                        colref1 = "F"
                        colref2 = "J"
                        colref3 = "G"
                    ElseIf k = 8 Then
                        colref1 = "G"
                        colref2 = "K"
                        colref3 = "H"
                    ElseIf k = 9 Then
                        colref1 = "H"
                        colref2 = "L"
                        colref3 = "I"
                    ElseIf k = 10 Then
                        colref1 = "I"
                        colref2 = "M"
                        colref3 = "J"
                    ElseIf k = 11 Then
                        colref1 = "J"
                        colref2 = "N"
                        colref3 = "K"
                    ElseIf k = 12 Then
                        colref1 = "K"
                        colref2 = "O"
                        colref3 = "L"
                    ElseIf k = 13 Then
                        colref1 = "L"
                        colref2 = "P"
                        colref3 = "M"
                    End If
                Worksheets(i).Cells(j, k).FormulaLocal = "=(VLOOKUP(VALUE(MID(CELL(" & Chr(34) & "fılename" & Chr(34) & ";" & colref1 & CStr(j - 130) & ");FIND(" & Chr(34) & "]" & Chr(34) & ";CELL(" & Chr(34) & "fılename" & Chr(34) & ";" & colref1 & CStr(j - 130) & ");1)+1;30));'FTE GİDER DAĞILIM ANAHTARI'!$C:" & colref2 & ";" & CStr(k + 1) & ";0)/'FTE GİDER DAĞILIM ANAHTARI'!" & colref2 & "$7)*Premises!" & colref3 & CStr(j - 111)
                End If
            Next k
        Next j

        i = i + 1
    Loop
    Next i
End Sub

And it constantly gives Next Without For / For Without Next / Loop Without Do / End If Without Block If Errors whenever I try to fix one of them. I have checked to see if the statements are in correct order and I couldn't find anything wrong with it.

I used RUbberDuck (A fantastic free addin for VBA) to correctly indent your code. I then deleted the long innermost if statement and the cause of your problem was patently clear.

Option Explicit

Private Sub bngComm_Click()
Dim i                                           As Integer, j As Integer, k As Integer
Dim colref1                                     As String, colref2 As String, colref3 As String

    For i = 13 To 134
        Do While IsInArray(Worksheets(i).Name, arrBingol)
            For j = 131 To 144
                If j = 136 Then

                Else
                    For k = 2 To 13

                        Worksheets(i).Cells(j, k).FormulaLocal = "=(VLOOKUP(VALUE(MID(CELL(" & Chr(34) & "filename" & Chr(34) & ";" & colref1 & CStr(j - 130) & ");FIND(" & Chr(34) & "]" & Chr(34) & ";CELL(" & Chr(34) & "filename" & Chr(34) & ";" & colref1 & CStr(j - 130) & ");1)+1;30));'FTE GIDER DAGILIM ANAHTARI'!$C:" & colref2 & ";" & CStr(k + 1) & ";0)/'FTE GIDER DAGILIM ANAHTARI'!" & colref2 & "$7)*Premises!" & colref3 & CStr(j - 111)
                    End If
                Next k
            Next j

            i = i + 1
        Loop
    Next i
End Sub

The Next K should be before the end if.

End If without block If, End If without block If An End If statement must have a corresponding If statement. This error has the following cause and solution: and feedback for guidance about the ways you can receive support and provide feedback. 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

As @GSerg said, you typed Next k in the wrong place. And so many ifs can be resumed with a Select Case...End Select statement.

Dim i As Integer, j As Integer, k As Integer
Dim colref1 As String, colref2 As String, colref3 As String

For i = 13 To 134
    Do While IsInArray(Worksheets(i).Name, arrBingol)
        For j = 131 To 144 Step 1
            If j = 136 Then
            Else
                For k = 2 To 13
                    Select Case k
                        Case 2: colref1 = "A": colref2 = "E": colref3 = "B"
                        Case 3: colref1 = "B": colref2 = "F": colref3 = "C"
                        Case 4: colref1 = "C": colref2 = "G": colref3 = "D"
                        Case 5: colref1 = "D": colref2 = "H": colref3 = "E"
                        Case 6: colref1 = "E": colref2 = "I": colref3 = "F"
                        Case 7: colref1 = "F": colref2 = "J": colref3 = "G"
                        Case 8: colref1 = "G": colref2 = "K": colref3 = "H"
                        Case 9: colref1 = "H": colref2 = "L": colref3 = "I"
                        Case 10: colref1 = "I": colref2 = "M": colref3 = "J"
                        Case 11: colref1 = "J": colref2 = "N": colref3 = "K"
                        Case 12: colref1 = "K": colref2 = "O": colref3 = "L"
                        Case 13: colref1 = "L": colref2 = "P": colref3 = "M"
                    End Select
                    Worksheets(i).Cells(j, k).FormulaLocal = "=(VLOOKUP(VALUE(MID(CELL(" & Chr(34) & "filename" & Chr(34) & ";" & colref1 & CStr(j - 130) & ");FIND(" & Chr(34) & "]" & Chr(34) & ";CELL(" & Chr(34) & "filename" & Chr(34) & ";" & colref1 & CStr(j - 130) & ");1)+1;30));'FTE GIDER DAGILIM ANAHTARI'!$C:" & colref2 & ";" & CStr(k + 1) & ";0)/'FTE GIDER DAGILIM ANAHTARI'!" & colref2 & "$7)*Premises!" & colref3 & CStr(j - 111)
                Next k
            End If
        Next j

        i = i + 1 '<--!!!!
    Loop
Next i

I'm really curious about your code, because you got i = i + 1 inside a Do, and that's ok, but that Do is already inside a For i=... . I guess you have your reasons.

Also, now that you got the Select Case part, you can see a clearly mathematical relation between the value of k and the columns, so probably you can even adapt that part, because the relation is colref1 = k-1 : colref2 = k+3: colref3 = k and use column numbers instead of letters. But this is just a suggestion.

Hope this helps.

"Next Without For" Compile Error in Excel VBA, LOOP block, and the END IF statement were omitted, QB would give you an error “LOOP without DO” which is manifestly wrong—the error is IF without END IF. CONST Smallest in main program IF ABS (Y.2 - Y 1) > Smallest THEN X next  Check other control structures within the IfEnd If structure and verify that they are correctly matched. Also check that the block If is correctly formatted. For additional information, select the item in question and press F1 (in Windows) or HELP (on the Macintosh).

While this is not a breakdown of your problem (the others took care of that), I just want to show you how you could simplify your IF statements:

Sub Test()

Dim k As Long
Dim colref1 As String, colref2 As String, colref3 As String

For k = 2 To 13
    colref1 = Chr(63 + k): colref2 = Chr(67 + k): colref3 = Chr(64 + k)
Next k

End Sub

MS Excel: How to use the FORNEXT Statement (VBA), A Block If without End If error occurs when the VBA compiler is unable to find the End If that Duration: 2:23 Posted: Jun 3, 2016 This is my first time using a Microsoft Excel Macro and I'm trying to run the following code and keep getting the: Compile Error: Block If without End if message and I can't figure out why Sub P() ' ' Macro1 Macro ' Changing FedEx Descriptions

QuickBASIC Programming for Scientists and Engineers, It is an error I encountered when running a macro. I'm very confused You've got an "END IF" statement without a beginning IF. If using nested "For-Next loops" it always helps when using "Next" to include what next it is. eg:. For Next Loop. The ‘For Next’ loop allows you to go through a block of code for the specified number of times. For example, if I ask you to add the integers from 1 to 10 manually, you would add the first two numbers, then add the third number to the result, then add the fourth number to the result, as so on..

What is a "Block If without End If Compile Error" in an Excel VBA , End if without block if more than one statement after an if, you *must* not put the first of those statements will return the error you have seen. Not a problem but then I am lost because I have several txtboxes that I have to test if the cell is blank and input data if so. Example Test if A13 is blank if so input the vlaue in txtposition in that cell (I did that with the code below)

"End if without block If", The same logic can be built in VBA using the If Then Else statement as well IF Then Else in VBA- Block IF without End If error Sub SaveCloseAllWorkbooks() Dim wb As Workbook For Each wb In Workbooks On error resume next If wb. Sub UPDATE() Dim temp As Integer 'stock_list is the current stock level used to avoid errors such as adjusting itself; Dim row_stock As Integer 'row_stock is the row in thew stock list of the current product

Comments
  • When you compile, it shows you exactly where it is wrong. It highlights the End If after the Worksheets(i).Cells(j, k).FormulaLocal =. Because there must be next k at that point.
  • I can't tell if your indentations are wrong in your original code or if they got messed up when you posted it here. Anyway - if you get "x without y" errors, make sure your indentations are correct. That is the fastest way to identify what's missing.
  • Yes. I figured it out. Thanks guys. I will definitely check the rubberduck stuff.
  • I literally just thought the same thing as per your last alinea =)
  • And if you go just one step further and use FormulaR1C1Local, you will not need the entire select case block and will be able to just use k directly. And if you go one step further yet and switch to FormulaR1C1, amending the formula accordingly to use en-us function names and separators, then the code will also work on any localized Excel, not just in tr-tr.
  • i = i + 1 is there because i needs to be incremented each time the Do statement loops, not only when it breaks and goes to the For loop. I will try using the Select Case blocks. Thanks for your consideration.