multiple if statements

[13lack13lade]

Hi All,

i am creating some error checking on a template of mine and i need to have a code that runs a bunch of if statements and exits if it doesnt meet the criteria at any step of the process, and the end result if it passes all criteria is 'msgbox go' but i cant seem to get it right - excel 2007

Sheets("USERPAGE").Activate
If IsEmpty(Sheets("USERPAGE").Range("D74").Value) = False Then
If Sheets("USERPAGE").Range("F74") = vbNullString Then
           MsgBox ("Please check Min Nights - Min Nights mandatory when using FOC")
           Exit Sub
        Else
             
        
    If IsEmpty(Sheets("USERPAGE").Range("B71").Value) = True Then
    MsgBox ("Please check product code - Mandatory")
    Exit Sub
    Else
        
    If IsEmpty(Sheets("USERPAGE").Range("C71").Value) = True Then
    MsgBox ("Please check City Code - Mandatory")
    Exit Sub
    Else

    
     If IsEmpty(Sheets("USERPAGE").Range("J71").Value) = True Then
     MsgBox ("Please check child ages - Mandatory")
     Exit Sub
     Else

     If IsEmpty(Sheets("USERPAGE").Range("L71").Value) = True Then
     MsgBox ("Please check infant age - Mandatory")
     Exit Sub
     Else
        
    If IsEmpty(Sheets("USERPAGE").Range("D75").Value) = False Then
     If Sheets("USERPAGE").Range("F75") = vbNullString Then
     MsgBox ("Fraction required when Part-day Checkout used")
     Exit Sub
     Else
     MsgBox ("go")
    End If

[millz]

Hi 13lack13lade, that's a problem due to poor indentations.


How your chunk of code looks like when properly indented:

Sheets("USERPAGE").Activate
If IsEmpty(Sheets("USERPAGE").Range("D74").Value) = False Then
    If Sheets("USERPAGE").Range("F74") = vbNullString Then
        MsgBox ("Please check Min Nights - Min Nights mandatory when using FOC")
        Exit Sub
    Else
    
        If IsEmpty(Sheets("USERPAGE").Range("B71").Value) = True Then
            MsgBox ("Please check product code - Mandatory")
            Exit Sub
        Else
            
            If IsEmpty(Sheets("USERPAGE").Range("C71").Value) = True Then
                MsgBox ("Please check City Code - Mandatory")
                Exit Sub
            Else
            
                If IsEmpty(Sheets("USERPAGE").Range("J71").Value) = True Then
                    MsgBox ("Please check child ages - Mandatory")
                    Exit Sub
                Else
                
                    If IsEmpty(Sheets("USERPAGE").Range("L71").Value) = True Then
                        MsgBox ("Please check infant age - Mandatory")
                        Exit Sub
                    Else
                    
                        If IsEmpty(Sheets("USERPAGE").Range("D75").Value) = False Then
                            If Sheets("USERPAGE").Range("F75") = vbNullString Then
                                MsgBox ("Fraction required when Part-day Checkout used")
                                Exit Sub
                            Else
                                MsgBox ("go")
                            End If

It becomes very visible that you are missing many End If's. I didn't check through your If statements though, I assume they are working as expected.

[13lack13lade]

Its fine ive figured it out, i separated them into different subs, then created a new sub to call them all, works fine

Thanks for your help

[13lack13lade]

where do the endif's go because ive tried seperating them all into seperate if statements and also putting all the end ifs at the end and none of which seem to work.

if i separate them out then it runs but doesnt do anything..

[lancer102rus]

try this

Sheets("USERPAGE").Activate
If Not IsEmpty(Sheets("USERPAGE").Range("D74").Value) Then
   If Sheets("USERPAGE").Range("F74") = vbNullString Then
        MsgBox "Please check Min Nights - Min Nights mandatory when using FOC"
        Exit Sub
    ElseIf IsEmpty(Sheets("USERPAGE").Range("B71").Value) Then
        MsgBox "Please check product code - Mandatory"
        Exit Sub
    ElseIf IsEmpty(Sheets("USERPAGE").Range("C71").Value) Then
        MsgBox "Please check City Code - Mandatory"
        Exit Sub
    ElseIf IsEmpty(Sheets("USERPAGE").Range("J71").Value) Then
        MsgBox "Please check child ages - Mandatory"
        Exit Sub
    ElseIf IsEmpty(Sheets("USERPAGE").Range("L71").Value) Then
        MsgBox "Please check infant age - Mandatory"
        Exit Sub
    End If
End If

If Not IsEmpty(Sheets("USERPAGE").Range("D75").Value) Then
    If Sheets("USERPAGE").Range("F75") = vbNullString Then
        MsgBox "Fraction required when Part-day Checkout used"
        Exit Sub
    End If
End If

MsgBox "go"

[millz]

I personally would avoid heavily nested If-Else statements, so I would do it this way, separating each condition:

Sheets("USERPAGE").Activate
If IsEmpty(Sheets("USERPAGE").Range("D74").Value) = False Then 'I assume D74 indicates FOC
    If Sheets("USERPAGE").Range("F74") = vbNullString Then
        MsgBox ("Please check Min Nights - Min Nights mandatory when using FOC")
        Exit Sub
    End If
End If

If IsEmpty(Sheets("USERPAGE").Range("B71").Value) = True Then
    MsgBox ("Please check product code - Mandatory")
    Exit Sub
End If

If IsEmpty(Sheets("USERPAGE").Range("C71").Value) = True Then
    MsgBox ("Please check City Code - Mandatory")
    Exit Sub
End If

If IsEmpty(Sheets("USERPAGE").Range("J71").Value) = True Then
    MsgBox ("Please check child ages - Mandatory")
    Exit Sub
End If

If IsEmpty(Sheets("USERPAGE").Range("L71").Value) = True Then
    MsgBox ("Please check infant age - Mandatory")
    Exit Sub
End If

If IsEmpty(Sheets("USERPAGE").Range("D75").Value) = False Then 'I assume D75 indicates Part-day Checkout
    If Sheets("USERPAGE").Range("F75") = vbNullString Then
        MsgBox ("Fraction required when Part-day Checkout used")
        Exit Sub
    End If
End If

MsgBox ("go")

[13lack13lade]

Thanks lancer, mine just ran the statements but didnt actually stop the if statements, your code is exactly what i was looking for!