The End If without block If error is a VBA code error that occurs when the code containing any If block does not follow the syntax correctly. However, you can easily solve this error if you keep some rules in mind.
In this article, we will explore End If without Block If VBA error in Excel. We will start the article by explaining a common cause and solution of this error. Using End If after a single line if statement is the common reason for this error. We will also discuss how to solve this problem by removing extra End Ifs and sometimes adding the missing End Ifs.
Download Practice Workbook
Download this practice workbook while reading this article.
How to Fix End If Without Block If Issue Using VBA
In this article, we will discuss four common causes and solutions of the error End If without block If in VBA.
1. Omit End If Statement in Single Line Statement
The End If without Block If error might occur if you write an If statement in a single line and insert an End If statement at the end like in the following code.
Sub SingleLine()
If Range("C5").Value > 40 Then Range("D5").Value = "Pass"
End If
End Sub
- Click on the Run button and it will show the End If without block If error.
Solution:
- Make sure to remove the End If as it is not necessary when you write the If statement in one line.
Sub SingleLine()
If Range("C5").Value > 40 Then Range("D5").Value = "Pass"
End Sub
- Now run the code again and you get your desired output without any error.
2. Remove Extra End If Statements
The End If without Block If error can occur if you have more End If statements than If statements. In the following VBA, there are two If statements and three End If statements.
Sub ExtraEndIfs()
Dim score As Integer
For Each cell In Range("C5:C12")
score = cell.Value
If score >= 90 Then
cell.Offset(0, 1).Value = "A"
ElseIf score >= 80 Then
cell.Offset(0, 1).Value = "B"
ElseIf score >= 70 Then
cell.Offset(0, 1).Value = "C"
ElseIf score >= 60 Then
cell.Offset(0, 1).Value = "D"
Else
If score >= 50 Then
cell.Offset(0, 1).Value = "E"
Else
cell.Offset(0, 1).Value = "F"
End If
End If
End If
Next cell
End Sub
- You will get the same error if you run this code.
Solution:
- To resolve this issue, you need to make sure that there is one End If statement for each If statement. Any extra End If must be removed before running the code.
- There is one extra End If in the above code. Remove the “End If” to solve the error.
Sub ExtraEndIfs()
Dim score As Integer
For Each cell In Range("C5:C12")
score = cell.Value
If score >= 90 Then
cell.Offset(0, 1).Value = "A"
ElseIf score >= 80 Then
cell.Offset(0, 1).Value = "B"
ElseIf score >= 70 Then
cell.Offset(0, 1).Value = "C"
ElseIf score >= 60 Then
cell.Offset(0, 1).Value = "D"
Else
If score >= 50 Then
cell.Offset(0, 1).Value = "E"
Else
cell.Offset(0, 1).Value = "F"
End If
End If
Next cell
End Sub
- Now run this code and the error will be resolved.
3. Insert End If for Each If Statement
You might face End If without Block If error if an If statement does not end with an End If statement. In the following code, the second If statement is missing the End If statement.
Sub AllEndIfs()
If Range("C5").Value > 40 Then
Range("D5").Value = "Pass"
End If
If Range("C6").Value > 40 Then
Range("D6").Value = "Pass"
End Sub
- The Block If without End If error will occur when you execute the code.
Solution:
- Add an End If statement for the second If statement.
Sub AllEndIfs()
If Range("C5").Value > 40 Then
Range("D5").Value = "Pass"
End If
If Range("C6").Value > 40 Then
Range("D6").Value = "Pass"
End If
End Sub
- Press F5 to run the code and you will get your desired result.
4. Delete Corresponding End If While Removing If Statement
Sometimes while removing a part of the VBA code, you might forget to remove the corresponding End If statement. In the following code, the If statement is deleted but the corresponding End If statement is not removed.
Sub ExtraEndIfs()
Dim score As Integer
For Each cell In Range("C5:C12")
score = cell.Value
If score >= 90 Then
cell.Offset(0, 1).Value = "A"
ElseIf score >= 80 Then
cell.Offset(0, 1).Value = "B"
ElseIf score >= 70 Then
cell.Offset(0, 1).Value = "C"
ElseIf score >= 60 Then
cell.Offset(0, 1).Value = "D"
Else
End If
End If
Next cell
End Sub
- As a result, the End If without block If error appears.
Solution:
- Remove that extra End If.
Sub ExtraEndIfs()
Dim score As Integer
For Each cell In Range("C5:C12")
score = cell.Value
If score >= 90 Then
cell.Offset(0, 1).Value = "A"
ElseIf score >= 80 Then
cell.Offset(0, 1).Value = "B"
ElseIf score >= 70 Then
cell.Offset(0, 1).Value = "C"
ElseIf score >= 60 Then
cell.Offset(0, 1).Value = "D"
Else
End If
Next cell
End Sub
- Then run the code and the problem will be fixed.
Things to Remember
- Each If statement has only one End If statement.
- When the If statement is written in one line, you don’t need an End If statement to terminate the If statement.
- Use proper indentation for If and other statement blocks that improve visibility. This ensures unwanted syntaxes don’t cause any problems while executing the code.
Frequently Asked Questions
1. How do you end an if statement in VBA?
The If statement starts with an If, followed by a condition and a statement and then ends with the End statement.
If (Condition) Then
(Statement)
End If
2. What is runtime error 424 in VBA?
Runtime error 424 is an error in VBA that occurs when you run a code using incorrect object names. For example, if your object name is X and you are using Y in the code, it will cause a runtime 424 error.
3. What is the End If used for?
End If is used in VBA code to terminate a multiple-line If statement. It is written after the statement of the if condition is specified.
Conclusion
Thanks for making it this far. I hope you found this article helpful. In this article, we have explained possible reasons and their solution of the issue End If without block If in VBA. We have described when to remove End Ifs and when to add extra End Ifs to solve the problem. If you have any queries or recommendations regarding this article, feel free to let us know in the comment section below.
Get FREE Advanced Excel Exercises with Solutions!