Autofilter applying to multiple columns

[Pasty]

Hi there,

I'm trying to get my code to search for processes in relation to actions. Each action can have up to 8 different processes against it sat in columns N:U.

The code is this:

filterCriteria = InputBox("Please enter what process you're searching for")
Application.ScreenUpdating = False


   With Sheets("Risk")
   .AutoFilterMode = False
   With .Range("N:U")
   .AutoFilter
   .AutoFilter Field:=1, Criteria1:=filterCriteria, VisibleDropDown:=False
   .AutoFilter Field:=2, Criteria1:=filterCriteria, VisibleDropDown:=False
   .AutoFilter Field:=3, Criteria1:=filterCriteria, VisibleDropDown:=False
   .AutoFilter Field:=4, Criteria1:=filterCriteria, VisibleDropDown:=False
   .AutoFilter Field:=5, Criteria1:=filterCriteria, VisibleDropDown:=False
   .AutoFilter Field:=6, Criteria1:=filterCriteria, VisibleDropDown:=False
   .AutoFilter Field:=7, Criteria1:=filterCriteria, VisibleDropDown:=False
   .AutoFilter Field:=8, Criteria1:=filterCriteria, VisibleDropDown:=False
   End With
   End With
   With Sheets("Compliance")
   .AutoFilterMode = False
   With .Range("N:U")
   .AutoFilter
   .AutoFilter Field:=1, Criteria1:=filterCriteria, VisibleDropDown:=False
   .AutoFilter Field:=2, Criteria1:=filterCriteria, VisibleDropDown:=False
   .AutoFilter Field:=3, Criteria1:=filterCriteria, VisibleDropDown:=False
   .AutoFilter Field:=4, Criteria1:=filterCriteria, VisibleDropDown:=False
   .AutoFilter Field:=5, Criteria1:=filterCriteria, VisibleDropDown:=False
   .AutoFilter Field:=6, Criteria1:=filterCriteria, VisibleDropDown:=False
   .AutoFilter Field:=7, Criteria1:=filterCriteria, VisibleDropDown:=False
   .AutoFilter Field:=8, Criteria1:=filterCriteria, VisibleDropDown:=False
   End With
   End With
   With Sheets("Audit")
   .AutoFilterMode = False
   With .Range("N:U")
   .AutoFilter
   .AutoFilter Field:=1, Criteria1:=filterCriteria, VisibleDropDown:=False
   .AutoFilter Field:=2, Criteria1:=filterCriteria, VisibleDropDown:=False
   .AutoFilter Field:=3, Criteria1:=filterCriteria, VisibleDropDown:=False
   .AutoFilter Field:=4, Criteria1:=filterCriteria, VisibleDropDown:=False
   .AutoFilter Field:=5, Criteria1:=filterCriteria, VisibleDropDown:=False
   .AutoFilter Field:=6, Criteria1:=filterCriteria, VisibleDropDown:=False
   .AutoFilter Field:=7, Criteria1:=filterCriteria, VisibleDropDown:=False
   .AutoFilter Field:=8, Criteria1:=filterCriteria, VisibleDropDown:=False
   End With
   End With

The problem I am having is that if I input for example "IT: Disaster Recovery" as the search criteria it filters out a different value on each sheet other than the one I am searching for.

Would anyone know why this is?

 

[xenou]

1) you might want to test the results of your input box. I changed your statement to use the input box method (not the inputbox function) and added a type argument - this will ensure you are using a text value as the criteria.

2) I also added a debug.print for kicks...so you can see the criteria entered in the immediate window.

3) It occurs to me you will have a problem in the routine if the criteria is not in the column being filtered...i.e., you can't filter for a value that is not in the column. With so many columns, are you sure they will all have the criteria you enter? This strikes me as a potential problem for long-term use, unless you know all sheets and all columns will have the criteria. You may also want to test your user's entry to make sure it is a valid criteria for filtering on.

4) I tried to shorten this code with loops...hope it works.

Sub test()
Dim mySheetsArray As Variant
Dim msg As String

msg = "Please enter what process you're searching for."
filterCriteria = Application.InputBox(msg, Type:=2) 'type 2 is text
Debug.Print filterCriteria

Application.ScreenUpdating = False

'Loop through 3 sheets and 8 filterfields on each sheet
mySheetsArray = Array("Risk", "Compliance", "Audit")
For x = 1 To 3
    With Sheets(mySheetsArray(x))
        
        .AutoFilterMode = False
        
        With .Range("N:U")
        .AutoFilter
            For y = 1 To 8
                .AutoFilter Field:=x, _
                Criteria1:=filterCriteria, VisibleDropDown:=False
            Next y
        End With
        
    End With
Next x

Application.ScreenUpdating = True
End Sub

 

[Pasty]

Hi,

Thanks for your help - I am trying a different tactic since I posted this by put a formula in the IV column saying whether its true or false that these columns contain the search value. I gave this a try and it keeps say x has not been defined so I did the Dim bit and still didn't work.

Regards,

Matt