Advanced Filter Searching

[Hornet-Wing]

Hi all, I have been a long time lurker and have had great benefit from old threads around these parts. So thanks However, I have come up with a problem I wasn't able to google myself out of...

Background:
In my company we have an excel spread sheet of procedure titles which link to a PDF of the procedure itself. As the titles do not always reflect content, I decided to add keywords into another column and use a filter to show the user exactly what he was looking for.

V1 Code:

Sub Find_Macro()

Dim SearchingTerm As String
Dim SearchTerm As String


SearchingTerm = Range("D2").Value

SearchTerm = "=*" & SearchingTerm & "*"

ActiveSheet.Range("$F$2:$F$212").AutoFilter Field:=1, Criteria1:=SearchTerm, Operator:=xlAnd

End Sub

V1 worked OK, but it limited me on the number of criteria. Also the search term had to be together for it to be filtered. Not very useful.

So I set about using AdvancedFilter instead. This is what I came up with:

V2 Code:

'Dimension the user's search term as a string in global declerations
Dim SearchTerm As String

Sub Find_Macro()

'####################################################
'#  Searching Macro 2.0 (Uses Advanced Filter)
'#  Code Written By Hornet-Wing
'#  Basic notes have been added to the code for easy understanding
'####################################################

'This line mandates that if an error occurs the code is to stop  exceution
On Error GoTo Etrap

'Give all Variables a dimension
'   -Words is a array of search keywords entered
'   -i is just a variable used in a loop to count
'   -FinalSearch is string that get output after the code is done formatting
'   -countFiltered is a variable which comes out with the number of record found after the filter
Dim Words() As String
Dim i As Integer
Dim FinalSearch As String
Dim countFiltered As Long

'Get the user's search term(s). Cell reference can be changed depending on where your user types the search query
SearchTerm = Range("D2").Value

'This if statemet searches the variable to see if it contains nothing i.e. user left the search cell blank
If SearchTerm = "" Then

    'if the variable SearchTerm is empty the filters will be cleared AND the code will execute Etrap (End the macro)
    ActiveSheet.ShowAllData
    GoTo Etrap

End If

'This line splits the SearchTerm string into an array of individual words
Words = Split(SearchTerm, " ")

'i is defined as 0
i = 0

'This loop goes through all the individual elements in the words array (all the words from the search term)
Do
    'These 3 lines: 1. append * as a prefix and suffix 2. Add the word to the FinalSearch variable 3.add one to i
    Words(i) = "*" & Words(i) & "* "
    FinalSearch = FinalSearch & Words(i)
    i = i + 1

'Loop until i = to the number of words in the words array.
Loop Until i = (UBound(Words) + 1)

'This line removes a space at the end of the FinalSearch string (this was a bug)
FinalSearch = Left(FinalSearch, Len(FinalSearch) - 1)

'Here the code outputs the FinalSearch value into the spreadsheet. The Cell reference can be changed to
'somewhere where you have space.
Range("H2").Value = FinalSearch

'AdvancedFilter is executed here. The 1st range is the range that is List Range, and the second range is
'the range with the criterial (where FinalSearch has been output)
Range("F2:F212").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("H1:H2"), Unique:=False

'This bit of code counts the number of results for the filter. Not necessary, but for aesthetics
countFiltered = WorksheetFunction.Subtotal(3, Range("A3:A212"))
Range("K1").Value = countFiltered

'This is the Etrap, and it exits the macro.
Etrap:
Exit Sub

End Sub

The code is probably quite a bit more complicated than most of the pros here would have done, but it does the job. The code above gives me the ability to search the keywords for even partial matches which is great and what I aimed for!

Problem:
My problem now is the keywords have to come in the be in the same order as typed into the "search" cell in order for the entry to be filtered out. For example if the key word list is: "Apple, Pear, Banana" and my search term is "Apple Banana", the filter will come up with the correct entry above. When I try "Banana Apple" I get nothing, for obvious order reasons. A fix would be to list the key words forwards and backwards. However, I was hoping for a sleeker solution or perhaps a better method of attaining my goal.

Thanks for reading the long winded thread :D

 

[mikerickson]

Try a three row criteria range

Fruit (header)
*Apple*
*Banana*

Note the use of wildcards and that this approach will not exclude "Apple" without Banana.

 

[Hornet-Wing]

Does that not imply: "Apple" OR "Banana

Where as I would like: "Apple" AND "Banana" irrespective of the order.

 

[mikerickson]

Then you could use two columns

Fruit / Fruit
*Apple* / *Banana*


(/ used to indicate column separator)

 

[Hornet-Wing]

Yep, that is exactly what I was looking for! I think I was looking at it the wrong way, probably should have just Googled "AdvancedFilter AND". Now to see if I can get my code to split the word array into several cells and vary the range of the AdvancedFilter criteria (depending on word count).

 

[Hornet-Wing]

Sorry double post. I cant edit my previous

How can I loop through columns?

I know how to loop through rows

j=0

Do
    Range("L" & (3 + j)).Value = Words(j)
    j = j + 1
Loop Until j = (UBound(Words) + 1)

I cannot add j to L as its a string, right?

 

[Hornet-Wing]

Still cant edit previous posts :/

Found a really round about solution.

WARNING BOTCHED CODE COMING UP:

j = 0

Do
    ColumnN = 76 + j
    ColumnL = Chr(ColumnN)
    Range(ColumnL & "3").Value = Words(j)
    j = j + 1
Loop Until j = (UBound(Words) + 1)

Is there a better/sleeker way of doing the above?

 

[Norie]

Use Cells.

I'm not 100% sure which columns you are looping through, but I think it's L.

Column L's column number is 12.

j = 0

Do
  
    Cells(3, j+12)= Words(j)

Loop Until j = (UBound(Words) + 1)