Setting a date filter as a variable in VBA

[Davek81]

Hi All, I have some code that filters a range of data based on a unique number and the last 7 days. What I would like to do is be able to change the 7 days and put it as a variable so that it can be changed to whatever the user specifys. I am not sure on the syntax but basically i want the code to say filter based on todays date minus whatever is in cell "x"

This is my code, i have highlighted the required variable

.AutoFilterMode = False
.UsedRange.AutoFilter
.UsedRange.AutoFilter field:=1, Criteria1:="D5060"
.UsedRange.AutoFilter field:=2, Criteria1:="<=" & CLng(Date), Operator:=xlAnd, _
Criteria2:=">=" & CLng(Date - 7)

Thanks in advance

 

[dmt32]

Hi,
try changing the line of your code to this:

CLng(Date - CInt(Range("X1").Value))

If the worksheet with variable value is not active when code run then you will need to qualify the range.
Adjust range as required.

Dave