Filtering the next 2 weeks lookahead after another date-CodePudding

The code below is showing a weird error, shown in the screenshot

I need to click Ok for the filter to work

Sub LA2w()
Dim range_to_filter As Range
Set range_to_filter = Range("K6:S999")
Dim DD As Range
Set DD = Cells(1, 11)


range_to_filter.AutoFilter Field:=19, Criteria1:=Array("In Progress", "Not Started", "="), Operator:=xlFilterValues, Operator:=xlAnd
range_to_filter.AutoFilter Field:=11, Criteria1:=">=" & DD.Value, Operator:=xlAnd, Criteria2:="<=" & DD.Value   15


End Sub

The revised one that @BigBen suggested is

Sub LA4W()
Dim range_to_filter As Range
Set range_to_filter = Range("K6:S999")
Dim DD As Range
Set DD = Cells(1, 11)


range_to_filter.AutoFilter Field:=19, Criteria1:=Array("In Progress", "Not Started", "="), Operator:=xlFilterValues, Operator:=xlAnd
range_to_filter.AutoFilter Field:=11, Criteria1:=">=" & Format$(DD.Value, "mm/dd/yyyy"), Operator:=xlAnd, Criteria2:="<=" & Format(DD.Value   30, "mm/dd/yyyy")


End Sub

I attached the sheet in here in case you want to see it.

CodePudding user response：

Set is for object variables. So Set DD to the range, then use its value in the autofilter criteria.

Dim DD As Range
Set DD = Cells(11, 1)

range_to_filter.AutoFilter _
   Field:=11, _
   Criteria1:=">=" & Format$(DD.Value, "mm/dd/yyyy"), _
   Operator:=xlAnd, _
   Criteria2:="<=" & Format$(DD.Value   15, "mm/dd/yyyy")

Another (potentially clearer) option: a Date variable:

Dim DD As Date
DD = Cells(11, 1).Value

range_to_filter.AutoFilter Field:=11, Criteria1:=">=" & DD, _
                        Operator:=xlAnd, _
                        Criteria2:="<=" & DD   15