Filter Conditions?

Posted by RoB on August 16, 2001 3:08 PM

Thanks to Barrie, I have this code which uses autofilter to copy the user specified conditions. I want to know if there is a way for the user to specify multiple conditions when asked. Is there a way for the user to do this? or in the macro?

Here is the applicable code:

'Copies Defined Range and pastes on "Prelim Sort" Sheet
Sheets(CurrentSheetName).Range(RangeSelect).Copy _
Destination:=Sheets("Prelim Sort").Range("A1")

Thanks.

Posted by Robb on August 17, 2001 5:56 AM

Rob

You can set another criteria within the filter you have set, just use Input Box to get another one from the user.

'Get the filter's criteria from the user
FilterCriteria = InputBox("Enter the Condition to Sort out:")
FilterCriteria2 = InputBox("Enter the Second Condition to Sort out:")'Filter the data based on the user's input
Selection.AutoFilter field:=ColumnFilterSelect, Criteria1:=FilterCriteria, Operator:=xlOr, Criteria2:=FilterCriteria2

[The operator could be xlAnd , if that's what you want]

If you want the user to be able to filter first by one field and then by another, just call the filter twice (or whatever):

'Get the filter's criteria from the user
FilterCriteria = InputBox("Enter the Condition to Sort out:")
'Filter the data based on the user's input
Selection.AutoFilter field:=ColumnFilterSelect, Criteria1:=FilterCriteria
'Get the second filter's criteria from the user
FilterCriteria2 = InputBox("Enter the Next Condition to Sort out:")
'Filter the data based on the user's input
Selection.AutoFilter field:=ColumnFilterSelect2, Criteria1:=FilterCriteria2

You then use the rest of your code to copy the result.

Does this help?

Regards

Posted by Rob on August 17, 2001 3:25 PM

ill try making a user defined loop maybe...thanks