End If without block If error

Question

I Wrote this code. I dont understand why I got error : End If without block If

Sub mytest()

Dim i As Integer
Dim s As Integer
Dim j As Integer
Dim p As Integer
Dim k As Integer
s = 0

With Worksheets("mysheet")
.Range("B28:B75").Select

For i = 28 To 75
        If Cells(i, 2).Value > 0 Then Cells(i, 2).Interior.Color = RGB(255, 255, 255)
        s = s + 1
        End If
Next i

.Range("A28:A75").Select

For j = 28 To 75

    If Cells(i, 2).Value = 0 Then Cells(i, 2).Interior.Pattern = xlPatternLightDown
    Cells(i, 2).Interior.Color = RGB(255, 255, 255)
    End If

Next j

p = 75 - s
For k = 1 To s
    Cells(s + k, 1).Interior.Color = RGB(18, 0, 0)
Next k

End With

The End If clause wasnt omitted. I dont understand why I got error

Note that your error is not Block If without End If but End If without block If. You don't have an missing End If, you have too much ;) (see answer from Macro man below or this answer ) . — Vincent G
– Vincent G, Commented May 13, 2016 at 10:08

SierraOscar · Accepted Answer · 2016-05-13 09:54:50Z

4

If you write an If statement on a single line this:

If Foo = Bar Then FooBar()

You don't need to use an End If because the action is executed on the same line and so the end of the statement is implied (The compiler knows anything after Then on the same line is conditional, so you don't need to tell it where your conditional code ends)

If you place the action on a separate line:

If Foo = Bar Then
    FooBar()
End If

Then you have to explicitly tell the compiler where the conditional code ends by using an End If because there is no other way for it to know.

answered May 13, 2016 at 9:54

SierraOscar

17.7k6 gold badges44 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2018-05-08 21:49:10Z

Move statements after Then on new line. IMHO VBA expects single line statement if you use it with code after then, so next line with code without end if throws this error. I also recomends you to read more about VBA here for better range and cell specification.

but for your error use something like this

Sub mytest()

Dim i As Integer
Dim s As Integer
Dim j As Integer
Dim p As Integer
Dim k As Integer
s = 0

With Worksheets("mysheet")
.Range("B28:B75").Select

For i = 28 To 75
        If Cells(i, 2).Value > 0 Then
        Cells(i, 2).Interior.Color = RGB(255, 255, 255)
        s = s + 1
        End If
Next i

.Range("A28:A75").Select

For j = 28 To 75

    If Cells(i, 2).Value = 0 Then
    Cells(i, 2).Interior.Pattern = xlPatternLightDown
    Cells(i, 2).Interior.Color = RGB(255, 255, 255)
    End If

Next j

p = 75 - s
For k = 1 To s
    Cells(s + k, 1).Interior.Color = RGB(18, 0, 0)
Next k

End With

Collectives™ on Stack Overflow

End If without block If error

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related