Embedded If Then Statement

[TheShaunMichael]

Have a large If, ElseIf statement. This goes 3 layers deep. The problem is, the "End If" portion of the 3rd layer is exiting the entire loop rather than just the local function.

Here's a snippit. At the end of the "If Counter > 1 Then" segment is the problem. I've tried embedding the If Counter segment in the "For Y = 26 to 29" section but I have the same problem.

ElseIf Worksheets(X).Cells(27, 13) = "" Then
Counter = 0
For Y = 26 To 29
If Worksheets(X).Cells(Y, 13) = "" Then
Counter = Counter + 1
End If
Next
If Counter > 1 Then
Worksheets(X).Cells(27, 13) = Me.tbMeetingDate
End If

 

[vaskov17]

Make sure the below cells really are empty if you want the code below them to execute.

Worksheets(X).Cells(26, 13)
Worksheets(X).Cells(27, 13)
Worksheets(X).Cells(28, 13)
Worksheets(X).Cells(29, 13)

also are you sure you need counter>1 and not counter>=1 or counter>0?

 

[TheShaunMichael]

They are definitely empty! And yes the counter works as it should.

Again, rather than going to the next "ElseIf" statement, it proceeds to the very end.

 

[vaskov17]

If it goes into the If statement then it will never go into the ElseIfs under it.

 

[TheShaunMichael]

Okay. Is there a way to execute a command based on the counter value that will NOT affect the following ElseIf statements?

 

[vaskov17]

Probably, but you would have to post the remaining code.

 

[TheShaunMichael]

If Me.lbGeneralEvaluator <> "" Then

X = Me.lbGeneralEvaluator
LastAssignmentRole = Worksheets(X).Cells(Rows.Count, 24).End(xlUp).Row
Worksheets(X).Cells(LastAssignmentRole + 1, 24) = "General Evaluator"
Worksheets(X).Cells(LastAssignmentRole + 1, 25) = Me.tbMeetingDate

''Project 3
If Worksheets(X).Cells(18, 13) = "" Then

Worksheets(X).Cells(18, 13) = Me.tbMeetingDate​

''Project 10
ElseIf Worksheets(X).Cells(54, 13) = "" Then

Worksheets(X).Cells(54, 13) = Me.tbMeetingDate​

''Project 2
ElseIf Worksheets(X).Cells(14, 13) = "" Then

Counter = 0
For Y = 12 To 14

If Worksheets(X).Cells(Y, 13) = "" Then

Counter = Counter + 1​

End If​

Next

If Counter > 1 Then

Worksheets(X).Cells(14, 13) = Me.tbMeetingDate​

End If
​

''Project 5
ElseIf Worksheets(X).Cells(27, 13) = "" Then

Counter = 0
For Y = 26 To 29

If Worksheets(X).Cells(Y, 13) = "" Then

Counter = Counter + 1​

End If​

Next

If Counter > 1 Then

Worksheets(X).Cells(27, 13) = Me.tbMeetingDate​

End If
​

''Project 7
ElseIf Worksheets(X).Cells(39, 13) = "" Then

Counter = 0
For Y = 38 To 41

If Worksheets(X).Cells(Y, 13) = "" Then

Counter = Counter + 1​

End If​

Next

If Counter > 2 Then

Worksheets(X).Cells(39, 13) = Me.tbMeetingDate​

End If
​

''Project 8
ElseIf Worksheets(X).Cells(47, 13) = "" Then

Counter = 0
For Y = 45 To 47

If Worksheets(X).Cells(Y, 13) = "" Then

Counter = Counter + 1​

End If​

Next

If Counter > 1 Then

Worksheets(X).Cells(47, 13) = Me.tbMeetingDate​

End If
​

End If
​

End If

 

[vaskov17]

If Me.lbGeneralEvaluator <> "" Then
    X = Me.lbGeneralEvaluator
    LastAssignmentRole = Worksheets(X).Cells(Rows.Count, 24).End(xlUp).Row
    Worksheets(X).Cells(LastAssignmentRole + 1, 24) = "General Evaluator"
    Worksheets(X).Cells(LastAssignmentRole + 1, 25) = Me.tbMeetingDate

    ''Project 3
    If Worksheets(X).Cells(18, 13) = "" Then Worksheets(X).Cells(18, 13) = Me.tbMeetingDate
        
    ''Project 10
    If Worksheets(X).Cells(54, 13) = "" Then Worksheets(X).Cells(54, 13) = Me.tbMeetingDate
        
    ''Project 2
    If Worksheets(X).Cells(14, 13) = "" Then
        Counter = 0
        For Y = 12 To 14
            If Worksheets(X).Cells(Y, 13) = "" Then Counter = Counter + 1
        Next
        
        If Counter > 1 Then Worksheets(X).Cells(14, 13) = Me.tbMeetingDate
    End If
        
    ''Project 5
    If Worksheets(X).Cells(27, 13) = "" Then
        Counter = 0
        For Y = 26 To 29
            If Worksheets(X).Cells(Y, 13) = "" Then Counter = Counter + 1
        Next
        
        If Counter > 1 Then Worksheets(X).Cells(27, 13) = Me.tbMeetingDate
    End If
        
    ''Project 7
    If Worksheets(X).Cells(39, 13) = "" Then
        Counter = 0
        For Y = 38 To 41
            If Worksheets(X).Cells(Y, 13) = "" Then Counter = Counter + 1
        Next
        
        If Counter > 2 Then Worksheets(X).Cells(39, 13) = Me.tbMeetingDate
    End If
    
    ''Project 8
    If Worksheets(X).Cells(47, 13) = "" Then
        Counter = 0
        For Y = 45 To 47
            If Worksheets(X).Cells(Y, 13) = "" Then Counter = Counter + 1
        Next
    
        If Counter > 1 Then Worksheets(X).Cells(47, 13) = Me.tbMeetingDate
    End If
End If

 

[TheShaunMichael]

Ah I wish that could work. BUT - if Project 3 or Project 10 are true, for example, I do NOT want to run the code that follows. Is there an "end" type of string I can put in to skip to the end of this segment (keeping in mind there is another segment of text following the final End If)?

 

[vaskov17]

Sounds like some projects can run together while some cannot, can you list which projects can do that and which can't. If only one project should run at any time then your original code should have been working correctly.