Stopping an Autofilter macro if no value is found...

[chazrab]

This code searches for a criteria value catval between the given dates d1 & d2:

    Range("A1:E1").Select
    Selection.AutoFilter
    With Sheets("REGISTER").Range("B1").CurrentRegion.Offset(1, 0)
    .AutoFilter Field:=2, Criteria1:=catval
    .AutoFilter Field:=3, Criteria1:=">=" & d1, Operator:=xlAnd, Criteria2:="<=" & d2
    .SpecialCells(xlVisible).Copy Sheets("REPORT").Range("A1")
    .AutoFilter
    End With

..works great as long as there is a value to be found. This requires populating the REGISTER sheet before it runs correctly. But what happens there is no value to be found like on day 1 of each month. The code bugs out. I would like it to generate a Msgbox that says "No value found" and simply stop running and return user control to the active sheet. Any ideas on how this is done and most importantly, at what point in the code the Msgbox line would be placed ?
Thanks for any help.
cr

 

[Coding4Fun]

You could try using error handler like this.

Sub Filter_Stuff()

On Error GoTo myError:


    Range("A1:E1").Select
    Selection.AutoFilter
    With Sheets("REGISTER").Range("B1").CurrentRegion.Offset(1, 0)
    .AutoFilter Field:=2, Criteria1:=catval
    .AutoFilter Field:=3, Criteria1:=">=" & d1, Operator:=xlAnd, Criteria2:="<=" & d2
    .SpecialCells(xlVisible).Copy Sheets("REPORT").Range("A1")
    .AutoFilter
    End With

Exit Sub

myError:
MsgBox ("Something Went Wrong")
Err.Clear
Exit Sub


End Sub