如果在excel中嵌套了VBA

Question

我对VBA比较陌生。我正在尝试为VBA中的嵌套IFs编写代码。我有以下代码，但输出是错误的。怎样才能使嵌套的if函数工作？

    Private Sub CommandButton1_Click()


  If Range("H2") = 1 Then
     Range("X3") = "$CT$10:$CT$150"

       If Range("H2") = 2 Then
     Range("X3") = "$CZ$10:$CZ$150"

      If Range("H2") = 3 Then
     Range("X3") = "$DF$10:$DF$150"

     If Range("H2") = 4 Then
     Range("X3") = "$DL$10:$DL$150"

     If Range("H2") = 5 Then
     Range("X3") = "$DR$10:$DR$150"

     If Range("H2") = 6 Then
     Range("X3") = "$DX$10:$DX$150"

     If Range("H2") = 7 Then
     Range("X3") = "$ED$10:$ED$150"

     If Range("H2") = 8 Then
     Range("X3") = "$EJ$10:$EJ$150"

     If Range("H2") = 9 Then
     Range("X3") = "$EP$10:$EP$150"

     If Range("H2") = 10 Then
     Range("X3") = "$EV$10:$EV$150"

     If Range("H2") = 11 Then
     Range("X3") = "$FB$10:$FH$150"

     If Range("H2") = 12 Then
     Range("X3") = "$FH$10:$FH$150"



  End If
  End If
  End If
  End If
  End If
  End If
  End If
  End If
  End If
  End If
  End If
  End If



End Sub

代码只有在H2 = 1的情况下才能正常工作，否则就不能工作。

Answer 1

后退一步-根据H2的值，每列之间有一个设置的距离。

你不需要IF或Select Case，只需要一些快速的算术：

Sub t()
Dim startCol As Long, tmpCol As Long

startCol = 98 ' This is the `CT` Column

With Worksheets("Sheet1") ' Change as needed
    tmpCol = startCol + 6 * (.Range("H2").Value - 1)

    Dim rng As Range
    Set rng = .Range(.Cells(10, tmpCol), .Cells(150, tmpCol))
    .Range("X3").Value = rng.Address
End With

End Sub

Answer 2

Microsoft Excel中IF-THEN-ELSE语句的语法为：

If condition_1 Then
   result_1

ElseIf condition_2 Then
  result_2

...

ElseIf condition_n Then
   result_n

Else
   result_else

End If

Answer 3

对于这种情况，我喜欢使用Select Case：

Private Sub CommandButton1_Click()
    With Worksheets("Sheet1") ' change to your sheet
        Select Case .Range("H2")
            Case 1
                .Range("X3") = "$CT$10:$CT$150"
            Case 2
                .Range("X3") = "$CZ$10:$CZ$150"

            '... The rest

            Case Else
                .Range("X3") = ""
        End Select
    End With
End Sub