3

I am still learning Excel VBA and would appreciate it if anyone can help me get this right. I want to filter a date column based on a startDate and endDate I specified in a sheet called DateMaster. When I run the macro, it gives me Run Time Error 13, Start Date = 0. Below is my code. 

Sub FIlterCopy()

Dim StartDate As Long
Dim EndDate As Long

StartDate = ThisWorkbook.Worksheets("DateMaster").Range("C2").Value
EndDate = ThisWorkbook.Worksheets("DateMaster").Range("D2").Value

Application.ScreenUpdating = False

ThisWorkbook.Worksheets("FilterMaster").Activate

Range("A:BA").Select

Selection.ClearContents

Application.Workbooks.Open ("C:\WRI\Data\Revenue Update.xls")

Windows("Revenue Update.xls").Activate

Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.AutoFilter
Selection.AutoFilter Field:=5, Criteria1:= _
"=Backlog", Operator:=xlOr, Criteria2:="=RMA"
Selection.AutoFilter Field:=29, Criteria1:= _
"=Direct"
Selection.AutoFilter Field:=20, Criteria1:=">=" & StartDate, Operator:=xlAnd, Criteria2:="<=" & EndDate

Application.ScreenUpdating = True

End Sub
Thomas Bormans
  • 5,156
  • 6
  • 34
  • 51
ASE Dev
  • 512
  • 2
  • 5
  • 14
  • Have you tried stepping through your code using `F8`? Also, I would avoid selecting and activating in your code. [This post explains how](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). You are getting this error though because it is a type mismatch. – Dan Mar 29 '16 at 17:54
  • 2
    (1) If you want to store a date in a variable then you might want to save it as such: `Dim StartDate As Date`. (2) The error message is pretty obvious. Why don't you check if cell C2 truly contains a valid date. (3) Even if there is a date you might want to confirm by adding: `If IsDate(ThisWorkbook.Worksheets("DateMaster").Range("C2").Value) Then` just before assigning the date and a `Debug.Print StartDate` just after assigning the variable. – Ralph Mar 29 '16 at 19:18
  • 1
    Dim Start as Date worked, not string. Thanks Guys!!! – ASE Dev Mar 29 '16 at 20:23

1 Answers1

1

If you set StartDate and EndDate as Long, you should use the CDate command at autofilter

Selection.AutoFilter Field:=20, Criteria1:=">=" & CDate(StartDate), Operator:=xlAnd, Criteria2:="<=" & CDate(EndDate)

Otherwise, define both variables as Date.

soundslikeodd
  • 1,078
  • 3
  • 19
  • 32
Diego Ribba
  • 138
  • 8