Ошибка end if without block if vba excel
задание: верно ли, что max и min элементы матрицы А (5,5) расположены по разные стороны её главной диагонали?
Sub maa9()
Dim A(1 To 5, 1 To 5) As Integer
Dim i As Byte, j As Byte, imax As Byte, imin As Byte, jmax As Byte, jmin As Byte, min As Integer, max As Integer
For i = 1 To 5
For j = 1 To 5
A(i, j) = Cells(i, j)
Next j: Next i
max = A(1, 1)
min = A(1, 1)
imax = 1
imin = 1
jmax = 1
jmin = 1
For i = 1 To 5
For j = 1 To 5
If A(i, j) > max Then max = A(i, j): imin = i: jmax = j Else If A(i, j) < min Then min = A(i, j): imin = i: jmin = j
End If ЗДЕСЬ ОШИБКУ ВЫДАЁТ
Next i
Next j
If imax > jmax Then If imin < jmin Then Cells(1, "A") = "да"
Cells(1, "B") = "нет"
ActiveSheet.Range("a1:e5").Select
With Selection
.Font.Name = "times new roman"
.Font.Size = 14
.Font.Color = 120
.Font.Bold = True
.Font.Italic = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Borders.Color = RGB(0, 200, 0)
End With
Я исправила не так, как вы мне посоветовали, но программа у меня пошла. только вот требуемого результата не получается, скажите пожалуйста, может у меня ошибка в условии, там где уже проверка идёт. я ввела произвольную матрицу так чтобы максимальный и мин элементы были по разные стороны гл диагонали, должно быть выведено "да" но у меня "нет" все время пишет. в чём проблема? ? вот в таком виде программа
Sub maa9()
Dim A(1 To 5, 1 To 5) As Integer
Dim i As Byte, j As Byte, imax As Byte, imin As Byte, jmax As Byte, jmin As Byte, min As Integer, max As Integer
For i = 1 To 5
For j = 1 To 5
A(i, j) = Cells(i, j)
Next j: Next i
max = A(1, 1)
min = A(1, 1)
imax = 1
imin = 1
jmax = 1
jmin = 1
For i = 1 To 5
For j = 1 To 5
If A(i, j) > max Then max = A(i, j): imin = i: jmax = j Else If A(i, j) < min Then min = A(i, j): imin = i: jmin = j
Next j
Next i
If imax > jmax Then If imin < jmin Then Cells(1, "F") = "да"
Cells(1, "G") = "нет"
ActiveSheet.Range("a1:e5").Select
With Selection
.Font.Name = "times new roman"
.Font.Size = 14
.Font.Color = 120
.Font.Bold = True
.Font.Italic = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Borders.Color = RGB(0, 200, 0)
End With
Может быть, неправильно там где селлз.
> If A(i, j) > max Then max = A(i, j): imin = i: jmax = j Else If A(i, j) < min Then min = A(i, j): imin = i: jmin = j
> End If ЗДЕСЬ ОШИБКУ ВЫДАЁТ
Два IF и только один END IF.
Попробуй слитно написать: ElseIf.
Не нужно пытаться написать все в одну строку, пиши каждый оператор с новой строки:
If A(i, j) > max
max = A(i, j)
imin = i
jmax = j
ElseIf A(i, j) < min
min = A(i, j)
imin = i
jmin = j
End If
How to Fix the “End If without block If” Error
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.
Looking at the example above, we see that the syntax for using a simple If statement is
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.
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”.
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.
Rule 2: Extra End If statements
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
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
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:
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”.
Lakshmi Ramakrishnan is an automation specialist, with experience as a trainer, a solution architect, a tester, and a developer. She's built VBA tools across everything from accounting to IT, and enjoys sharing her expertise and knowledge to help beginners.
Else Without If Error in VBA: Why Is it Happening?
What you’re seeing is a compile error that indicates that an Else (or ElseIf) keyword was not preceded by a correct If statement.
Meaning, the compiler found an Else statement (which it will highlight for the user) without seeing an If statement in the lines above it. This is an error because it violates the correct syntax of an If-Then-Else statement. The correct syntax is as follows:
For using the shorthand syntax for if statement that specifies a condition and an action (for when the condition is met):
If [Test Expression] Then [Action]
To use the standard syntax for an If statement that specifies a condition and an action (for when the condition is met) and an alternate action for when the condition is not met:
If [Test Expression] Then
Else
End if
For specifying more than one condition (and their actions):
If [Test Expression] Then
ElseIf [Test Expression 2] Then
Else
End if
The compiling error “Else Without If” occurs when “ If [Test Expression] Then “ is missing or written incorrectly. Let’s discuss the common causes of the error further in details below with examples.
Missing If Statement
When VBA compiler sees that the Else keyword is not preceded by a recognizable (correct) If statement, it generates an error ‘Else without If’. For every Else, there must be an If statement. However, for every If, we do not necessarily need an else statement.
Example 1: Missing If Statement
In this example, we display an input box that takes the user’s input and stores it in x. Then we encounter an Else keyword, but there was no If statement prior to it. This indeed will cause a compiler error: Else Without If.
To solve this problem, we just need to add an If statement. An If statement consists of If [condition] Then .
Following good indentation practices is crucial for recognizing whether each if-else-then logic consists of the necessary keywords (If statement, Else keyword, End If keyword).
If [condition] Then
Else
If [condition] Then
Else
End If
End If
Example 2: Else statement is inside a loop and If statement is outside
You might be surprised that you are getting the error when you already have the If statement present in the code and placed prior to the Else keyword and written correctly. The problem might not be apparent at first glance. But if we look closer, we will see that the scope of the If statement is different from that of the else statement.
Everything between the If statement and the End If keyword belongs to the scope of this If statement. Other scopes can also exist in the code due to the existence of loops such as For Next loop, Do Until loop or For Each Next loop.
A scope exists when a logical statement requires multiple keywords to indicate the start and end points of the statement. A problem occurs if you overlap scopes in a way that causes one to be partially placed inside the other. The correct way to have multiple scopes work with each other is to have one totally placed inside the other.
In the following example, If [condition] Then is followed by the beginning of a For loop followed by the Else keyword. This separates the If statement and the Else keyword and causes the error to show up.
To fix the issue, we ensure that the If logical statement as a whole is fully encompassed within the For loop. Or the For loop is fully encompassed within the If logical statement Action; between If and Else or between Else and End If.
As a general rule, if a section of your code has a some status due to having a starting and ending point (If – Else, Else – End If, For – Next, Do – Until) then you cannot start another section (scope) within it without ending it within the first section.
Start Point (If, Else, For, Do)
Another Start Point
Another End Point
End Point (Else, End If, Next, Until)
To apply this to our example, we change it to the following code;
Incorrect If Statement
Example 3: Placing the action on the same line with If Statement
Another very common mistake that is often made is to write the If statement in a way that is not compatible with utilizing the Else keyword.
If we want to use the Else keyword, we must place the action part of the If-Then-Else logic in the next line after the if statement. If we are not using the Else keyword, then we can place the action on the same line as the If statement and we do not write the Else keyword or the End If keyword.
When the action (that should be carried out if the condition is true) is placed on the same line as the If statement, then the If statement is considered complete. This is considered a shorthand way of writing the If – then logic.
That is why when compiler encounters an Else keyword after that, it does not find an If statement that belongs to the Else keyword, because that If statement has been completed with placing the action on the line as the If statement . The following code is NOT correct and will cause the compiler error: Else without If.
The action ( MsgBox "x value is equal to 1" ) needs to be placed on the next row after the If statement in order to be able to have an Else statement used in the If-Then-Else logic. In the following code, we moved in the action statement to the next line, which fixes the problem.
We now have covered all the possible causes of compile error: Else without if. To recap, if you get this error, check whether
1) There is an If statement in place that precedes the Else keyword.
2) The if statement line does not contain anything other than If [condition] Then .
3) Verify that the If statement and the Else keyword are not separated by another scope or section, such as loops.
About the author Mahmoud Mostafa
Mahmoud is a highly experienced VBA coder with 8 years of experience in Excel automation. He was a full time Excel Automation Specialist for a US government Medicare & Medicaid contractor for 4 years. He earned his Bachelor's and Master's degree from North Dakota State University in Industrial Engineering and Management.
This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.
Answered by:
Question
I am new to VBA and i am not very good with it.
i am having an error with the following code: Block If without end if
here is my code:
Thank you in anticipation for your support.
Answers
1. Remove the colon after each instance of the word "Then"
2. Also, "Var" is used by Excel as the name of a function.
Change Var to something else.
3. Add the line "Option Explicit" as the first line in the code module and declare all variables.
Dim vVar as Variant
Dim Output as String
4. You don't need "( )" when using a Msgbox unless you are returning a value from it.
Msgbox Output works just fine.
All replies
1. Remove the colon after each instance of the word "Then"
2. Also, "Var" is used by Excel as the name of a function.
Change Var to something else.
3. Add the line "Option Explicit" as the first line in the code module and declare all variables.
Dim vVar as Variant
Dim Output as String
4. You don't need "( )" when using a Msgbox unless you are returning a value from it.
Msgbox Output works just fine.
The reason you are getting that error is because of the ":" after "Then:". Try this:
Sub Recalculateplan()
Var = Sheets("Information").Cells(4, 2).Value
If (Var = "$H$3") Then
Output = "The Cheapest Plan Is Strawberry at " & Range("H3").Value
MsgBox (Output)
'B11
Range("B11").Value = Output
End If
If (Var = "$H$4") Then
Output = "The Cheapest Plan Is Banana at " & Range("H4").Value
MsgBox (Output)
'B11
Range("B11").Value = Output
End If
If (Var = "$H$5") Then
Output = "The Cheapest Plan Is Apple at " & Range("H5").Value
MsgBox (Output)
'B11
Range("B11").Value = Output
End If
If (Var = "$H$6") Then
Output = "The Cheapest Plan Is Kiwi at " & Range("H6").Value
MsgBox (Output)
'B11
Range("B11").Value = Output
End If
End Sub
Dev centers
Learning resources
Community
Support
Programs
© 2022 Microsoft
This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.
Asked by:
Question
This is module files
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 Additem
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 If
Читайте также: