VBA - Filter by Input Variable - not working

[dragon-goddess1990]

Hi

I have got some code that filters several columns. But I have got one which filters by user's input called Date1. (they input via input box)

Sub OneMonthBeforeCCDateBACKLOGFilter()
Dim Date1 As Date
Date1 = InputBox(Prompt:="What is todays date?", _
Title:="Enter Todays Date", Default:="")
Selection.AutoFilter Field:=15, Criteria1:="CONTRACTOR", Operator:=xlAnd
Selection.AutoFilter Field:=14, Criteria1:="<>CEASED TRADING", Operator:=xlAnd
Selection.AutoFilter Field:=21, Criteria1:="", Operator:=xlAnd
Selection.AutoFilter Field:=18, Criteria1:="<=" & CStr(Date1), Operator:=xlAnd
Selection.AutoFilter Field:=18, Criteria1:="<=" & CStr(Date1), Operator:=xlAnd

It all goes fine as it puts the input into the custom criteria but its as if it doesnt run it.

When I manually went into the custom criteria after running the macro it shows the criteria but I have to click OK and then it displays the data. (when this criteria isnt active no data shows)

Hope you can help.

Amy

 

[Trevor G]

As you are looking to convert the date to string you might need to format it first so something like this

format (cstr(date1),"DDMMYY")

 

[dragon-goddess1990]

Do I need to declare the variable as string:

Dim Date1 As String

Instead of:

Dim Date1 As Date

Amy

 

[dragon-goddess1990]

Its really strange as it adds the criteria to the custom filter but its like it doesnt bother running it.

Amy