AutoFilter: Setting Criteria1 to an Array for "Any of these words" type filtering

[sheldonbeswift]

Greetings!

I have a "Keywords" field and am using AutoFilter to support three types of "searches" (filters):

1. All of these words
2. This exact word or phrase
3. Any of these words

The first two seem to be working fine, however I can't seem to get the last ("Any of these words") to work...any help would be much appreciated!

Please Login or Register  to view this content.

Note that the MsgBox line I've commented out returns the correct values...thus I suspect it's something to do with the "r.AutoFilter Field:=2, Criteria1:=searchTerms, Operator:=xlFilterValues" line I've bolded above.

[AB33]

"The first two seem to be working fine"

Arrays Can ONLY take 2 elements. The same with OR and AND operators.

[sheldonbeswift]

AB33 - I'm sorry if I wasn't clear...what I meant is: the first two types of filters are working (1. All of these words, 2. This exact word or phrase); the third type (3. Any of these words) doesn't work at all. It sounds like you're saying that the searchTerms array can only be two elements--and thus any additional elements would be thrown out--however that filter returns 0 results, even if there is only one element in the array (e.g. if one were to enter "Test" in the Keywords field).

Am I missing something, or did I misinterpret your response?

[AB33]

That is right, can only take two values, but the syntax might not be right. This line gives some idea.

Please Login or Register  to view this content.

[sheldonbeswift]

Removed duplicate post.

[sheldonbeswift]

AB33 - Based on the following, my syntax appears to be correct (I've tried putting searchTerms in quotes, but that didn't work either): http://msdn.microsoft.com/en-us/libr.../ff194617.aspx

Please Login or Register  to view this content.

I must be missing something....when I enter the keyword "Keyword1", the filter is Equals "Keyword1". If I enter the keywords "Keyword1 Keyword2", the filter Equals "Keyword2" (the filter is aways Equals [the last keyword]). In both examples I would expect to see "Contains" (versus Equals), and in the latter example I would expect to see Contains "Keyword1" or Contains "Keyword2".

Attachment 250336

[AB33]

There is a problem with the attachment, I could not open it.

Please Login or Register  to view this content.

Is filtering not ranges, but a single range A1, which is the heading for filter.

[JosephP]

can you post the attachment again? your original code works ok for me-assuming your autofilter range does have two columns ;-)

[sheldonbeswift]

JosephP - Sure, please see attached. The original code works, just not the way I want it to ("Any of these words" should be filter on Contains "Keyword1 or Contains "Keyword2" or Contains "Keyword3").

7-15-2013 11-53-04 AM.jpg

[JosephP]

a picture is of no use I'm afraid

however-you cannot use an array for 'contains' in an autofilter-it has to be an exact match on the array elements