Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter criteria in as a variable
I want to set the upper and lower criteria for the filter of column F to variables in H1 and H2 dropdowns.
The message box reads correctly, however, the only value returned to the destination ranges is the F1 value. Hard coding the criteria into the code works okay. Thanks. Howard Sub A_Filter_Copy() Dim CriteriaU As Object Dim CriteriaL As Object Set CriteriaU = ActiveSheet.Cells(1, 8) Set CriteriaL = ActiveSheet.Cells(2, 8) 'MsgBox CriteriaU & " " & CriteriaL Application.ScreenUpdating = False ActiveSheet.Range("F1", Range("F1").End(xlDown)).AutoFilter Field:=1, Criteria1:=CriteriaU, _ Operator:=xlAnd, Criteria2:=CriteriaL ActiveSheet.AutoFilter.Range.Copy Range("A" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues Selection.AutoFilter Application.ScreenUpdating = False Application.CutCopyMode = False End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter criteria in as a variable
Hi Howard,
Am Thu, 4 Jun 2015 04:08:37 -0700 (PDT) schrieb L. Howard: I want to set the upper and lower criteria for the filter of column F to variables in H1 and H2 dropdowns. then you have to code =Criteria1 and <=Criteria2: Sub A_Filter_Copy() Dim CriteriaU As Double Dim CriteriaL As Double CriteriaU = ActiveSheet.Cells(1, 8) CriteriaL = ActiveSheet.Cells(2, 8) 'MsgBox CriteriaU & " " & CriteriaL Application.ScreenUpdating = False With ActiveSheet .Range("F1", .Range("F1").End(xlDown)).AutoFilter Field:=1, _ Criteria1:="=" & CriteriaU, Operator:=xlAnd, Criteria2:="<=" & CriteriaL .AutoFilter.Range.Copy .Range("A" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues .AutoFilterMode = False End With Application.ScreenUpdating = False Application.CutCopyMode = False End Sub Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter criteria in as a variable
I see, gotta have the & < in the fray.
It was in the hard coded version I was using from the recorder, but I failed to catch the need of them in the variable code. Thanks Claus. Howard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
fixed filter criteria made variable | Excel Programming | |||
Building criteria string for Advanced Filter variable not resolvin | Excel Discussion (Misc queries) | |||
Advanced Filter VB Script for Variable Criteria Range | Excel Programming | |||
How do I identify Filter criteria or variable graph title? | Excel Discussion (Misc queries) | |||
"Criteria Range" in the "Data/Filter/Advanced Filter" to select Du | Excel Worksheet Functions |