Excel VBA IF then IF statement

Question

I am using an "If Then If msgbox vbYesno" Statement and I am not sure how to get out correctly of it (I know Goto is not clean).

Can someone tell me what my mistake is? I did not find anyone using something similar.

Sub IF_THEN_IF()

If Sheet1.Range("A1").Value > 500 Then
    If MsgBox("A1 > 500, Is this correct", vbYesNo, "Amount of Lines") = vbYes Then
        Range("H11").FormulaR1C1 = "My Formula"
    Else
        GoTo Jump
    End If
Else

Jump:    
    Range("H11").FormulaR1C1 = "I have Jumped"

End If

End Sub

If you describe what you are trying to achieve, not just your code, perhaps we can come up with a better solution — Shai Rado, Oct 16 '17 at 13:36

score 2 · Accepted Answer · answered Oct 16 '17 at 13:59

You can leave your procedure if you're not going to "jump", jumping otherway:

Sub IF_THEN_IF()
    If Sheet1.Range("A1").Value > 500 Then
        If MsgBox("A1 > 500, Is this correct", vbYesNo, "Amount of Lines") = vbYes Then
            Range("H11").FormulaR1C1 = "My Formula"
            Exit Sub
        End If
   End If
   'Jump
    Range("H11").FormulaR1C1 = "I have Jumped"
End Sub

Another option is use boolean variable:

Sub IF_THEN_IF()
    Dim Jump as Boolean
    Jump = Sheet1.Range("A1").Value <= 500
    If Not Jump Then Jump = MsgBox("A1 > 500, Is this correct", vbYesNo, "Amount of Lines") <> vbYes
    If Jump Then
        Range("H11").FormulaR1C1 = "I have Jumped"
    Else
        Range("H11").FormulaR1C1 = "My Formula"
    End If
End Sub

score 1 · Answer 2 · answered Oct 16 '17 at 13:37

1

This answer is in response to the comment you made on the answer of Vityaya.

Turn the code from Jump into a sub routine and call it as necessary.

Sub IF_THEN_IF()

With Sheet1 

    If .Range("A1").Value > 500 Then

        Dim res as Variant
        res = MsgBox("A1 > 500, Is this correct", vbYesNo, "Amount of Lines")

        If res = vbYes Then
            .Range("H11").FormulaR1C1 = "My Formula"
        Else
            Jump
        End If

    Else   

        Jump

    End If

End Sub

Sub Jump()
   'code here
End Sub

answered Oct 16 '17 at 13:37

Scott Holtzman

27,099
5
37
72

Now I see what the OP wanted. – Vityata Oct 16 '17 at 14:06
Yeah that quite the kind of solution I was expecting (more clear/clean) eventhough I was hoping to stay in the same Sub – Pierre44 Oct 18 '17 at 05:48

Vityata · Answer 3 · 2017-10-16T14:06:06.037

0

A possible option is like this:

Sub IfThenElse()

    With Sheet1
        If .Range("A1").value > 500 Then
            If MsgBox("A1 > 500, Is this correct", vbYesNo, "Amount of Lines") = vbYes Then
                .Range("H11") = "My Formula"
            Else
                .Range("H11") = "I have Jumped"
            End If
        Else
            .Range("H11") = "I have Jumped"
        End If
    End With

End Sub

You have two embedded ifs. That's all. In the answer of Excel VBA IF then IF statement @Scott Holtzmann, you may see how to avoid writing "I have jumped" twice, by following the DRY method.

edited Oct 16 '17 at 14:06

answered Oct 16 '17 at 13:15

Vityata

42,633
8
55
100

as a note to OP. The outer `Else` and line below can be totally removed without effect on the syntax as well. – Scott Holtzman Oct 16 '17 at 13:25
I already came up with this but my issue is that the line "I have jumped" as to come if the msgbox is vbNo and if Range("A1").value < 500. But the issue is that the "I have jumped" part is really long in my real Code. and I wouldn t like to write it twice. Any idea? – Pierre44 Oct 16 '17 at 13:25

score 0 · Answer 4 · answered Oct 16 '17 at 13:49

0

You can use the AND operator

Sub IF_THEN_IF()    
If Sheet1.Range("A1").Value > 500 And _
   MsgBox("A1 > 500, Is this correct", vbYesNo, "Amount of Lines") = vbYes Then

    Range("H11").FormulaR1C1 = "My Formula"

Else

    Range("H11").FormulaR1C1 = "I have Jumped"

End Sub

answered Oct 16 '17 at 13:49

Rosetta

2,665
1
13
29

1

But not same code, as this code will always ask with message box, regardless of >500 or not. VB doesn't have short circuited And operator. – LS_ᴅᴇᴠ Oct 16 '17 at 14:02
@ScottHoltzman don't mention it, it will attract competition. Oops missed the `End If` line, but nvm... :D – Rosetta Oct 16 '17 at 14:02
@LS_ᴅᴇᴠ, may be OP wants to confirm everything. :p – Rosetta Oct 16 '17 at 14:09
@Rosetta Maybe, but it is not what original code does. – LS_ᴅᴇᴠ Oct 16 '17 at 14:10

Excel VBA IF then IF statement

4 Answers4