+ Reply to Thread
Results 1 to 9 of 9

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

  1. #1
    Registered User
    Join Date
    03-11-2021
    Location
    Hyderabad
    MS-Off Ver
    2019
    Posts
    12

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

    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

  2. #2
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,217

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

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

    *BF*Delete*
    *Ship*Easy*
    *English*French*
    *UK*IN*
    If I was able to help, you can thank me by clicking the * Add Reputation under my user name

  3. #3
    Registered User
    Join Date
    03-11-2021
    Location
    Hyderabad
    MS-Off Ver
    2019
    Posts
    12

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

    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?

  4. #4
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,217

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

    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

  5. #5
    Registered User
    Join Date
    03-11-2021
    Location
    Hyderabad
    MS-Off Ver
    2019
    Posts
    12

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

    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.
    Attached Files Attached Files

  6. #6
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,217

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

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

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by nankw83; 03-17-2021 at 02:45 PM.

  7. #7
    Registered User
    Join Date
    03-11-2021
    Location
    Hyderabad
    MS-Off Ver
    2019
    Posts
    12

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

    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

  8. #8
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,217

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

    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

  9. #9
    Registered User
    Join Date
    03-11-2021
    Location
    Hyderabad
    MS-Off Ver
    2019
    Posts
    12

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

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Facing problem in creating 2 pivot table in macros!
    By 063Azi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-21-2020, 12:04 PM
  2. Macros and advanced filter
    By brian807 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-03-2016, 02:17 PM
  3. [SOLVED] Using Advanced Filter: problem with using wildcards
    By robkrouse in forum Excel - New Users/Basics
    Replies: 9
    Last Post: 01-24-2016, 04:18 AM
  4. Delete Rows hidden by either Auto Filter and Advanced Filter
    By lesoies in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-05-2013, 10:29 AM
  5. [SOLVED] Advanced Filter results don't match auto filter
    By WaterWings in forum Excel General
    Replies: 1
    Last Post: 09-13-2012, 05:37 PM
  6. Advanced Filter + Several Macros
    By excel_owns_me in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-12-2007, 02:52 AM
  7. [SOLVED] Wildcards in Advanced filter criteria don't work with Excel 2003
    By Inconceivable in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-14-2005, 10:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1