Msg box for nil return

[Godders199]

Hello, I have written this maybe basic code, to filter a sheet by criteria selected in 3 cells, this works fine, however rather than go to a blank sheet with just the head rows when there are no cases, i would prefer a msg box to just say, "no matches , change criteria and search again" I have copied various codes i have found but not got any to work. Do i need to change my existing code? or is there something i can just add on the end.


With Sheets("cases available ").Range("a:ab")
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilter.ShowAllData
.AutoFilter Field:=8, Criteria1:=Sheets("instructions ").Range("c23").Value, Operator:=xlFilterValues
.AutoFilter Field:=11, Criteria1:=Sheets("instructions ").Range("c35").Value, Operator:=xlFilterValues
.AutoFilter Field:=27, Criteria1:="=*" & Sheets("instructions ").Range("c26") & "*", Operator:=xlFilterValues
End With
Sheets("cases available ").Select


Any help as always appreciated.

 

[Fluff]

What row is the autofilter on?

 

[Godders199]

hi its on row 1

 

[Fluff]

Untested, but try

With Sheets("cases available ").Range("a:ab")
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilter.ShowAllData
.AutoFilter Field:=8, Criteria1:=Sheets("instructions ").Range("c23").Value, Operator:=xlFilterValues
.AutoFilter Field:=11, Criteria1:=Sheets("instructions ").Range("c35").Value, Operator:=xlFilterValues
.AutoFilter Field:=27, Criteria1:="=*" & Sheets("instructions ").Range("c26") & "*", Operator:=xlFilterValues
End With
[COLOR=#0000ff]If Range("A1").SpecialCells(xlVisible).Count = 1 Then
   MsgBox "No matches"
   Exit Sub
End If[/COLOR]
Sheets("cases available ").Select

 

[Godders199]

Thanks, I changed "a1" to E1 as column A does contain some blanks, column E will always have data. however Message box always shows even if the return is not zero . Is this because my marco Button is on the instructions tab, therefore it is looking at E1 on this tab, or is it looking at the cases available tab?

With Sheets("cases available ").Range("a:ab")
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilter.ShowAllData
.AutoFilter Field:=8, Criteria1:=Sheets("instructions ").Range("c23").Value, Operator:=xlFilterValues
.AutoFilter Field:=11, Criteria1:=Sheets("instructions ").Range("c35").Value, Operator:=xlFilterValues
.AutoFilter Field:=27, Criteria1:="=*" & Sheets("instructions ").Range("c26") & "*", Operator:=xlFilterValues
End With

If Range("e1").SpecialCells(xlVisible).Count = 1 Then
MsgBox "No matches"
Exit Sub
End If
Sheets("cases available ").Select
End Sub

 

[Fluff]

Is this because my marco Button is on the instructions tab, therefore it is looking at E1 on this tab

Yes that's correct. Try this instead

With Sheets("cases available ").Range("a:ab")
   If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilter.ShowAllData
   .AutoFilter Field:=8, Criteria1:=Sheets("instructions ").Range("c23").Value, Operator:=xlFilterValues
   .AutoFilter Field:=11, Criteria1:=Sheets("instructions ").Range("c35").Value, Operator:=xlFilterValues
   .AutoFilter Field:=27, Criteria1:="=*" & Sheets("instructions ").Range("c26") & "*", Operator:=xlFilterValues
End With
If Sheets("cases available ").Range("E1").SpecialCells(xlVisible).Count = 1 Then
   MsgBox "No matches"
   Exit Sub
End If
Sheets("cases available ").Select

 

[Godders199]

HI, this returns this error. RUntime errro 6, Overflow
when i debug the following line is highlighted

If Sheets("cases available ").Range("a1").SpecialCells(xlVisible).Count = 1 Then

the autofilter has returned 5 results

 

[Fluff]

OK, how about

Dim Usdrws As Long
Usdrws = Sheets("cases available ").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row

With Sheets("cases available ").Range("a:ab")
   If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilter.ShowAllData
   .AutoFilter Field:=8, Criteria1:=Sheets("instructions ").Range("c23").Value, Operator:=xlFilterValues
   .AutoFilter Field:=11, Criteria1:=Sheets("instructions ").Range("c35").Value, Operator:=xlFilterValues
   .AutoFilter Field:=27, Criteria1:="=*" & Sheets("instructions ").Range("c26") & "*", Operator:=xlFilterValues
End With
If Sheets("cases available ").Range("E1:E" & Usdrws).SpecialCells(xlVisible).Count = 1 Then
   MsgBox "No matches"
   Exit Sub
End If
Sheets("cases available ").Select

 

[Godders199]

Thanks FLuff, that extra bit of code did the trick..

Both thanks

 

[Fluff]

Glad to help & thanks for the feedback