What am I missing in this code: "Else without if error"?

Question

I am trying to run this code which first declares wsD and then WsS depending on a cell in the "Data" tab. I keep getting an

else without if error.

Set wsD = ThisWorkbook.Sheets("Data")

    If wsD.Range("C4") = "AL" Then Set wsS = ThisWorkbook.Sheets("AL_Sim")
    ElseIf wsD.Range("C4") = "COLL" Then Set wsS = ThisWorkbook.Sheets("COLL_Sim")
    ElseIf wsD.Range("C4") = "COMP" Then Set wsS = ThisWorkbook.Sheets("COMP_Sim")
    ElseIf wsD.Range("C4") = "GL" Then Set wsS = ThisWorkbook.Sheets("GL_Sim")
    ElseIf wsD.Range("C4") = "EPL" Then Set wsS = ThisWorkbook.Sheets("EPL_Sim")
    ElseIf wsD.Range("C4") = "LAW" Then Set wsS = ThisWorkbook.Sheets("LAW_Sim")
    ElseIf wsD.Range("C4") = "POL" Then Set wsS = ThisWorkbook.Sheets("POL_Sim")

    End If
Next

Is this a for loop? Can’t see the for in your code. Paste all ur code pls — Cohen, Sep 25 '18 at 21:18

score 10 · Answer 1 · answered Sep 25 '18 at 21:27

The If statement has two legal syntaxes.

Inline:

If {bool-expression} Then {statement}

And block:

If {bool-expression} Then
    {statements}
End If

So if there's a statement on the same line following the Then keyword, VBA parses the If statement as the inline syntax.

Thus, since the statement is complete, the next statement beginning with ElseIf makes no sense to the compiler: there's an "else without if".

Samuel Hulla · Answer 2 · 2019-05-26T09:49:52.707

There is a little known fact (or how I like to refer it, Microsoft's little practical joke..) in vba, that the If..Then..End If condition has actually two established forms:

Single-line syntax
Multi-line syntax

You basically mixed up multi-line and single-line syntax into one mush, as per MSDN:

What follows the Then keyword is examined to determine whether a statement is a single-line If. If anything other than a comment appears after Then on the same line, the statement is treated as a single-line If statement. If Then is absent, it must be the start of a multiple-line If...Then...Else.

Example of a single-line statement:

If <condition> Then <expression>

and multi-line (what you attempted to do):

If <condition> Then
  <expression>
ElseIf <condition> Then 'optional, note comment isn't evaluated as single-line expression
  <expression>
End If

So to sum it up, your code threw an error, because the ElseIf got evaluated as an <expression> after the If..Then statement, instead to evaluating to a conditional like you intended it to.

The unwritten rule goes, always write in multi-line syntax.
Not only you will steer clear of unnecessary errors (like you just encountered), but it is also inarguably easier to read and the standard amongst coders.

score 6 · Accepted Answer · answered Sep 25 '18 at 21:17

You need the actions on new lines:

If wsD.Range("C4") = "AL" Then
    Set wsS = ThisWorkbook.Sheets("AL_Sim")
ElseIf wsD.Range("C4") = "COLL" Then
    Set wsS = ThisWorkbook.Sheets("COLL_Sim")
ElseIf wsD.Range("C4") = "COMP" Then
    Set wsS = ThisWorkbook.Sheets("COMP_Sim")
ElseIf wsD.Range("C4") = "GL" Then
    Set wsS = ThisWorkbook.Sheets("GL_Sim")
ElseIf wsD.Range("C4") = "EPL" Then
    Set wsS = ThisWorkbook.Sheets("EPL_Sim")
ElseIf wsD.Range("C4") = "LAW" Then
    Set wsS = ThisWorkbook.Sheets("LAW_Sim")
ElseIf wsD.Range("C4") = "POL" Then
    Set wsS = ThisWorkbook.Sheets("POL_Sim")
End If

Select Case would be better here:

Select Case wsD.Range("C4")
    Case Is = "AL"
        Set wsS = ThisWorkbook.Sheets("AL_Sim")
    Case Is = "COLL"
        Set wsS = ThisWorkbook.Sheets("COLL_Sim")
    Case Is = "COMP"
        Set wsS = ThisWorkbook.Sheets("COMP_Sim")
    Case Is = "GL"
        Set wsS = ThisWorkbook.Sheets("GL_Sim")
    Case Is = "EPL"
        Set wsS = ThisWorkbook.Sheets("EPL_Sim")
    Case Is = "LAW"
        Set wsS = ThisWorkbook.Sheets("LAW_Sim")
    Case Is = "POL"
        Set wsS = ThisWorkbook.Sheets("POL_Sim")
End Select

You could simplify the `Select Case` too, based on the concatenation of the Range value and "_Sim". — BigBen, Sep 25 '18 at 21:20
Agreed, there are other modifications too, like using `With` to get rid of all the `ThisWorkbook` references, but I don't know what the rest of the code looks like, so I want to provide a solution with the least assumptions being made. — Valon Miller, Sep 25 '18 at 21:24
You're welcome, if this solution resolved the issue you were experiencing please mark it as the correct answer. — Valon Miller, Sep 25 '18 at 21:27

What am I missing in this code: "Else without if error"?

3 Answers3

Linked