Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If/Elseif AutoFilter Macro
SETUP. I have a column with many different job names repeated in it and
AutoFilter has been applied to it. I also have setup a drop down list that puts a selected job name into helper cell Q4. In the drop down list I also have the word 'All'. MACRO. I need a macro that does not trigger the filter if cell Q4 equals 'All', but filters the indicated job name if not equal to 'All'. Below is what I have so far. I can't figure out the code that should go after the first 'Then'. Also not sure if rest of macro is correct. BIG PICTURE. Ultimately, I want to use this code sequence three times to filter three different columns with one macro. Each will have it's own drop down list and helper cell with 'All' or names in them to direct the filter. Your help would be greatly appreciated. Sub FilterData_JobName() Application.ScreenUpdating = False 'Run (Select All) Filter before running specific filter ActiveSheet.Range("$B$8:$T$453").AutoFilter Field:=4 'Filter Col-E [Field:=4] (Job Name) Ref. cell Q4 Sheets("Time Sheet").Select With Sheets("Time Sheet") If Sheets("Time Sheet").Range("Q4").Value = "All" Then '--???? need code that ends this part of macro here ????-- ElseIf Sheets("Time Sheet").Range("Q4").Value < "All" Then ActiveSheet.Range("$B$8:$T$453").AutoFilter Field:=4, Criteria1:=ActiveSheet.Range("Q4").Value, _ Operator:=xlOr, VisibleDropDown:=True End With Application.ScreenUpdating = False End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
If/Elseif AutoFilter Macro
If you want it to do nothing when the helper cell = "All" then you do not need the first part of the If ... Then statement. I would set the helper range to an Object Variable for ease of use: Set sRng = Sheets("Time Sheet").Range("Q4") Then just use one If ... Then statement to test the value If sRng.Value < All Then 'Autofilter code here End If If you are using the same helper range for all three columns then you could put the If...Then statement inside a For...Next loop like: For i = 4 To 6 'assumes the 3 col are E, F and G If sRng.Value < All Then 'Autofilter code here End If ActiveSheet.Range("$B$8:$T$453").AutoFilter Field:=i, Criteria1:=ActiveSheet.Range("Q4").Value, _ Operator:=xlOr, VisibleDropDown:=True Next This is all untested and may require some editing. "kooldaman" wrote in message ... SETUP. I have a column with many different job names repeated in it and AutoFilter has been applied to it. I also have setup a drop down list that puts a selected job name into helper cell Q4. In the drop down list I also have the word 'All'. MACRO. I need a macro that does not trigger the filter if cell Q4 equals 'All', but filters the indicated job name if not equal to 'All'. Below is what I have so far. I can't figure out the code that should go after the first 'Then'. Also not sure if rest of macro is correct. BIG PICTURE. Ultimately, I want to use this code sequence three times to filter three different columns with one macro. Each will have it's own drop down list and helper cell with 'All' or names in them to direct the filter. Your help would be greatly appreciated. Sub FilterData_JobName() Application.ScreenUpdating = False 'Run (Select All) Filter before running specific filter ActiveSheet.Range("$B$8:$T$453").AutoFilter Field:=4 'Filter Col-E [Field:=4] (Job Name) Ref. cell Q4 Sheets("Time Sheet").Select With Sheets("Time Sheet") If Sheets("Time Sheet").Range("Q4").Value = "All" Then '--???? need code that ends this part of macro here ????-- ElseIf Sheets("Time Sheet").Range("Q4").Value < "All" Then ActiveSheet.Range("$B$8:$T$453").AutoFilter Field:=4, Criteria1:=ActiveSheet.Range("Q4").Value, _ Operator:=xlOr, VisibleDropDown:=True End With Application.ScreenUpdating = False End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
If/Elseif AutoFilter Macro
Maybe...
Option Explicit Sub FilterData_JobName() Dim wks As Worksheet Dim myStr As String Set wks = ActiveSheet 'or be specific 'Set wks = Worksheets("SomeSheetNameHere") Application.ScreenUpdating = False With wks 'Remove any filter in field 4 .Range("B8:T453").AutoFilter field:=4 'Filter Col-E [Field:=4] (Job Name) Ref. cell Q4 myStr = Worksheets("Time Sheet").Range("Q4").Value If LCase(myStr) = LCase("all") Then 'don't do anything, you already did the the (All) Else .Range("b8:t453").AutoFilter field:=4, Criteria1:=myStr End If End With Application.ScreenUpdating = False End Sub kooldaman wrote: SETUP. I have a column with many different job names repeated in it and AutoFilter has been applied to it. I also have setup a drop down list that puts a selected job name into helper cell Q4. In the drop down list I also have the word 'All'. MACRO. I need a macro that does not trigger the filter if cell Q4 equals 'All', but filters the indicated job name if not equal to 'All'. Below is what I have so far. I can't figure out the code that should go after the first 'Then'. Also not sure if rest of macro is correct. BIG PICTURE. Ultimately, I want to use this code sequence three times to filter three different columns with one macro. Each will have it's own drop down list and helper cell with 'All' or names in them to direct the filter. Your help would be greatly appreciated. Sub FilterData_JobName() Application.ScreenUpdating = False 'Run (Select All) Filter before running specific filter ActiveSheet.Range("$B$8:$T$453").AutoFilter Field:=4 'Filter Col-E [Field:=4] (Job Name) Ref. cell Q4 Sheets("Time Sheet").Select With Sheets("Time Sheet") If Sheets("Time Sheet").Range("Q4").Value = "All" Then '--???? need code that ends this part of macro here ????-- ElseIf Sheets("Time Sheet").Range("Q4").Value < "All" Then ActiveSheet.Range("$B$8:$T$453").AutoFilter Field:=4, Criteria1:=ActiveSheet.Range("Q4").Value, _ Operator:=xlOr, VisibleDropDown:=True End With Application.ScreenUpdating = False End Sub -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
If/Elseif AutoFilter Macro
ps. If you decide that you want to show all the data, you don't have to go
through each column. You can use: with wks 'show all the data If .FilterMode Then .ShowAllData End If end with (After all that other stuff where wks is assigned.) kooldaman wrote: SETUP. I have a column with many different job names repeated in it and AutoFilter has been applied to it. I also have setup a drop down list that puts a selected job name into helper cell Q4. In the drop down list I also have the word 'All'. MACRO. I need a macro that does not trigger the filter if cell Q4 equals 'All', but filters the indicated job name if not equal to 'All'. Below is what I have so far. I can't figure out the code that should go after the first 'Then'. Also not sure if rest of macro is correct. BIG PICTURE. Ultimately, I want to use this code sequence three times to filter three different columns with one macro. Each will have it's own drop down list and helper cell with 'All' or names in them to direct the filter. Your help would be greatly appreciated. Sub FilterData_JobName() Application.ScreenUpdating = False 'Run (Select All) Filter before running specific filter ActiveSheet.Range("$B$8:$T$453").AutoFilter Field:=4 'Filter Col-E [Field:=4] (Job Name) Ref. cell Q4 Sheets("Time Sheet").Select With Sheets("Time Sheet") If Sheets("Time Sheet").Range("Q4").Value = "All" Then '--???? need code that ends this part of macro here ????-- ElseIf Sheets("Time Sheet").Range("Q4").Value < "All" Then ActiveSheet.Range("$B$8:$T$453").AutoFilter Field:=4, Criteria1:=ActiveSheet.Range("Q4").Value, _ Operator:=xlOr, VisibleDropDown:=True End With Application.ScreenUpdating = False End Sub -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
If/Elseif AutoFilter Macro
Dear JLGWhiz,
Thank you so much. Your code worked perfectly. So simple! The only change I had to make was to put quotation marks around the word "ALL". I can now filter three columns with any combination of three different criteria. For anyone who might be interested, I've included my entire macro below. Note: The reason for the double AutoFilter runs at each column is that there is a subtotal calculation that occurs after the first run. The second run picks up the result of the calculation. Sub FilterData_MultipleColumns() Application.ScreenUpdating = False 'Unfilter applicable Fields before filtering new criteria ActiveSheet.Range("$B$8:$T$453").AutoFilter Field:=3 ActiveSheet.Range("$B$8:$T$453").AutoFilter Field:=4 ActiveSheet.Range("$B$8:$T$453").AutoFilter Field:=14 ActiveSheet.Range("$B$8:$T$453").AutoFilter Field:=15 '1. Filter Col-D [Field:=3] (PERSON) Ref. cell P4 Set sRng = Sheets("Time Sheet").Range("P4") If sRng.Value < "ALL" Then ActiveSheet.Range("$B$8:$T$453").AutoFilter Field:=3, Criteria1:= _ "=INCLUDED", Operator:=xlOr, Criteria2:=ActiveSheet.Range("P4").Value ActiveSheet.Range("$B$8:$T$453").AutoFilter Field:=3, Criteria1:= _ "=INCLUDED", Operator:=xlOr, Criteria2:=ActiveSheet.Range("P4").Value End If '2. Filter Col-E [Field:=4] (JOB NAME) Ref. cell Q4 Set sRng = Sheets("Time Sheet").Range("Q4") If sRng.Value < "ALL" Then ActiveSheet.Range("$B$8:$T$453").AutoFilter Field:=4, Criteria1:= _ "=INCLUDED", Operator:=xlOr, Criteria2:=ActiveSheet.Range("Q4").Value ActiveSheet.Range("$B$8:$T$453").AutoFilter Field:=4, Criteria1:= _ "=INCLUDED", Operator:=xlOr, Criteria2:=ActiveSheet.Range("Q4").Value End If '3. Filter Col-O [Field:=14] (PAY PERIOD) Ref. cell O5 Set sRng = Sheets("Time Sheet").Range("O5") If sRng.Value < "ALL" Then ActiveSheet.Range("$B$8:$T$453").AutoFilter Field:=14, Criteria1:= _ "=INCLUDED", Operator:=xlOr, Criteria2:=ActiveSheet.Range("O5").Value ActiveSheet.Range("$B$8:$T$453").AutoFilter Field:=14, Criteria1:= _ "=INCLUDED", Operator:=xlOr, Criteria2:=ActiveSheet.Range("O5").Value End If Application.ScreenUpdating = True End Sub |