Sub Exiting Without Moving Through Completely

[reberryjr]

For some reason, the code below isn't moving on to the Else statement when the value of R26 isn't > 0. Can someone help me understand where I went wrong in my code please?

Sub Assign_MD1()
Application.ScreenUpdating = False
Dim mTP, mSumm, mMD1 As Worksheet
Dim Response As VbMsgBoxResult
Set mTP = ThisWorkbook.Sheets("Total_Population")
Set mSumm = ThisWorkbook.Sheets("Summaries")
Set mMD1 = ThisWorkbook.Sheets("MD1")
If mTP.FilterMode = True Then mTP.ShowAllData
'If Len(frm_IntakeHome.cobo_ImportType.Value) = 0 Then
'    MsgBox "Please enter the import type you are processing."
'    If Response = vbOK Then frm_IntakeHome.cobo_ImportType.SetFocus
'    Cancel = True
'    Exit Sub
'Else
If mSumm.Range("R26").Value > 0 Then
    Response = MsgBox("Please review the records that show as past SLA, and make any necessary corrections.", vbOKCancel)
    If Response = vbOK Then
        With mTP
            On Error Resume Next
            .UsedRange.AutoFilter Field:=8, Criteria1:="<" & Date
            .UsedRange.AutoFilter Field:=13, Criteria1:="Y"
        End With
    mTP.Activate
    'Exit Sub
Else
    If mTP.FilterMode = True Then mTP.ShowAllData
    SortAscending mTP, "J1", "M1"
    With mTP
        On Error Resume Next
        .UsedRange.AutoFilter Field:=10, Critiera1:=""
        .UsedRange.AutoFilter Field:=13, Critiera1:="Y"
    End With
    End If
End If
    
mTP.Activate
Application.ScreenUpdating = True
End Sub

 

[Fluff]

Get rid of this

On Error Resume Next

both times it occurs & see what happens.

 

[reberryjr]

Ok, I did that, but there was no change in the result.

**Edit** I'm not sure if it matters (I don't think that it would), but just in case...the value of R26 is actually a formula that is summing 4 other fields.

 

[Eric Golf]

Hi,

I think your first nested If statement is missing an End IF, so the Else is being applied to it?

If mSumm.Range("R26").Value > 0 Then
    Response = MsgBox("Please review the records that show as past SLA, and make any necessary corrections.", vbOKCancel)
    If Response = vbOK Then
        With mTP
            On Error Resume Next
            .UsedRange.AutoFilter Field:=8, Criteria1:="<" & Date
            .UsedRange.AutoFilter Field:=13, Criteria1:="Y"
        End With
    mTP.Activate
    'Exit Sub
[COLOR="#FF0000"]    End IF[/COLOR]
Else

Insert line in Red above & try.

Hope this helps,

Eric

 

[Fluff]

Ok, what is the Value in R26, when it doesn't work?

 

[Fluff]

Good spot Eric.

The End If is in the wrong place it should be

If mSumm.Range("R26").Value > 0 Then
    Response = MsgBox("Please review the records that show as past SLA, and make any necessary corrections.", vbOKCancel)
    If Response = vbOK Then
        With mTP
            On Error Resume Next
            .UsedRange.AutoFilter Field:=8, Criteria1:="<" & Date
            .UsedRange.AutoFilter Field:=13, Criteria1:="Y"
        End With
    mTP.Activate
    'Exit Sub
    End If
Else
    If mTP.FilterMode = True Then mTP.ShowAllData
    SortAscending mTP, "J1", "M1"
    With mTP
        On Error Resume Next
        .UsedRange.AutoFilter Field:=10, Critiera1:=""
        .UsedRange.AutoFilter Field:=13, Critiera1:="Y"
    End With
End If

 

[reberryjr]

Thank you @Eric Golf! After you pointed that out, I noticed that it was odd that the code was skipping to the End If way at the bottom. Much appreciated!