none
VBA Exit code needed for when Criteria in autofilter is not found RRS feed

  • Question

  • I have a macro that emails a range of data based on the combobox selection.When the user selects a value (this becomes MyValBranch in the code) which then becomes Criteria1 in my AutoFilter Field 31 in the code . If it does not find the value of the MyValBranch variable in the AutoFilter range the sheet freezes and I have to kill it though task manager.

    Code:
    Sub Pipeline_EmailBranchNetRegs()
    ActiveSheet.Unprotect
    Dim rng As Range
    Dim OutApp As Object
    Dim OutMail As Object
    Dim Signature As String
    Dim mysht As Worksheet
    Dim myDropDown As Shape
    Dim myValBranch As String
    Dim RegRng As Range
    Dim ADRng As Range
    Dim BMRng As Range
    Dim PrevRegRng As Range
    
    Set mysht = ThisWorkbook.Worksheets("Pipeline")
    Set myDropDown = mysht.Shapes("Drop Down 264")
    myValBranch = myDropDown.ControlFormat.List(myDropDown.ControlFormat.Value)
    If myValBranch = "Choose Branch" Then
            MsgBox "Please Choose a Branch, then try again.", vbExclamation
            Exit Sub
        End If
    Set RegRng = Worksheets("Goals").Range("A:A").Find(What:=myValBranch, LookAt:=xlWhole)
    Set ADRng = Worksheets("Goals").Range("L:L").Find(What:=myValBranch, LookAt:=xlWhole)
    Set BMRng = Worksheets("Goals").Range("M:M").Find(What:=myValBranch, LookAt:=xlWhole)
    Set PrevRegRng = Worksheets("Goals").Range("A:A").Find(What:=myValBranch, LookAt:=xlWhole)
    
    If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
      ActiveSheet.ShowAllData
    End If
    ActiveSheet.Range("$a$6:$AQ$1000").AutoFilter Field:=34, Criteria1:="<>Pre-Approval"
    ActiveSheet.Range("$A$6:$AQ$1000").AutoFilter Field:=31, Criteria1:=myValBranch
    NumberofRegs = RegRng.Offset(0, 9).Value
    AD = RegRng.Offset(0, 11).Value
    BM = RegRng.Offset(0, 12).Value
    Goal = RegRng.Offset(0, 1).Value
    FormattedGoal = Format(Goal, "#,##0")
    
    PrevNumberofRegs = PrevRegRng.Offset(0, 10).Value
    
    Set rng = Nothing
    ' Only send the visible cells in the selection.
    Set rng = ActiveSheet.Range("a6", ActiveSheet.Range("H6").End(xlDown))
    
    If rng Is Nothing Then
        MsgBox "The selection is not a range or the sheet is protected. " & _
               vbNewLine & "Please correct and try again.", vbOKOnly
        Exit Sub
    End If
    
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    With OutMail
       .Display
    End With
    Signature = OutMail.HTMLBody
    strbody = "Snapshot of Current Pipeline and MTD Registration Count:" & "<br />" & "Current Month Goal = " & "$ " & FormattedGoal & "<br />" & ActiveSheet.Range("A1") & " " & ActiveSheet.Range("B1") & "<br />" & ActiveSheet.Range("A2") & Split(ActiveSheet.Range("B2").Text, ".")(0) & "<br />" & "Previous Month Registration Count = " & PrevNumberofRegs & "<br />" & "MTD Registration Count = " & NumberofRegs
    'Split(ActiveSheet.Range("B2").Text, ".")(0)
    With OutMail
        .to = BM
        .cc = AD
        .Subject = myValBranch & " - " & "Net Reg Pipeline"
        .HTMLBody = "<BODY style=font-size:11pt;font-family:Calibri>" & "</p>" & strbody & RangetoHTML(rng) & Signature
        .Display
    End With
    
    On Error GoTo 0
    
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
    
    Set OutMail = Nothing
    Set OutApp = Nothing
    ActiveSheet.Protect
    
    End Sub

    MEC


    Thursday, July 21, 2016 2:18 PM

All replies

  • Hi MEC,

    >> If it does not find the value of the MyValBranch variable in the AutoFilter range the sheet freezes and I have to kill it though task manager

    Do you mean the value of MyValBranch which is not exist in range make your sheet freezes? I made a test with AutoFilter and set Criteria1 with a non-exist value, it works correctly.

    To check whether your issue is related with AutoFilter, I would suggest you comment out this line “ActiveSheet.Range("$A$6:$AQ$1000").AutoFilter Field:=31, Criteria1:=myValBranch” to see whether your excel will freeze. I would suggest you debug your code line by line to check which line cause excel freeze.

    It would be helpful if you could share us your excel file, and then we could test at our side.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, July 22, 2016 11:47 AM