The syntax for If
allows two variants:
-
«Inline»
If {bool-expression} Then {do something}
-
«Block»
If {bool-expression} Then {do something} End If
An End If
token is illegal when you’re using the «inline» syntax.
So this (i.e. removing the End If
tokens) makes your code compilable again:
If ProdE <= 0.1 Then DaysLateE = 2
If 0.1 < ProdE <= 0.2 Then DaysLateE = 3
If 0.2 < ProdE <= 0.3 Then DaysLateE = 4
If 0.3 < ProdE <= 0.4 Then DaysLateE = 5
If 0.4 < ProdE <= 0.5 Then DaysLateE = 6
If 0.5 < ProdE <= 0.6 Then DaysLateE = 7
If 0.6 < ProdE <= 0.7 Then DaysLateE = 8
If 0.7 < ProdE <= 0.8 Then DaysLateE = 9
If 0.8 < ProdE <= 0.9 Then DaysLateE = 10
If 0.9 < ProdE <= 1 Then DaysLateE = 11
However, as @Rohan K suggested, a better option would be to use a Select Case
construct, because right now, all these conditions are evaluated all the time — with a Select Case
block, execution would exit the Select
block after finding a matching condition, and as a bonus you gain readabiilty:
Select Case ProdE
Case Is <= 0.1
DaysLateE = 2
Case Is <= 0.2
DaysLateE = 3
Case Is <= 0.3
DaysLateE = 4
Case Is <= 0.4
DaysLateE = 5
Case Is <= 0.5
DaysLateE = 6
Case Is <= 0.6
DaysLateE = 7
Case Is <= 0.7
DaysLateE = 8
Case Is <= 0.8
DaysLateE = 9
Case Is <= 0.9
DaysLateE = 10
Case Is <= 1
DaysLateE = 11
Case Else
'DaysLateE = ??
End Select
So, what happens when (hadn’t read where ProdE
is greater than or equal to 1?ProdE
came from, nevermind) It seems there’s a straight linear relationship between the value of ProdE
and DaysLateE
— you could try to come up with a formula to calculate it instead.
This probably isn’t perfect, but comes pretty close:
DaysLateE = Int(ProdE * 10 - 0.000000000001) + 2
And then you don’t need If
or Select
blocks.
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 &amp;lt; 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.
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
- Remove From My Forums
-
Вопрос
-
This is module files
Option Explicit
Sub Additem()
Dim ItemRow As Long, AvailRow As Long
With Sheet1
If .Range(«B5»).Value = Empty Then Exit Sub
On Error Resume Next
.Shapes(«itempic»).Delete
On Error GoTo 0
ItemRow = .Range(«B5»).Value ‘item row
AvailRow = .Range(«K999»).End(xlUp).Row + 1 ‘firs avail row
.Range(«B6»).Value = AvailRow ‘Set Receipt Row
.Range(«E3»).Value = Sheet2.Range(«B» & ItemRow).Value ‘Item Name
.Range(«F6»).Value = Sheet2.Range(«D» & ItemRow).Value ‘Item Price
.Range(«F8»).Value = 1 ‘Default Item Qty To 1‘Add Item Detail to receipt
.Range(«K» & AvailRow).Value = .Range(«E3»).Value ‘Item Name
.Range(«L» & AvailRow).Value = .Range(«F8»).Value ‘Item Qty
.Range(«M» & AvailRow).Value = .Range(«f6»).Value ‘Item Price
.Range(«N» & AvailRow).Value = «=L» & AvailRow & «*M» & AvailRow ‘Total Price formula‘On Error Resume Next
If Dir(Sheet2.Range(«E» & ItemRow).Value, vbDirectory) <> «» Then
With .Pictures.Insert(Sheet2.Range(«E» & ItemRow).Value)
With .ShapeRange
.LockAspectRatio = msoTrue
.Height = 45
.Name = «ItemPic»
End With
End With
With .Shapes(«ItemPic»)
.Left = Sheet1.Range(«D6»).Left
.Top = Sheet1.Range(«D6»).Top
.Visible = msoCTrue
End With
End If
‘On Error Goto 0
.Range(«E10:F10»).ClearContents ‘Clear Iteam Iteam
.Range(«E10»).Select
End With
End Sub………………………………………………………………………………………
This is sheet 1 code
Private Sub Worksheet_Change(ByVal Target As Range)
‘on change of item, if row found and add to receipt
If Not Intersect(Target, Range(«E10»)) Is Nothing And Range(«E10»).Value <> Empty Then AdditemEnd Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
‘On Selection of Receipt Item, load Item details
If Not Intersect(Target, Range(«K10:N9999»)) Is Nothing And Range(«K» & Target.Row).Value <> Empty Then Additem
Range(«B6»).Value = Target.Row ‘Selected Row
Range(«B4»).Value = True
Range(«E3»).Value = Range(«K» & Target.Row).Value ‘Item Name
Range(«F8»).Value = Range(«L» & Target.Row).Value ‘Item Qty
Range(«F6»).Value = Range(«M» & Target.Row).Value ‘Item Price
Range(«B4»).Value = False
End IfEnd 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 folder\Plates.txt» For Input As #1
Open «D:\New folder\Output.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 folder\Plates.txt" For Input As #1
Open "D:\New folder\Output.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.