Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Applying 2 filters via a Macro
Hello experts,
Please consider the following siutation: In column A I have contract numbers which can be one of 4 different numbers. In column C I have dates, formated as mmm-yy. I have a Macro that filters column C for a certain period - this works great. What I then what it do is filter OUT one contract number, so that only data is shown for 3 out of the 4 contract numbers for the period filtered. This works as long as there is row data for each contract number during those periods. If there isn't a row with a certain contract number during the dates applied by the first filter, the macro falls over as it then can't apply the second filter to data that doesnt exist. Hopefully the code below will help explain this. Range("C1").Select Selection.AutoFilter ActiveWindow.LargeScroll ToRight:=-2 ActiveSheet.Range("$A$1:$Z$53").AutoFilter Field:=3, Operator:= _ xlFilterValues, Criteria2:=Array(1, "2/1/2012", 1, "3/1/2012", 1, "4/1/2012") This bit works great. Then: Range("A1").Select Selection.AutoFilter ActiveWindow.LargeScroll ToRight:=-2 ActiveSheet.Range("$A$1:$Z$53").AutoFilter Field:=1, Criteria1:=Array( _ "3228260", "3228276", "3554229"), Operator:=xlFilterValues This works providing there is 1 or more rows with each of those 3 contract numbers in it. If, say, 3228260 is not present between 1st Feb and 1st April, then "3228260" is not an available option on the filter, therefore the macro can't 'select' it, therefore the macro falls over and throws an error. Does that make sense? Does anyone know any way around this? Many thanks for any help you can give. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Applying 2 filters via a Macro
Hi Pete,
Am Tue, 15 May 2012 16:15:53 +0000 schrieb pete212: I have a Macro that filters column C for a certain period - this works great. What I then what it do is filter OUT one contract number, so that only data is shown for 3 out of the 4 contract numbers for the period filtered. This works as long as there is row data for each contract number during those periods. If there isn't a row with a certain contract number during the dates applied by the first filter, the macro falls over as it then can't apply the second filter to data that doesnt exist. Hopefully the code below will help explain this. Try: With ActiveSheet.Range("A1") .AutoFilter Field:=1, Criteria1:=Array("3228260", "3228276", "3554229") .AutoFilter Field:=3, Criteria2:= _ Array(1, "2/1/2011", 1, "3/1/2011", 1, "4/1/2011") End With Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Applying 2 filters via a Macro
Hi Pete,
the copy of the code is not correct and the year is 2011 instead of 2012: With ActiveSheet.Range("A1") .AutoFilter Field:=1, Criteria1:=Array("3228260", "3228276", "3554229"), _ Operator:=xlFilterValues .AutoFilter Field:=3, Operator:=xlFilterValues, Criteria2:= _ Array(1, "2/1/2012", 1, "3/1/2012", 1, "4/1/2012") End With Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#4
|
|||
|
|||
Quote:
Thanks very much! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Applying macro to mulitple worksheets? | Excel Worksheet Functions | |||
Applying a macro to all worksheets | Excel Discussion (Misc queries) | |||
Applying Macro to only certain sheets | Excel Programming | |||
Applying two or more filters through VB | Excel Programming | |||
Applying Macro to all workbooks... | Excel Programming |