Compile error: End If without block If

Question

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

1 Change "ElseIf" in "ElseIf cel.Offset(0, 8).Value" to "If" 2 Delete "Else: End If" — Siddharth Rout
– Siddharth Rout, Commented Mar 22, 2014 at 16:49
Just a note to others who may have trouble with this error: If you use the Single-line syntax (in @SiddharthRout's answer) nested within another If statement, and you put an End If for the inside and outside statements, you will get this compiler error. — ZX9
– ZX9, Commented Jul 23, 2015 at 17:02

Siddharth Rout · Accepted Answer · 2014-03-22 16:50:45Z

6

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 Rout

150k18 gold badges212 silver badges258 bronze badges

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

Collectives™ on Stack Overflow

Compile error: End If without block If

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related