0

I have a sheet where users can search for a term. I read the cells into an array and filter the data. They can choose wheater one term or both terms should be in the data.

    If wksSearch.Cells(6, 2).Text = "" Then wksSearch.Cells(6, 2).Value = "xxx"
    If wksSearch.Cells(7, 2).Text = "" Then wksSearch.Cells(7, 2).Value = "xxx"
    
    If wksSearch.OLEObjects("chkBoth").Object.Value Then
        aSearch(0) = "*" & wksSearch.Cells(6, 2).Text & "*" & wksSearch.Cells(7, 2).Text & "*"
        aSearch(1) = "*" & wksSearch.Cells(7, 2).Text & "*" & wksSearch.Cells(6, 2).Text & "*"
    Else
        aSearch(0) = "*" & wksSearch.Cells(6, 2).Text & "*"
        aSearch(1) = "*" & wksSearch.Cells(7, 2).Text & "*"
    End If 
    wksdata.ListObjects("t_Data").Range.AutoFilter Field:=2, _
            Criteria1:=aSearch, Operator:=xlFilterValues

My code works! But if I but a third search term, my code bloats. Is there a better solution? The xxx I could do with each cell in range. Easy, but the search array for the filter creates a headache. At least for me. :) I could do a loop but I think the must be a better filter option

BigBen
  • 46,229
  • 7
  • 24
  • 40
webwohnraum
  • 21
  • 5
  • 2
    `AutoFilter` only supports 2 criteria if using wildcards. – BigBen Feb 10 '21 at 17:09
  • webwohnraum when you have say 3 values what do you want? fowards/backwards all 3 or pairs? e.g. if you have (6, 2), (7, 2), (8, 2) equal to "a", "b", "c" do you want the filter to be "\*a\*b\*c\*" and "\*c\*b\*a" – JohnnieL Feb 10 '21 at 18:56
  • or pairs "\*a\*b\*", "\*a\*c\*", "\*b\*c\*", "\*c\*b\*", "\*c\*a\*", "\*b\*c\*"? In any case the way to do it is to identify as a range your search terms and step through that range to build up your criteria1 array: @bigben I think this can be done if we are just adding to Criteria1 not imposing a thrid Critera - note this doesnt use Criteria2 or am i missing something in your comment? thanks both – JohnnieL Feb 10 '21 at 18:59
  • @JohnnieL - no, see [this answer](https://stackoverflow.com/a/29837083/9245853). – BigBen Feb 10 '21 at 19:01
  • cool jumping through that to this reference is helpful too https://stackoverflow.com/questions/16602872/set-auto-filtering-multiple-wildcards/34822944#34822944 - using autofilter a lot at the moment and it is tantalisingly useful for small stuff and frustrating that it seems limited for bigger applications - tks – JohnnieL Feb 10 '21 at 19:04
  • webwohnraum would still be interested to know how your wildcards propgate – JohnnieL Feb 10 '21 at 19:05
  • 1
    @JohnnieL Sorry I posted this at the end of my workday. Didn't expect answers so soon. I'd like to have two variations: 1) *a*,*b*,*c* 2) *a*b*c*, *a*c*b* , *b*a*c*, ... , *c*b*a* ( all 6 variations) The idea is I have a list of dates in which terms can be found. And the user should be able to search thru those either by selecting any term (Variation 1) or each term ( Variation 2) ist hat clearer ? – webwohnraum Feb 11 '21 at 09:22

0 Answers0