Results 1 to 6 of 6

Thread: Filter Macro where cell contains
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Mar 2018
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Filter Macro where cell contains

    Hi there,

    I have a sheet with 1500 records I'm trying to match to another sheet that has 700 records. The company name is not an exact match, so VLOOKUP is not an option. I built a tool with a macro for an advanced filter, and one to clear the data. The problem is that my filter is only looking up the company name if it STARTS with the text I put in my input cell. I want it to find it if it's anywhere in the column, not just the beginning. For example, if I put "National" in the designated lookup cell, I want it to find anywhere where "National" is in the company name field, not just at the beginning. Here's the code I used:

    Code:
    Sub Filter_Data()
    '
    ' Filter_Data Macro
    '
    
    
    '
        Application.ScreenUpdating = False
        Application.CutCopyMode = False
        Range("List").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range( _
            "Criteria"), Unique:=False
    End Sub
    Sub Reset_Filter()
    '
    ' Reset_Filter Macro
    '
    
    
    '
        Application.ScreenUpdating = False
        Range("A4").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range("A4:XFB4").Select
        Range(Selection, Selection.End(xlToLeft)).Select
        Range("A4:N4").Select
        Selection.ClearContents
        Application.CutCopyMode = False
        Range("List").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range( _
            "Criteria"), Unique:=False
        Range("A4").Select
    End Sub
    Can you help me fix it so it finds the data wherever it's located in the cell?

    Thank you!

    Judi

  2. #2
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,148
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Filter Macro where cell contains

    Try putting the keyword between 2 asterisk, like this:
    *National*

  3. #3
    Board Regular
    Join Date
    Mar 2018
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filter Macro where cell contains

    Quote Originally Posted by Akuini View Post
    Try putting the keyword between 2 asterisk, like this:
    *National*
    OH!!! As in, in my search box? YES! I will try that tomorrow when I get to work and let you know. Thanks so much! I always forget about those...

  4. #4
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,148
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Filter Macro where cell contains

    You're welcome.
    This article might be useful for you:
    https://www.contextures.com/xladvfilter01.html

  5. #5
    Board Regular
    Join Date
    Mar 2018
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filter Macro where cell contains

    It worked!!! Thank you! And I'll check out the article!

  6. #6
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,148
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Filter Macro where cell contains

    Ok, glad it works.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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