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,
I've also tried this but I get a run-time 1004 error:
I would be grateful of any assistance.
I currently have the following coding but I keep getting a run-time 91 error on the first line of the With statement,
VBA Code:
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:
Code:
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.