Ernest_PLM
New Member
- Joined
- Feb 13, 2013
- Messages
- 2
Hi all,
I've set up an macro to perform an Autofilter and copy the results to a new WorkSheet. HOWEVER, what i need help with is any code that would allow me identify any "," (comma) within a text string of a single cell to equal an "OR" so that it filters by multiple criteria.
For example
If my Data Source has:
ColumnA
Red
Blue
Green
Purple
Pink
AND
my Criteria cell is:
Red, Green, Pink
How do i get VBA to recognise that i want to return these 3 values from the data set?
I know that it would be easier to separate these 3 values into 3 different cells or to hard code it into the macro but it's not an option in my case and really need to get the code to recognise "," = OR.
Here is my VBA currently:
Sub a()
Sheets("Criteria").Activate
Sheets("Criteria").Range("H1:O1").Select
Selection.Copy
Sheets("Output").Select
Range("A1").PasteSpecial
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("D:D").EntireColumn.AutoFit
Columns("E:E").EntireColumn.AutoFit
Columns("F:F").EntireColumn.AutoFit
Columns("G:G").EntireColumn.AutoFit
Columns("H:H").EntireColumn.AutoFit
Range("C4").Select
Sheets("Source").Select
Application.CutCopyMode = False
Sheet1.Range("A3").Select
Sheets("Output").Select
Sheets("Source").Range("A1:H100000").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets("Criteria").Range("H1:O2"), CopyToRange:=Sheets("Output").Range("A1:H1"), Unique:=False
End Sub
I've set up an macro to perform an Autofilter and copy the results to a new WorkSheet. HOWEVER, what i need help with is any code that would allow me identify any "," (comma) within a text string of a single cell to equal an "OR" so that it filters by multiple criteria.
For example
If my Data Source has:
ColumnA
Red
Blue
Green
Purple
Pink
AND
my Criteria cell is:
Red, Green, Pink
How do i get VBA to recognise that i want to return these 3 values from the data set?
I know that it would be easier to separate these 3 values into 3 different cells or to hard code it into the macro but it's not an option in my case and really need to get the code to recognise "," = OR.
Here is my VBA currently:
Sub a()
Sheets("Criteria").Activate
Sheets("Criteria").Range("H1:O1").Select
Selection.Copy
Sheets("Output").Select
Range("A1").PasteSpecial
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("D:D").EntireColumn.AutoFit
Columns("E:E").EntireColumn.AutoFit
Columns("F:F").EntireColumn.AutoFit
Columns("G:G").EntireColumn.AutoFit
Columns("H:H").EntireColumn.AutoFit
Range("C4").Select
Sheets("Source").Select
Application.CutCopyMode = False
Sheet1.Range("A3").Select
Sheets("Output").Select
Sheets("Source").Range("A1:H100000").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets("Criteria").Range("H1:O2"), CopyToRange:=Sheets("Output").Range("A1:H1"), Unique:=False
End Sub
Last edited: