CODEWITHSUNDEEP
arraysvbaexcelautofilter
Rdster
Rdster

Reputation: 1872

Excel Autofilter Criteria array loop

I need to filter a column by dates. I determine the number of dates needed, and then apply the filter. Originally I figured there were only 2 cases, Tuesday thru Friday where you use the previous date. And Monday where Friday, Saturday and Sunday are needed.

Then I realized that there might be a holiday on Friday or Monday, and it would require more dates...and there might be a midweek holiday, like Thanksgiving. I then came up with this to cover the 4 most common possibilities:

  dPosting = dLast + 1 'dLast is the last Date used in the filter
  Select Case Date - dLast
    Case 2: 'Regular T - F (1 day)
      shFF.Range("A1", shFF.Cells(lMax, lCol)).AutoFilter Field:=cFind.Column, Operator:=xlFilterValues, Criteria1:=Array(2, dPosting)
    Case 3: 'Midweek Holiday (2 days)
      shFF.Range("A1", shFF.Cells(lMax, lCol)).AutoFilter Field:=cFind.Column, Operator:=xlFilterValues, Criteria1:=Array(2, dPosting, 2, dPosting + 1)
    Case 4: 'Regular Monday (3 days)
      shFF.Range("A1", shFF.Cells(lMax, lCol)).AutoFilter Field:=cFind.Column, Operator:=xlFilterValues, Criteria1:=Array(2, dPosting, 2, dPosting + 1, 2, dPosting + 2)
    Case 5: 'After F or M Holiday (4 days)
      shFF.Range("A1", shFF.Cells(lMax, lCol)).AutoFilter Field:=cFind.Column, Operator:=xlFilterValues, Criteria1:=Array(2, dPosting, 2, dPosting + 1, 2, dPosting + 2, 2, dPosting + 3)
    Case Else: 'Other
      MsgBox "Too many days since last run.  Please contact me", vbCritical, "Out of Scope"
  End Select

Then I figured, what if the person that runs this is out for a week for vacation...and threw in the Case Else.

Now I think that the easiest and cleanest way will be to simply determine the number of dates needed from the last date that was run dLast and Now. Put that into an array and use that as the Criteria...but I can't figure out the syntax.

Checked these, but still but can't make it work:

This is my test code to play with the syntax and data sheet:

Sub Test()
Dim i As Integer
Dim d As Date
Dim sArr(0 To 2) As String

  d = #4/25/2016#
  For i = 0 To 2
    sArr(i) = "2, """ & d + i & """"
  Next i
  ActiveSheet.Range("$A$1:$R$16643").AutoFilter Field:=17, Operator:=xlFilterValues, Criteria1:=sArr

End Sub

The macro recorder spits this out for using 3 dates:

    ActiveSheet.Range("$A$1:$R$16643").AutoFilter Field:=17, Operator:=xlFilterValues, _
Criteria2:=Array(2, "4/25/2016", 2, "4/26/2016", 2, "4/27/2016")

What am I missing??

Upvotes: 2

Views: 1405

Answers (1)

Nathan_Sav
Nathan_Sav

Reputation: 8531

Could you use something like this, and calculate the dates, dt1 and dt2, using Date - dLast, instead of the selects, and try

.AutoFilter Field:=2, Criteria1:= ">=" & dt1, Operator:=xlAnd, Criteria2:="<=" & dt2

Upvotes: 1

Related Questions