Filter based on multiple criteria does not work (VBA)

Question

I would like to search and filter a column in my data, based on inclusion of either one of three criterias stored in another spreadsheet. For example, if the criterias are "Albert", "Bethany" and "Christine" I would like it to filter the specific column for everything that includes the three names given that all three criterias are filled in.

With two of the criterias below my array code successfully works, however, when including the third criteria (lcriteria) it does not allow me to find a match for any of the three criterias even though the first two was previously found. Anyone that knows what might be the problem or can help me out?

I am getting crazy over here... Thank you!

Here is my code:

If Not IsEmpty(Worksheets("Formstack").Range("M2")) Then
    jCriteria = Worksheets("Formstack").Range("L2")      
    kCriteria = Worksheets("Formstack").Range("M2")
    lCriteria = Worksheets("Formstack").Range("N2")

    critArr = Array("*" & jCriteria & "*", "*" & kCriteria & "*", "*" & lCriteria & "*")

    Worksheets("Data").Range("A1").AutoFilter Field:=1, Criteria1:=critArr, Operator:=xlFilterValues
End If

This code does not compile - you are missing a `THEN` after the `if`. Are you missing something else as well? — Vityata, Jul 24 '17 at 11:07
Sorry it should be included, just deleted my comments for clarification of the code and accidently deleted my then. — R. Aflaki, Jul 24 '17 at 11:11
Take a look here: https://stackoverflow.com/questions/28575754/filter-out-multiple-criteria-using-excel-vba — Vityata, Jul 24 '17 at 11:21
@Vityata - thank you, probably will not be able to get it to work then considering... :( — R. Aflaki, Jul 24 '17 at 12:41

score 0 · Answer 1 · answered Jul 24 '17 at 11:02

0

Since you want to read the value of a cell, i´d use .Value. So in you case:

lCriteria = Worksheets("Formstack").Range("N2").Value

If this won´t solve your problem i´d suggest you to debug your code and look whats in the String at runtime, to check if it is as you assumed.

answered Jul 24 '17 at 11:02

Tim Schmidt

1,297
1
15
30

1

`Value` is a default property of `Range` object. – Egan Wolf Jul 24 '17 at 11:06
Ok wasn´t aware of that, I thought that could make a difference in some cases, still in my opinion you should use the `.value` since its clearer that way.. – Tim Schmidt Jul 24 '17 at 11:07
I tried both, but does not give me any answers unfortunately. Somehow the code works when including two of the criterias in the array, but not three. – R. Aflaki Jul 24 '17 at 11:13
Have you tried to the criterias without reading them, but creating the array directly, so you are absolutley sure the string is correct? – Tim Schmidt Jul 24 '17 at 11:18
Yes! I have tried, still does not work both. Also, when changing the order of the criterias, the first two always successfully filter - inclusion of the third gives no matches... Also I've debugged the code and look whats in the String at runtime. – R. Aflaki Jul 24 '17 at 11:21
Well, thats an interesting problem, but i think i can´t help you here. For me it´s the same, the first two are workin gfine, but as soon im adding the third creteria it wont display a thing. Only if i search for certain values (without the * wildcard) the found one will be displayed. Well.. but I have no idea – Tim Schmidt Jul 24 '17 at 11:39
@FatTony Thank you anyways! – R. Aflaki Jul 24 '17 at 12:39

score 0 · Answer 2 · answered Jul 24 '17 at 13:17

For custom filters, you cannot have more than 2 lines - you can check that by trying to set the filter manually: Only 2 fields in the dialog.

There is a way to have more than two filter values, but it works different. It's called Advanced filter. There is an explanation on Superuser. You have to fill the criteria range either manually (referencing your cells from worksheet Formstack) or with VBA code.

Filter based on multiple criteria does not work (VBA)

2 Answers2