Filter Macro where cell contains

Domroy

Board Regular
Joined
Mar 8, 2018
Messages
88
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
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,558
Office Version
365
Platform
Windows
Try putting the keyword between 2 asterisk, like this:
*National*
 

Domroy

Board Regular
Joined
Mar 8, 2018
Messages
88
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...
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,558
Office Version
365
Platform
Windows
Ok, glad it works.:)
 

Forum statistics

Threads
1,089,166
Messages
5,406,544
Members
403,096
Latest member
cyclingdad

This Week's Hot Topics

Top