What am I missing in this code: “Else without if e

2020-07-22 18:18发布

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

3条回答
叛逆
2楼-- · 2020-07-22 18:32

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.

查看更多
该账号已被封号
3楼-- · 2020-07-22 18:44

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

查看更多
家丑人穷心不美
4楼-- · 2020-07-22 18:47

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
查看更多
登录 后发表回答