excel - VBA在Excel中嵌套IF

Question

我对 VBA 比较陌生。我正在尝试在 VBA 中编写嵌套 IF 的代码。我有以下代码，但输出是错误的。如何使嵌套的 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