custom autofilter problem

[Jshugs]

Hello-

I'm trying to create a macro that uses a form where someone can search for a part number (or part of a part number) in a field and the macro will search for that part. I'm using the custom autofilter "contains" option to do this.

 Sheets(MainSheet).Range("$B$1:$O$" & DataLastRow).AutoFilter Field:=Col - 1, Criteria1:= _
        SearchWords(jj), Operator:=xlFilterValues

If Sheets(MainSheet).Range(Cells(2, Col), Cells(DataLastRow, Col)).SpecialCells(xlCellTypeVisible).Count > 0 Then

On Error Resume Next

 Set FiltRange = Sheets(MainSheet).Range(Cells(2, Col), Cells(DataLastRow, Col)).SpecialCells(xlCellTypeVisible)

On Error GoTo ERR1
        
End If

It works fine, except when someone types in something that returns no results, like SearchWords(jj)="*adafsfd*" I've tried everything to fix it. The line below gives the error

Sheets(MainSheet).Range(Cells(2, Col), Cells(DataLastRow, Col)).SpecialCells(xlCellTypeVisible)

I tried catching that error, but I still get a runtime "No cells were found" Does anyone know how I can fix this? Maybe check if no cells are visible or something?


THANKS IN ADVANCE!

-Jon

 

[Jshugs]

I think i made a little mistake. It is actually a "text filter", not a "custom filter", but I don't think it matters too much.


thanks!
-Jon

 

[texasalynn]

I usually put in a count after applied the filter to see if there are more than 1 rows.

 

[Jshugs]

how do you count? I thought I tried that, and it still comes up with an error. Refer to my code above.

thanks!
-Jon

 

[Norie]

Jon

What's a 'text filter'?

What you seem to be using is an Auto Filter and you don't seem to be qualifying all your ranges. ie not providing a worksheet reference.

This is your code with the ranges properly referenced.

    With Sheets(MainSheet)

        .Range("$B$1:$O$" & DataLastRow).AutoFilter Field:=Col - 1, Criteria1:=SearchWords(jj), Operator:=xlFilterValues
 
        If .Range(.Cells(2, Col), .Cells(DataLastRow, Col)).SpecialCells(xlCellTypeVisible).Count > 0 Then
 
            Set FiltRange = .Range(.Cells(2, Col), .Cells(DataLastRow, Col)).SpecialCells(xlCellTypeVisible)
 
        End If

    End With

It probably won't solve the problem you describe but it's always a good idea.

What error message are you getting?

Is it something to do with there being no visible cells?

If it is then perhaps you could add a check to see if the filter is going to return any records before you go ahead with it.

One way to do that might be to use Application.WorksheetFunction.CountIf.

 

[Jshugs]

Looks like Application.WorksheetFunction.CountIf worked!!! Thank you!