EXCEL VBA: Is it possible to use the Filter function in VBA to filter more than 1 search string?

Question

Hello,

I sometimes use the Filter function to filter a 1-dimensional array.

The syntax is:
Filter(sourcearray, match, [ include, [ compare ]])

The "match" argument is used to search the string we want.
It seems that we can only search ONE string, say "cat".

Is it possible to search more than one string, say "cat" and "dog"?
How can this be done? In Excel, we can use filters to do this. I wonder if we can do this in VBA.

I tried using a variable to represent "match", like this:

ElseIf opt_1 = True Then
Dim var As String
var = "cat"
vArray2 = Filter(vArray1, var, True, vbTextCompare)

It works fine!

But how can I add "dog" to var? (so that the filtered rows contain either "cat" or "dog")

Thanks
Leon

Answer 1

Try something like this:

Dim array1 As Variant
array1 = Array("... cats ...", " ...Dogs ...", " ...Cats and dogs ...", "another text")

Dim array2 As Variant
array2 = Array()
Dim t
For Each t In array1
    If InStr(1, t, "cats", vbTextCompare) > 0 Or InStr(1, t, "dogs", vbTextCompare) > 0 Then
        ReDim Preserve array2(UBound(array2) + 1)
        array2(UBound(array2)) = t
    End If
Next

It is also possible to exclude words like "ducats" or "hotdogs".

Answer 2

Hi
I am Sagar Singh
I am a Student in Information & Communication Technology System Maintenance Course Study in ITI( Industrial Training Institute) Course duration 2 years and I am admission at 2020 and my course complete in 2022

Not a Laptop & Computer

I am study's in mobile phone