CODEWITHSUNDEEP
excelvba
NeilD137
NeilD137

Reputation: 37

Compile Error on Autofilter by Dynamic Date

I am getting an error when trying to run a macro which should filter my data based on the date in Column AN. I want this to be date dependent, so it should filter everything from 2 months prior to today's date in time.

Sub Move2017()
Macro 2017 Clear
Dim DQ As Date
DQ = dateserial(Year(Now), month(Now) - 2, 1)
Range("A1:AZ100000").AutoFilter field:=40, Operator:= _
    xlFilterValues, Criteria2:=Array(1, DQ)
End Sub

I keep getting the following error: "Compile Error: Wrong number of arguments or invalid property assignment"

Any thoughts on how to solve this?

Upvotes: 0

Views: 90

Answers (2)

Shai Rado
Shai Rado

Reputation: 33672

Just in case @Scott Holtzman solution doesn't work, sometimes the AutoFilter with Dates can be tricky, depends exactly how you formatted the Cells. In some scenarios it works better with Double.

Also, you can use DateAdd function much easier to get a date 2 month prior to Today's date.

Option Explicit

Sub Move2017()

Dim DQ As Double

DQ = DateAdd("m", -2, Date) '<-- get the date 2 month prior to today's date
Range("A1:AZ100000").AutoFilter field:=40, Criteria1:=">" & DQ, Operator:=xlAnd

End Sub

Upvotes: 3

Scott Holtzman
Scott Holtzman

Reputation: 27239

Try this:

Range("A1:AZ100000").AutoFilter field:=40, Criteria1:=">="&DQ, Operator:=xlAnd

Tested and works for me.

Upvotes: 2

Related Questions