Auto filter between a range of dates

[K1600]

I am trying to apply a filter to a data set which is with an excel formatted table (Table8) but the first bit I need to do is only show data where the date in the 'Date' column is less than the date in the 'Last MOT date' column.

I currently have the following coding but I keep getting a run-time 91 error on the first line of the With statement,

Dim RTF As Range
Dim ColDate As Long
Dim ColMOTDate As Long
Dim ColRemoval As Long
Dim ColComplete As Long

Set RTF = Worksheets("Pro").Range("Table8").Range("A1").CurrentRegion

'Filters date
    With RTF
        ColMOTDate = .Rows(1).Find(what:="Last MOT date", LookIn:=xlFormulas, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext).Column - .Column + 1
        .AutoFilter field:=ColMOTDate, Criteria1:=">=" & ColDate
    End With
'Filters 'Removal' column
    With RTF
        ColRemoval = .Rows(1).Find(what:="Removal means", LookIn:=xlFormulas, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext).Column - .Column + 1
        .AutoFilter field:=ColRemoval, Criteria1:="Inspection"
    End With
'Filters Competle column
    With RTF
        ColComplete = .Rows(1).Find(what:="Complete?", LookIn:=xlFormulas, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext).Column - .Column + 1
        .AutoFilter field:=ColComplete, Criteria1:="No"
    End With

I've also tried this but I get a run-time 1004 error:

Set RTF = Worksheets("Pro").Range("Table8").Range("A1").CurrentRegion

'Filters date
    With RTF
        .AutoFilter field:=ColMOTDate, Criteria1:=">=" & ColDate
    End With

I would be grateful of any assistance.

 

[MARK858]

Ok try changing

Dim ColMOTDate As Long

to

Dim ColMOTDate As Range

and

ColMOTDate = .Rows(1).Find(what:="Last MOT date", LookIn:=xlFormulas, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext).Column - .Column + 1

to

Set ColMOTDate = .Rows(4).Find(what:="Last MOT date", LookIn:=xlFormulas, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext)

and use

CLng(ColDate)

You still might need to change xlFormulas to xlValues

Btw if you want to refer to a the headers in a Table it is easier to use

Worksheets("Pro").ListObjects("Table8").HeaderRowRange

 

[K1600]

Ok try changing

Dim ColMOTDate As Long

to

Dim ColMOTDate As Range

and

ColMOTDate = .Rows(1).Find(what:="Last MOT date", LookIn:=xlFormulas, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext).Column - .Column + 1

to

Set ColMOTDate = .Rows(4).Find(what:="Last MOT date", LookIn:=xlFormulas, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext)

and use

CLng(ColDate)

You still might need to change xlFormulas to xlValues

Btw if you want to refer to a the headers in a Table it is easier to use

Worksheets("Pro").ListObjects("Table8").HeaderRowRange

That's gone back to runtime error 91 and debug takes me to the colMOTDate = .Rows(4).Find(what:="Last MOT date", LookIn:=xlFormulas, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext) line again.

Just in case I have missed something, this is what I've got from the start now:

Dim RTF As Range
Dim colDate As Long
Dim colMOTDate As Range

Set RTF = Worksheets("Pro").Range("Table8").Range("A1").CurrentRegion '

'Filters date
    With RTF
        colMOTDate = .Rows(4).Find(what:="Last MOT date", LookIn:=xlFormulas, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext)
        .AutoFilter Field:=colMOTDate, Criteria1:=">=" & CLng(colDate)
    End With

Sorry for being a pain...

 

[MARK858]

You are missing the Set

  Set colMOTDate = .Rows(4).Find(what:="Last MOT date", LookIn:=xlFormulas, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext)

 

[K1600]

You are missing the Set

  Set colMOTDate = .Rows(4).Find(what:="Last MOT date", LookIn:=xlFormulas, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext)

Oops, sorry I missed that.

That steps through that now but errors again with a runtime 1004 "AutoFilter method of range class failed" at .AutoFilter Field:=colMOTDate, Criteria1:=">=" & CLng(colDate)

 

[MARK858]

Hover your mouse over colDate and colMOTDate and tell me what Value they show and also in a spare cell put the formula =ISNUMBER(Y7) changing the Y7 to a cell that has a date and tell me if it says FALSE or TRUE.

 

[K1600]

Hover your mouse over colDate and colMOTDate and tell me what Value they show and also in a spare cell put the formula =ISNUMBER(Y7) changing the Y7 to a cell that has a date and tell me if it says FALSE or TRUE.

Hovering over them in this bit..... .AutoFilter Field:=colMOTDate, Criteria1:=">=" & CLng(colDate) gives me:

colMOTDate = "Last MOT date"
colDate = 0

The isnumber formula returns a 'TRUE' value.

 

[MARK858]

Ok that is quite obvious, you aren't assigning a date to colDate anywhere in your code.

 

[K1600]

Ok that is quite obvious, you aren't assigning a date to colDate anywhere in your code.

Ah ok. What would I need to add to do that? Would I also need to do it for the colMOTDate too?

 

[MARK858]

What date are you filtering for? and are you hard coding it or is it a date in a cell?

 

[K1600]

What date are you filtering for? and are you hard coding it or is it a date in a cell?

Both columns will already have a date in them. The 'Date' column is a manual input and the 'Last MOT date' one is obtained from an API but at the point of this filter being applied both are in the spreadsheet as an entry.