VBA nested IF in excel

Question

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.

Use Select Case. The problem is you are nesting the Ifs so it only will test the others if H2 =1 if H2<>1 it will not even test the others as they are inside the first if. — Scott Craner
– Scott Craner, Commented Jan 24, 2019 at 16:56
Except the first IF, replace all other IFs with ELSEIF and in the end just keep one EndIF. Delete the rest of the End Ifs — Siddharth Rout
– Siddharth Rout, Commented Jan 24, 2019 at 16:57
IF isn't a function its a keyword, the return from Range is not an integer, set breakpoints on the areas where you want to check then use the debugger to examine the Ranges. — SPlatten
– SPlatten, Commented Jan 25, 2019 at 7:38

BruceWayne · Accepted Answer · 2019-01-24 17:31:02Z

10

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

edited Jan 24, 2019 at 17:31

answered Jan 24, 2019 at 17:15

BruceWayne

23.5k16 gold badges75 silver badges126 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Scott Craner Over a year ago

Gosh darn you and your patterns. :)

BruceWayne Over a year ago

@ScottCraner - As much as I love lengthy Select Case/If statements, you gotta love a good pattern when it works!

John Coleman Over a year ago

I think that this is the best solution. +1

dnacarlos · Accepted Answer · 2019-01-24 16:58:35Z

4

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

answered Jan 24, 2019 at 16:58

dnacarlos

914 bronze badges

1 Comment

dnacarlos Over a year ago

To clarify, the solution does not constitute as a nested statement. "Nested" if statements are not needed for this case.

Scott Craner · Accepted Answer · 2019-01-24 17:02:28Z

4

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

answered Jan 24, 2019 at 17:02

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Comments

Tom Dee · Accepted Answer · 2019-01-24 16:59:25Z

3

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.

answered Jan 24, 2019 at 16:59

Tom Dee

2,6844 gold badges20 silver badges27 bronze badges

Comments

John Coleman · Accepted Answer · 2019-01-24 17:28:00Z

3

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

answered Jan 24, 2019 at 17:28

John Coleman

52.1k7 gold badges59 silver badges127 bronze badges

Collectives™ on Stack Overflow

VBA nested IF in excel

5 Answers 5

3 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related