- A+

Category：Languages

I am relatively new to VBA. I am trying to write a code for nested IFs in VBA. I have the following code, yet the output is wrong. How can I make the nested if function work?

` 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 `

The code somehow works only if H2 = 1. Otherwise it does not work.

Take a step back - you have a set distance between each column, depending on `H2`

value.

You don't need `IF`

or `Select Case`

, just some quick arithmetic:

`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 `