CODEWITHSUNDEEP
excelvbaif-statement
actuario99
actuario99

Reputation: 103

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

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

Upvotes: 3

Views: 863

Answers (3)

Samuel Hulla
Samuel Hulla

Reputation: 7089

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

  1. Single-line syntax
  2. 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.

Upvotes: 7

Mathieu Guindon
Mathieu Guindon

Reputation: 71157

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".

Upvotes: 10

Valon Miller
Valon Miller

Reputation: 1156

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

Upvotes: 6

Related Questions