Today I’ll show you how to resolve the error “End If without block If” in VBA. But first, you have to understand the “If” statement in order to fix the issue.
The IF statement and its various forms
The If statement is a conditional clause that helps us to run code using a condition that is decided during runtime. You might wonder, “What is the need to decide the condition during runtime? Can’t we decide that earlier?” In reality, there are many situations where an action needs to be performed only if certain criteria are met or a condition is fulfilled. Sometimes this check might even depend on the user’s input value.
For example, let us imagine that a bank offers 8% ROI on fixed deposit accounts if the customer is a senior citizen and only 6% ROI for other customers. In this case, the code that calculates the interest and maturity amount should both a) consider the age of the customer and b) use a condition to use different values for senior and non-senior citizens. This is where an “If conditional statement” steps in.
Now let’s see the code for the above scenario assuming that one must be 60 years old to be called a senior citizen.
Sub sample_coding() 'declaration of variables Dim matamt, prinamt, roi, term, custage ‘ receive input from user custage = InputBox("Enter the age of the customer") ‘ assign some values prinamt = 10000 ' Principal amount term = 2 ' 2 years ' decide the roi value If custage < 60 Then roi = 6 Else roi = 8 End If ' formula to calculate the FD maturity amount. matamt = prinamt + (prinamt * roi * term / 100) ‘ printing the output Debug.Print matamt End Sub
Looking at the example above, we see that the syntax for using a simple If statement is
If <condition> Then
<code>
End If
But the same conditional statement has different forms as listed below.
- A simple If Block
- An If – Else block
- An Else-If block
- Nested If block
The Compile Error “End If without Block If:
This is a simple compile time error that’s thrown when the code containing any If blocks do not comply with the syntax (or) such a statement does not exist.
Here are some instances where this error might occur
Rule 1: End If with single line statement
If the single line of code to be executed is placed in the same line as the “If – then” statement, then the “End If” statement needs to be omitted. In other words, the If statement is considered complete without an “End If” statement in cases where the conditional code is placed in the same line.
If &amp;lt;condition&amp;gt; Then &amp;lt;code&amp;gt;
For example:
The If condition in the above code can be rewritten using this rule to avoid the compile error “End if without block If”.
' Fix an roi in common roi = 8 'Change the value for non-senior citizens alone using the rule 1 If custage &amp;lt; 60 Then roi = 6 ' comment or remove the end if statement to fix the error. 'End If
According to Rule 1, if “End If” is used in the above code, you will encounter the error “End If without block If”. So, do not forget to remove it.
If you’re using nested if conditions, ensure that every “If” statement that has been opened, has a corresponding “End If” statement. This is in addition to Rule 1 above.
Example 1
If custage &amp;lt; 60 Then roi = 6 If strgen = "Female" And custage &amp;gt; 57 Then roi = 8 End If '********Line is explained below********* Else roi = 8 End If
In this piece of code,
- The inner “If” condition follows Rule 1 (i.e. code is placed in the same statement after “Then” keyword). Therefore, this statement is a standalone statement that does not require “End If”.
- But since we have an “End If” statement , it will be considered to be the corresponding “End “ of the outer if statement (Line 1).
- This leads to the “Else” keyword in the fifth line looking for its corresponding “If statement”. In turn, we end up with the error “Else without If” which is similar to “End if without block If”.
- The solution to this problem is to remove the unnecessary “End if” in line 4 or place the code “
roi=8
” in the next line i.e between the IF… then and the End if statements.
Example 2
If apple = "sweet" Then If mango = "sweet" Then Debug.Print "Fruits are sweet" End If End If
In this example,
- Here since line 2 is already complete without “End if “, line 3 is automatically matched with the If statement of line number 1.
- So, the “End If” in line 4 searches for its pair of “If statement” and leads to the compile error “End if without block If”.
- The solution to this is to remove line 3 or place the “
Debug.Print
” statement in a separate line before the “End If” statement in line no 3.
Rule 3: Forgetting part of your deleted code
Ensure that there is no “End if” statement left behind without an “If” statement in your code. This might happen when you maintain code or change your logic after a long period of time.
For example, you might think that an “If – End if “ block of code might not be required in a certain place. And after you delete that “If block”, you may forget to delete its “End If” statement. This again causes the same compile error we keep seeing, “End if without block If”.
For Example:
If apple = "sweet" Then End If End If
Imagine that you wanted to delete the inner If block in the above example. While doing so, you forgot to delete the “End If” statement. Then, you are sure to encounter the compile error “End If without block If”.
Here is a video that explains everything outlined above with sample code. The code is explained and executed line by line, so you can completely understand what causes the error “End if without block If”.
Summary
Basically, when you look at the error statement, it is clear that it is thrown if there are any unnecessary ‘End If’ statements. The only solution is to trace the code and remove the erroneous statement after confirming that it does not affect the rest of the code in any way.
The other compile error “Else without If”, for which there is an example in Rule 2, is related to this error. It is thrown when there is an “Else <some code> End If” statement or just an “Else” statement without an “If <condition> Then” statement. In general, for any error , it is wise and time saving to check the syntax first and then proceed with troubleshooting.
I am currently running the below loop to pull out information from another spreadsheet, but keep getting the following error message Compile error: End If without block If, at
ElseIf cel.Offset(0, 8).Value = "" Then wshT.Cells(r, 14).Value = "Physical"
What could be causing it and how do I remediate it? My code below:
' Loop though cells in column A on main.xlsm
For r = 1 To m
' Can we find the value in column A
Set cel = wshS.Columns(3).Find(What:=wshT.Cells(r, 1).Value, _
LookAt:=xlWhole, MatchCase:=False)
If Not cel Is Nothing Then
If cel.Offset(0, 8).Value = "Yes" Then wshT.Cells(r, 14).Value = "Virtual"
ElseIf cel.Offset(0, 8).Value = "" Then wshT.Cells(r, 14).Value = "Physical"
Else: End If
End If
Next r
asked Mar 22, 2014 at 16:46
methuselahmethuselah
12.7k45 gold badges162 silver badges309 bronze badges
2
Further to my comments above, change your code to
If Not cel Is Nothing Then
If cel.Offset(0, 8).Value = "Yes" Then wshT.Cells(r, 14).Value = "Virtual"
If cel.Offset(0, 8).Value = "" Then wshT.Cells(r, 14).Value = "Physical"
End If
or to this
If Not cel Is Nothing Then
If cel.Offset(0, 8).Value = "Yes" Then
wshT.Cells(r, 14).Value = "Virtual"
ElseIf cel.Offset(0, 8).Value = "" Then
wshT.Cells(r, 14).Value = "Physical"
End If
End If
For the syntax of IF/EndIf
, see the below
'~~> Multiple-line syntax:
If condition [ Then ]
[ statements ]
[ ElseIf elseifcondition [ Then ]
[ elseifstatements ] ]
[ Else
[ elsestatements ] ]
End If
'~~> Single-line syntax:
If Condition Then [ statements ] [ Else [ elsestatements ] ]
answered Mar 22, 2014 at 16:50
Siddharth RoutSiddharth Rout
147k17 gold badges206 silver badges250 bronze badges
0
Option Explicit Sub checking() Dim a() Dim b() Dim i As Long Dim k As Long Dim j As Long Dim iLastRow As Long a = Sheets("ÎèÎ").[A1].CurrentRegion.Value b = Sheets("ÎÑÂ").[A1].CurrentRegion.Value ReDim c(1 To UBound(a) + UBound(b), 1 To 12) j = 1 For i = 1 To UBound(a) If a(i, 7) Like "*ÎÎÎ*" Or _ a(i, 7) Like "*ÀÎ*" Or _ a(i, 7) Like "*""*""*" Or _ UBound(Split(a(i, 7), " "), 1) + 1 >= 5 Then For k = 1 To UBound(b) If a(i, 7) = b(k, 1) Then c(j, 1) = a(i, 1) c(j, 2) = a(i, 2) c(j, 3) = a(i, 3) c(j, 4) = a(i, 4) c(j, 5) = a(i, 5) c(j, 6) = a(i, 6) c(j, 7) = a(i, 7) c(j, = a(i, c(j, 9) = b(k, 1) c(j, 10) = b(k, 2) c(j, 11) = b(k, 3) c(j, 12) = b(k, 4) j = j + 1 Else With CreateObject("VBScript.RegExp") .Pattern = "[à-ÿÀ-߸¨]+s[à-ÿÀ-߸¨]{1}[.]{1}[à-ÿÀ-߸¨]{1}[.]{1}$" If .Test(a(i, 7)) Then If a(i, 7) = b(k, 1) _ Or Right(a(i, 7), 4) = Right(b(k, 1), 4) _ Or Left(a(i, 7), InStr(a(i, 7), " ") - 2) = Left(a(k, 1), InStr(a(i, 7), " ") - 2) Then c(j, 1) = a(i, 1) c(j, 2) = a(i, 2) c(j, 3) = a(i, 3) c(j, 4) = a(i, 4) c(j, 5) = a(i, 5) c(j, 6) = a(i, 6) c(j, 7) = a(i, 7) c(j, = a(i, c(j, 9) = b(k, 1) c(j, 10) = b(k, 2) c(j, 11) = b(k, 3) c(j, 12) = b(k, 4) j = j + 1 End If End If End If Next k Next i Sheets("Èòîã").[A1].Resize(UBound(c), 12) = c End Sub
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 |
Sub Algdevis() Worksheets("devis").Select Dim codeclient As Integer Dim nom As String Dim rue As String Dim ville As String Dim codepostal As String Dim pays As String Dim tel As Long Dim email As String Dim ndevis As Integer Dim distance As Single Dim terrain As Single Dim paymentMode As String Dim reference As String Dim quantCom As Integer reponse = 6 cpt = 1 i = 1 s = 2 t = 3 w = 3 Do While reponse = 6 ligneNumero = cpt + 25 If cpt = 1 Then codeclient = InputBox("Entrez le code du client :") Do While i <= 20 If Worksheets("clients").Cells(i, 1) = codeclient Then Worksheets("devis").Cells(5, 4) = codeclient Do While s <= 8 Worksheets("devis").Cells(4 + s, 4).Value = Worksheets("clients").Cells(i, s).Value s = s + 1 Loop Exit Do End If i = i + 1 Loop End If Do While reponseType <> "achat" Or reponseType <> "service" reponseType = InputBox("Il s'agit d'un achat ou du service?") Loop If reponseType = "achat" Then tva = 19.6 tauxDeRemise = 0 reference = InputBox("Entrez le reference :") Worksheets("devis").Cells(ligneNumero, 1).Value = reference Do While t < 200 If Worksheets("plantes et arbres").Cells(t, 1).Value = reference Then Worksheets("devis").Cells(ligneNumero, 2).Value = Worksheets("plantes et arbres").Cells(t, 2).Value Worksheets("devis").Cells(ligneNumero, 4).Value = Worksheets("plantes et arbres").Cells(t, 3).Value Exit Do End If t = t + 1 Loop quantite = InputBox("Entrez la quantite d'achat:") Worksheets("devis").Cells(ligneNumero, 3).Value = quantite Worksheets("devis").Cells(20, 2).Value = 19.6 Worksheets("devis").Cells(ligneNumero, 1).Value = reference montant = quantite * Worksheets("devis").Cells(ligneNumero, 4).Value Worksheets("devis").Cells(ligneNumero, 5).Value = montant Worksheets("devis").Cells(19, 2).Value = montant Worksheets("devis").Cells(21, 2).Value = montant * (tva / 100) ElseIf reponseType = "service" Then quantite = 1 Worksheets("devis").Cells(ligneNumero, 3).Value = quantite serviceJardin = InputBox("Il s'agit de creation du jardin ou de entretien?") If serviceJardin = "creation" Then tva = 19.6 terrain = InputBox("Entrez la surface du terrain :") Worksheets("devis").Cells(16, 2) = terrain If terrain < 200 Then tauxDeRemise = 1 ElseIf terrain > 200 And terrain < 600 Then tauxDeRemise = 2.5 ElseIf terrain > 600 And terrain < 1000 Then tauxDeRemise = 3 ElseIf terrain > 1000 And terrain <= 10000 Then tauxDeRemise = 5 ElseIf terrain > 10000 Then tauxDeRemise = 10 End If Worksheets("devis").Cells(18, 2).Value = tauxDeRemise ElseIf serviceJardin = "entretien" Then tva = 5.5 Worksheets("devis").Cells(16, 2) = 0 End If reference = InputBox("Entrez le reference :") Do While w <= 200 If Worksheets("Prestations").Cells(t, 1).Value = reference Then Worksheets("devis").Cells(ligneNumero, 2).Value = Worksheets("Prestations").Cells(w, 2).Value Worksheets("devis").Cells(ligneNumero, 4).Value = Worksheets("Prestations").Cells(w, 3).Value Exit Do End If w = w + 1 Loop Worksheets("devis").Cells(ligneNumero, 1).Value = reference montant = Worksheets("devis").Cells(ligneNumero, 3).Value * Worksheets("devis").Cells(ligneNumero, 4).Value Worksheets("devis").Cells(ligneNumero, 5).Value = montant Worksheets("devis").Cells(19, 2).Value = montant Worksheets("devis").Cells(20, 2).Value = montant * (1 - (Worksheets("devis").Cells(18, 2).Value / 100)) Worksheets("devis").Cells(21, 2).Value = (montant - montant * (tauxDeRemise / 100)) * (tva / 100) End If 'при выдаче ошибки выделяет эту строку ndevis = InputBox("Entrez le numero du devis :") Worksheets("devis").Cells(14, 2) = ndevis distance = InputBox("Entrez la distance :") If distance <= 50 Then fraisDeTransport = Worksheets("Transport").Cells(3, 2).Value ElseIf distance <= 200 And distance > 50 Then fraisDeTransport = Worksheets("Transport").Cells(4, 2).Value ElseIf distance > 200 Then fraisDeTransport = Worksheets("Transport").Cells(5, 2).Value End If fraisDeTransport = fraisDeTransport + fraisDeTransport * 0.055 Worksheets("devis").Cells(15, 2).Value = distance Worksheets("devis").Cells(22, 2) = fraisDeTransport paymentMode = InputBox("Entrez le mode de payement :") Worksheets("devis").Cells(17, 2) = paymentMode Total = Worksheets("devis").Cells(20, 2).Value + Worksheets("devis").Cells(20, 2).Value * (tva / 100) + fraisDeTransport Worksheets("devis").Cells(23, 2) = Worksheets("devis").Cells(23, 2) + Total reponse = MsgBox("Voulez-vous acheter encore une plante?", vbYesNo) If reponse = vbYes Then cpt = cpt + 1 reponseType = "" End If Loop End Sub |
-
#2
You’ve got an «END IF» statement without a beginning IF. Something like:
when you need
Code:
If Test = 0 Then
'what to do if condition true
Else
'what to do if condition false
End If
Regards,
-
#3
Hello,
Could you post the code ?
Ciao,
Don.
-
#4
I had an existing macro that I used as a model for what I wanted to do. I admit freely I practically no clue about complicated macros. This is the macro I used as a model and it works fine:
‘Team Total Net Yards
t = 1
Do
t = t + 1
Loop Until Cells(t, 1) = «TOTAL» And _
Cells(t, 2) = «NET» And _
Cells(t, 3) = «YARDS»
tny1 = Cells(t, 4)
tny2 = Cells(t, 5)
End If
I wanted to add some things so I copied this macro (probably not correct anyway but…what do I know) and this is how it all looks now:
‘Team Total Net Yards
t = 1
Do
t = t + 1
Loop Until Cells(t, 1) = «TOTAL» And _
Cells(t, 2) = «NET» And _
Cells(t, 3) = «YARDS»
tny1 = Cells(t, 4)
tny2 = Cells(t, 5)
End If
Offset = 1
temp = Cells(c, 1)
If Len(temp) < 4 And Right(temp, 1) = «.» Then
temp = temp & Cells(c, 2)
Offset = 2
End If
‘Team Net Yards Rushing
t = 1
Do
t = t + 1
Loop Until Cells(t, 1) = «NET» And _
Cells(t, 2) = «YARDS» And _
Cells(t, 3) = «RUSHING»
tny3 = Cells(t, 4)
tny4 = Cells(t, 5)
End If
Offset = 1
temp = Cells(c, 1)
If Len(temp) < 4 And Right(temp, 1) = «.» Then
temp = temp & Cells(c, 2)
Offset = 2
End If
So that is what I did, the second half, the code in bold. I’m clueless so any help is appreciated.
Bill
-
#5
Looks to me like it’s the first «End If» in each section. Try this and let us know results.
Code:
'Team Total Net Yards
t = 1
Do
t = t + 1
Loop Until Cells(t, 1) = "TOTAL" And _
Cells(t, 2) = "NET" And _
Cells(t, 3) = "YARDS"
tny1 = Cells(t, 4)
tny2 = Cells(t, 5)
Offset = 1
temp = Cells(c, 1)
If Len(temp) < 4 And Right(temp, 1) = "." Then
temp = temp & Cells(c, 2)
Offset = 2
End If
'Team Net Yards Rushing
t = 1
Do
t = t + 1
Loop Until Cells(t, 1) = "NET" And _
Cells(t, 2) = "YARDS" And _
Cells(t, 3) = "RUSHING"
tny3 = Cells(t, 4)
tny4 = Cells(t, 5)
Offset = 1
temp = Cells(c, 1)
If Len(temp) < 4 And Right(temp, 1) = "." Then
temp = temp & Cells(c, 2)
Offset = 2
End If
Best regards,
-
#6
You are missing an If statement in the first part, probably the best thing would be to post the whole subroutine:
Everything between:
Sub and End Sub
Ciao,
Don.
-
#7
Barrie,
Yes what you gave me got me past my error. Unfortunately I have more errors to figure out. I’m struggling but…
Thanks for the help. I do appreciate it.
Bill
-
#8
I am getting the same compile error: «End If without Block If». My code is as below.
Please guide. I am not able to figure out how to solve this.
Please help.
Sub Process()
Close
Open «D:New folderPlates.txt» For Input As #1
Open «D:New folderOutput.txt» For Output As #2
Do
For i = 1 To 10000
Line Input #1 , x
xStart = False
If Left(x, 5) = «Plate» Or Left(x, 5) = «Eleme» Then
Print #2 , x
Line Input #1 , x
A$ = x
For j = 1 To 12
Line Input #1 , x
End If
If j = 2 And x = «-65.000» Then
xPrint = True
End If
A$ = A$ & vbTab & x
Next
End If
If xPrint Then Print #2 , A$
End If
xPrint = False
If Left(x, 5) = «(HZ 1» Then
For j = 1 To 13
Line Input #1 , x
Next
End If
Next
Loop
Close
End Sub
-
#9
You are getting yourself tied up in knots with your «If» statements and your «For» loops
If using nested «For-Next loops» it always helps when using «Next» to include what next it is. eg:
Code:
For j = 1 to 100
Next [COLOR=#ff0000][B]j[/B][/COLOR]
I tried fixing your code but got tied up myself. Having structured code helps dodge these common headaches. so for example, I’ve used a snippet of your code with correct indentation to help reading:
Code:
Do
For i = 1 To 10000
Line Input [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , x
xStart = False
If Left(x, 5) = "Plate" Or Left(x, 5) = "Eleme" Then
Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=2]#2[/URL] , x
Line Input [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , x
a$ = x
For j = 1 To 12
Line Input [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , x
Next j
End If
Next i
Loop
-
#10
Kind of hard to follow the code without proper indentation but I gave it a go, and here you are.
Rich (BB code):
Sub Process()
Dim i As Long, j As Long, xStart As Boolean
Dim x As String
Dim xPrint As Boolean
Dim A As String
Close
Open "D:New folderPlates.txt" For Input As #1
Open "D:New folderOutput.txt" For Output As #2
Do
For i = 1 To 10000
Line Input #1 , x
xStart = False
If Left(x, 5) = "Plate" Or Left(x, 5) = "Eleme" Then
Print #2 , x
Line Input #1 , x
A$ = x
For j = 1 To 12
Line Input #1 , x
Next j
End If
If j = 2 And x = "-65.000" Then
xPrint = True
End If
A$ = A$ & vbTab & x
If xPrint Then Print #2 , A$
xPrint = False
If Left(x, 5) = "(HZ 1" Then
For j = 1 To 13
Line Input #1 , x
Next j
End If
Next i
Loop
Close
End Sub
That will compile but I don’t know if it’ll actually do what you want it to.