vba Excel IF -Then-Else 条件

Excel IF -Then-Else condition

提问by SeanC

Kindly assist to run the below Excel macro. I think i'm missing some thing. Logic not working.

请协助运行以下 Excel 宏。我想我错过了一些东西。逻辑不通。

Sub RiskGrade()

Dim Value1 As String

Sheets("Sheet1").Select
If (Cells(10, 2) = "BX") Then Cells(12, 4) = -8
Exit Sub

ElseIf (Cells(10, 2) = "GX") Then Cells(12, 4) = -7
Exit Sub

Else
    For i = 12 To 14

        Value1 = Right(Trim(Sheet1.Cells(i, 2)), 1)
        'MsgBox Value1
        Select Case Value1
            Case "W"
                Cells(i, 4) = 1
            Case "H", "S", "R", "F", "G"
                Cells(i, 4) = 2
            Case "D"
                Cells(i, 4) = 3
            Case "C"
                Cells(i, 4) = 4
            Case "B"
                Cells(i, 4) = 5
            Case "A"
                Cells(i, 4) = 6
            Case "*"
                Cells(i, 4) = 7
            Case "M"
                Cells(i, 4) = 8
            Case "E"
                Cells(i, 4) = 9
            Case OTHER
                Cells(i, 4) = -9
        End Select
        Exit For
    Next i
End If

End Sub

回答by Jay Wick

You've started an IF statement and in the same line added an action, which means it's not going to expect an END IF or ELSE after that.

您已经启动了一个 IF 语句并在同一行中添加了一个操作，这意味着在此之后不会期望 END IF 或 ELSE。

In VB

在VB中

IF condition THEN action

IF 条件 THEN 动作

is a single IF statement.

是单个 IF 语句。

Consider

考虑

If (Cells(10, 2) = "BX") Then
  Cells(12, 4) = -8
  Exit Sub
ElseIf (Cells(10, 2) = "GX") Then
  Cells(12, 4) = -7
  Exit Sub
...

回答by SeanC

1. Case OTHERshould be Case Else
2. The Exit Forwill terminate the loop after the first time through, so that needs to be removed, or changed to where you want the processing to abort

3. The If ... Thenshould be multi-line, or singleline.

   • Multi-Line:
     If (Cells(10, 2) = "BX") Then
Cells(12, 4) = -8
Exit Sub
ElseIf ...

   • Single Line:
     If (Cells(10, 2) = "BX") Then Cells(12, 4) = -8 : Exit Sub
ElseIf ...
     (note use of :to separate commands in same Ifstatement)

1. Case OTHER应该 Case Else
2. 在Exit For第一时间通过后，将终止循环，所以需要被移除或更改到要处理中止

3. 本If ... Then应该是多行，单行或。

   • 多线：
     If (Cells(10, 2) = "BX") Then
Cells(12, 4) = -8
Exit Sub
ElseIf ...

   • 单行：（ 注意使用来分隔同一语句中的命令）
     If (Cells(10, 2) = "BX") Then Cells(12, 4) = -8 : Exit Sub
ElseIf ...
:If

回答by Qbik

You should use OPTION EXPLICIT, because it helps to spot errors when typos are applied to keywords.

您应该使用OPTION EXPLICIT，因为它有助于在将拼写错误应用于关键字时发现错误。

In current shape you specified unexpected behaviour by using Case OTHERwhich should be Case Else, there is no keyword OTHERin VBA so OTHERis interpreted as variable of Variantdatatype, but Case Elsewould be interpreted properly as "in other case do ...".

在当前形状中，您通过使用Case OTHERwhich should be指定了意外行为，VBA 中Case Else没有关键字OTHER，因此OTHER被解释为Variant数据类型的变量，但Case Else会被正确解释为“在其他情况下做...”。

Also using Exit Forafter Selectstops macro execution on i=12, 13 and 14 are then not applied.

也使用Exit ForafterSelect停止宏执行i=12，然后不应用 13 和 14。

Option Explicit
Sub RiskGrade()

Dim Value1 As String

Sheets("Sheet1").Select

If (Cells(10, 2) = "BX") Then 
Cells(12, 4) = -8
Exit Sub

ElseIf (Cells(10, 2) = "GX") Then 
Cells(12, 4) = -7
Exit Sub

Else
    For i = 12 To 14

        Value1 = Right(Trim(Sheet1.Cells(i, 2)), 1)
        Debug.Print Value1 'CTRL+G for immediate window

        Select Case Value1
            Case "W"
                Cells(i, 4) = 1
            Case "H", "S", "R", "F", "G"
                Cells(i, 4) = 2
            Case "D"
                Cells(i, 4) = 3
            Case "C"
                Cells(i, 4) = 4
            Case "B"
                Cells(i, 4) = 5
            Case "A"
                Cells(i, 4) = 6
            Case "*" 'I don't know what behaviour in will cause, does it spot '*' only, or any char
                Cells(i, 4) = 7
            Case "M"
                Cells(i, 4) = 8
            Case "E"
                Cells(i, 4) = 9
            Case Other ' not OTHER
                Cells(i, 4) = -9
        End Select
        'Exit For ' - why exit after first iteration - guess it's unnecessary procedure ?
    Next i
End If

End Sub

结束子

回答by user3023582

If you remove both the "exit sub" it should work. With the "exit sub" it seems that you are closing your if without "end if"

如果您同时删除“exit sub”，它应该可以工作。使用“exit sub”，您似乎正在关闭 if 而不使用“end if”