CODEWITHSUNDEEP
excelvbaif-statement
n4pster
n4pster

Reputation: 103

VBA nested IF in excel

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.

Upvotes: 4

Views: 3823

Answers (5)

BruceWayne
BruceWayne

Reputation: 23283

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

Upvotes: 10

John Coleman
John Coleman

Reputation: 51998

Your cases are consecutive integers. Just put it in an array:

Private Sub CommandButton1_Click()

    Dim addresses As Variant

    addresses = Array("$CT$10:$CT$150", "$CZ$10:$CZ$150", "$DF$10:$DF$150", "$DL$10:$DL$150", _
                    "$DR$10:$DR$150", "$DX$10:$DX$150", "$ED$10:$ED$150", "$ED$10:$ED$150", _
                    "$EJ$10:$EJ$150", "$EP$10:$EP$150", "$EV$10:$EV$150", "$FB$10:$FH$150", "$FH$10:$FH$150")

    Range("X3").Value = addresses(Range("H2").Value - 1)

End Sub

Upvotes: 3

Scott Craner
Scott Craner

Reputation: 152505

For something like this I like to use 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

Upvotes: 4

Tom Dee
Tom Dee

Reputation: 2674

You are nesting the if statements so to work down the code they all need to be true which is impossible. You should format it like this:

If Range("H2") = 1 Then
    Range("X3") = "$CT$10:$CT$150"
ElseIf Range("H2") = 2 Then
    Range("X3") = "$CZ$10:$CZ$150"
ElseIf Range("H2") = 3 Then
    Range("X3") = "$DF$10:$DF$150"

...

End If

However in your situation I would advice using a select case to make the code look cleaner and easier to read.

Upvotes: 3

dnacarlos
dnacarlos

Reputation: 91

The syntax for the IF-THEN-ELSE statement in Microsoft Excel is:

If condition_1 Then
   result_1

ElseIf condition_2 Then
  result_2

...

ElseIf condition_n Then
   result_n

Else
   result_else

End If

Upvotes: 4

Related Questions