CODEWITHSUNDEEP
excelvbafilterrangecriteria
Kat
Kat

Reputation: 23

Less than and Greater than Criteria

Hope all is well.

Having some trouble writing code for an excel file. Push button macro control will be added to sheet to filter on how long an item has been open on list. Data is column E has this information (a calculation that equals the number of weeks an item has been open). In cell E4, I added a dropdown select of the options which are: < 1 week, >1 week but less than 2 weeks, > 2 weeks, > 3 weeks, > 1 month. The user selects the required option, and then push the control button which should filter the range based on the value of the number in E AND if the entry in column H = "open". My code is not working. Below is what I have so far:

Sub Button2_Click()
    ActiveSheet.Unprotect Password:="1234"
    Sheets("CMLog").AutoFilterMode = False
    
Dim List1 = Range ("E4")

With Worksheets("CMLog").Range("A9:AC5000)
If List1 = "< = 1 week" Then
.AutoFilter Field:=8, Criteria1:="Open"
.AutoFilter Field:=5, Criteria1:="<=1"
End If
If List1 ="> 1 week but <=2" Then
.AutoFilter Field:=8, Criteria1:="Open"
.AutoFilter Field:=5, Criteria1:=">1" And "<=2"
End If

End With
ActiveSheet.Protect Password:="1234"

End Sub

The code continues on with >3 weeks, and >1 month criteria options but I didn't want to retype it all here. When I try to run it, I first got a Run Time Error 9 and "With Worksheets("CMLog").Range("A9:AC5000)" was highlighted. I am not getting that error any longer but nothing is happening. I never worked with criteria ranges before so I am not sure if that is where the issue is. Any suggestions? Note: the excel had the filter options on. Pressing the control button turned those off but nothing else happened.

Kat

Upvotes: 1

Views: 568

Answers (1)

Toddleson
Toddleson

Reputation: 4457

This line: .AutoFilter Field:=5, Criteria1:=">1" And "<=2"

needs to make use of the Operator argument of Autofilter. You are trying to use And to join two criteria together, but you cannot do that within the Criteria1 argument. You must make use of Operator:=xlAnd and put the two criteria into Criteria1 and Criteria2.

.AutoFilter Field:=5, Criteria1:=">1", Criteria2:="<=2", Operator:=xlAnd

Upvotes: 1

Related Questions