| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!!! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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!!! . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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!! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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!!! . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using VLookup when text isn't an exact match | Excel Worksheet Functions | |||
| Routine to find exact Row matches in Col1 Col2 Col3 but exact offsetting numbers in Col4 | Excel Discussion (Misc queries) | |||
| How can I exact no. from text string? E.g ACBE123445te | Excel Worksheet Functions | |||
| TEST FOR EXACT TEXT | Excel Worksheet Functions | |||
| Vlookup where text not exact | Excel Discussion (Misc queries) | |||
Linear Mode
