CODEWITHSUNDEEP
vbaexcel
John
John

Reputation: 183

End If without block If error

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

Upvotes: 1

Views: 2670

Answers (2)

SierraOscar
SierraOscar

Reputation: 17637

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.

Upvotes: 4

Luboš Suk
Luboš Suk

Reputation: 1546

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

Upvotes: 1

Related Questions