Facing problem in finding Syntax for Advanced auto filter with wildcards using Macros

[girishankar09]

0


I have created an event like when something changes in a cell, then an advanced filter should happen.

The values to be filtered will be in C2:E3 with C2,D2,E2 as headers. the values will be filtered in B6:D67 with B6,C6,D6 as headers.

Now the issue is:

the values which I filter with CriteriaRange are dynamic with values like "BF Delete", "Ship Easy" "English/French", UK,IN which might contain spaces, / etc

The values in the ranger where I apply filter are even more complex and the example are like "BFA BF Delete", DIIP Ship Easy (US & IN).

I code which I wrote are taking exact match and does not work when they have space, commas, forward slash or brackets.

I want it to work it this way and I am unable to find the correct syntax or format I can write the macros for my use case for it to work even when partial words match. My code is mentioned below.


Sub FilterData()
Range("B6:D67").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("C2:E3"), Unique:=False

End Sub

[nankw83]

In your CriteriaRange, try putting * to use wildcards like below

*BF*Delete*
*Ship*Easy*
*English*French*
*UK*IN*

[girishankar09]

Thanks for the response. But in my use case I will not be able to use values like *BF*Delete* since the values are dynamic and thats the reason I used cell reference which will have the value CriteriaRange:= _
Range("C2:E3"), Unique:=False

Is there a way that I add wildcard like "*" to the above cell range?

[nankw83]

If you have formulas in your CriteriaRange, you can save the formula in a variable, change the text in your CriteriaRange then put back your formulas after the advanced filter macro ... If you post a sample file, we can provide more assistance

[girishankar09]

Thanks for looking into it.

I have attached a sample file which contains values and macros.

File description.

from row 73 to 82 in "Allocation tool" sheet will become drop downs in column B3
and when these drop downs change, C3 to F3 will change automatically

When they change, based on the values in C3, D3 and E3, filtration should happen under the values in B6,C6 and D6
(there is already an event written or this and once the drop down is changes you will need to click on some of the other cell for filters to apply
"not sure why tho but it work

The problem here is the mixed combination of Skill Marketplace Language which can be fixed only by using wildcards and I am not sure of syntax
of using "*" at front and back and also if needed other wild cards can be added since these are complex combinations

could you please help me with the right code for this problem.

[nankw83]

I have added the below code to your file as attached, see if this does help ...

Please Login or Register  to view this content.

[girishankar09]

You were just Amazing, it really worked. I have added reputation to the above post

I have one last questions

There are times where the the input skills are multiple, example within C3:E3 range, there might be values like example "skills": BF Remove/KYC (UK) or something like Ship Easy, PUC Exemption where project requires multiple skills.

Is there a way to alter the same code provided by you above (leaving the rest as it is since it helped with my earlier use case) to add lines where when there are "/" or "," it should first search for ones before the "/" or "," and then add to filter the search results which are after the "/" or ","

Example: BF Remove/KYC (UK)
First it searches with skill "BF Remove" and then, search for "KYC (UK)" and add it to the previous search so that I get search results of both the skills

Greatly appreciate your time

[nankw83]

With xlFilterInPlace I don't think this is possible because you'll search for "BF Remove" & show the data for that then run another search for "KYC (UK)" which will clear previous search & apply the new one

[girishankar09]

Hmm, Thanks for the info. If there any way or code which can help in achieving both the earlier solution and the latest question if not possible using xlFilterInPlace? If not I would consider it not possible to Achieve using Macros. Sorry I am not that good at VBA macros so trying to get as much help as possible.