Filtering for exact text
 

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!!!

Filtering for exact text
 

Instead of: ActiveCell.FormulaR1C1 = "=""=TEAMC 1"""


JDaniel wrote:
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!!!


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Filtering for exact text
 

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
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!!!
.

Filtering for exact text
 

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
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 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
Action:=xlFilterCopy, CriteriaRange:=
Range("C1:D5"), CopyToRange:=Range("AA5"),
Unique:=Fals
End Su
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!!

Filtering for exact text
 

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
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!

Jeff

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

-IA

-----Original Message-----
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!!!
.

.