Filtering for exact text

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to filter a large report, using two parameters, Planner Code and Status. Everything works fine, except one of the 4 planner codes I am filtering by, TEAMC 1, is bringing back not only that but TEAMC 11, TEAMC 12, etc...

The macro:

Does the advanced filter, by inserting the filter parameters, (Westak, Team C1*, ViaSystems, and TTM
' and only for "Released" parts.
'
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Range("C1").Select
ActiveCell.FormulaR1C1 = "Planner"
Range("C2").Select
ActiveCell.FormulaR1C1 = "Westak"
Range("C3").Select
ActiveCell.FormulaR1C1 = "TEAMC 1"
Range("C4").Select
ActiveCell.FormulaR1C1 = "ViaSystems"
Range("C5").Select
ActiveCell.FormulaR1C1 = "TTM"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Status"
Range("D2").Select
ActiveCell.FormulaR1C1 = "Released"
Range("D3").Select
ActiveCell.FormulaR1C1 = "Released"
Range("D4").Select
ActiveCell.FormulaR1C1 = "Released"
Range("D5").Select
ActiveCell.FormulaR1C1 = "Released"
Range("C8").Select
Range("A7:N25006").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
Range("C1:D5"), CopyToRange:=Range("AA5"), Unique:=False
End Sub


The line that is causing me trouble is:

ActiveCell.FormulaR1C1 = "TEAMC 1"

I have tried insering "=TEAMC 1", as the help screen under "Filter by using advanced criteria" suggested, but this just stalls out the macro. I have also tried leaving a space: "TEAMC 1 ", thinking that would rule out the second number, mush like it does in the Find/Replace function; this returned nothing at all, not even TEAMC 1....

The wildcard characters also are of no help, as they don't cover a case where you want nothing after a certain character.

Help!!!
 
Well, unfortunately I do not have a sample of the data
that you run this macro against to test out my ideas. But
from what you are describing, the problem could be as
simple as making TEAMC 1 into TEAMC1 or TEAMC_1 or
TEAMC.1 - give it a hack.

-IA
-----Original Message-----
I am trying to filter a large report, using two
Click to expand...
parameters, Planner Code and Status. Everything works
fine, except one of the 4 planner codes I am filtering by,
TEAMC 1, is bringing back not only that but TEAMC 11,
TEAMC 12, etc...
The macro:

Does the advanced filter, by inserting the filter
Click to expand...
parameters, (Westak, Team C1*, ViaSystems, and TTM
' and only for "Released" parts.
'
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Range("C1").Select
ActiveCell.FormulaR1C1 = "Planner"
Range("C2").Select
ActiveCell.FormulaR1C1 = "Westak"
Range("C3").Select
ActiveCell.FormulaR1C1 = "TEAMC 1"
Range("C4").Select
ActiveCell.FormulaR1C1 = "ViaSystems"
Range("C5").Select
ActiveCell.FormulaR1C1 = "TTM"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Status"
Range("D2").Select
ActiveCell.FormulaR1C1 = "Released"
Range("D3").Select
ActiveCell.FormulaR1C1 = "Released"
Range("D4").Select
ActiveCell.FormulaR1C1 = "Released"
Range("D5").Select
ActiveCell.FormulaR1C1 = "Released"
Range("C8").Select
Range("A7:N25006").AdvancedFilter
Click to expand...
Action:=xlFilterCopy, CriteriaRange:= _
Range("C1:D5"), CopyToRange:=Range("AA5"), Unique:=False
End Sub


The line that is causing me trouble is:

ActiveCell.FormulaR1C1 = "TEAMC 1"

I have tried insering "=TEAMC 1", as the help screen
Click to expand...
under "Filter by using advanced criteria" suggested, but
this just stalls out the macro. I have also tried leaving
a space: "TEAMC 1 ", thinking that would rule out the
second number, mush like it does in the Find/Replace
function; this returned nothing at all, not even TEAMC
1....
The wildcard characters also are of no help, as they
Click to expand...
don't cover a case where you want nothing after a certain
character.
 
Izar

Unfortunately, your solution would require changing the "Planner Code" attribute in Oracle for all parts that are called out under TeamC 1. Debra's solution worked like a charm, but thanks anyway, for giving my problem a try

Jef

----- Izar Arcturus wrote: ----

Well, unfortunately I do not have a sample of the data
that you run this macro against to test out my ideas. But
from what you are describing, the problem could be as
simple as making TEAMC 1 into TEAMC1 or TEAMC_1 or
TEAMC.1 - give it a hack

-I
-----Original Message----
I am trying to filter a large report, using two
Click to expand...
parameters, Planner Code and Status. Everything works
fine, except one of the 4 planner codes I am filtering by,
TEAMC 1, is bringing back not only that but TEAMC 11,
TEAMC 12, etc..parameters, (Westak, Team C1*, ViaSystems, and TT
' and only for "Released" parts

Rows("1:1").Selec
Selection.Insert Shift:=xlDow
Selection.Insert Shift:=xlDow
Selection.Insert Shift:=xlDow
Selection.Insert Shift:=xlDow
Selection.Insert Shift:=xlDow
Selection.Insert Shift:=xlDow
Range("C1").Selec
ActiveCell.FormulaR1C1 = "Planner
Range("C2").Selec
ActiveCell.FormulaR1C1 = "Westak
Range("C3").Selec
ActiveCell.FormulaR1C1 = "TEAMC 1
Range("C4").Selec
ActiveCell.FormulaR1C1 = "ViaSystems
Range("C5").Selec
ActiveCell.FormulaR1C1 = "TTM
Range("D1").Selec
ActiveCell.FormulaR1C1 = "Status
Range("D2").Selec
ActiveCell.FormulaR1C1 = "Released
Range("D3").Selec
ActiveCell.FormulaR1C1 = "Released
Range("D4").Selec
ActiveCell.FormulaR1C1 = "Released
Range("D5").Selec
ActiveCell.FormulaR1C1 = "Released
Range("C8").Selec
Range("A7:N25006").AdvancedFilter
Click to expand...
Action:=xlFilterCopy, CriteriaRange:=
Range("C1:D5"), CopyToRange:=Range("AA5"), Unique:=Fals
End Su
Click to expand...
under "Filter by using advanced criteria" suggested, but
this just stalls out the macro. I have also tried leaving
a space: "TEAMC 1 ", thinking that would rule out the
second number, mush like it does in the Find/Replace
function; this returned nothing at all, not even TEAMC
1...don't cover a case where you want nothing after a certain
character
 
I see why Debra's solution is more attractive. Glad you
got the code you needed.

-IA
-----Original Message-----
Izar,

Unfortunately, your solution would require changing
Click to expand...
the "Planner Code" attribute in Oracle for all parts that
are called out under TeamC 1. Debra's solution worked like
a charm, but thanks anyway, for giving my problem a try!
 
Back
Top