question

LaiKanLeon-4557 avatar image
0 Votes"
LaiKanLeon-4557 asked LaiKanLeon-4557 commented

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

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






office-vba-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered LaiKanLeon-4557 commented

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".

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello, Sagar Singh

Thanks for your reply.

Your code seems to make sense.

However, when I incorporate it in my program, and run it, there is an error here:

array2(UBound(array2)) = t

The tooltip reads "UBound(array2)) = -1

So, it seems that array2 is not being populated by the filtered data.

Maybe one or two lines of code are missing. What?

Thanks
Leon




0 Votes 0 ·

Maybe you did not incorporate it correctly. Initially UBound(array2) is -1. After the shown ReDim it becomes 0, 1, 2, etc. Show some details.


0 Votes 0 ·

Hi Sagar

I spent a lot of time trying to figure out what went wrong.

In fact your code is perfect. Array2 is correctly populated.

But I met a curious problem.

See the attached word file which explains everything.
121927-capture.png
Hope you can help.

Thanks
Leon




Added:
I just moved my last line of code inside the IF block, and it works correctly.
But it still puzzles me why array2 goes out of scope

0 Votes 0 ·
capture.png (43.6 KiB)

Hi Sagar

Thanks a lot for all your help.

I feared that so many loopings would slow my program.
Buy looping through half a million rows took less than 2 seconds!
And it's very unlikely that I will ever use more than 20,000 rows.

You have been able to filter by more than one value.
The VBA FILTER function cannot do this!

Best Regards
Leon

0 Votes 0 ·
60571197 avatar image
0 Votes"
60571197 answered 60571197 rolled back

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.