VBA nested IF in excel

  • 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 

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: