Hi all, this one has got me stumped!
I am writing an Excel VBA Application for a Catering Company who run Functions. I am trying to Filter my Database on a From and To Date for Functions booked between two dates. I am also Sorting the Database on whether it is Proceeding (YES or NO in Column 10) and Function Date Order (in Column 2) in ascending order.
PROBLEM
Not all Functions are appearing between the two dates, only some?
Dates are formatted as Custom > "dd/mm/yyyy" both on the Input Worksheet (Cells AV23 and BA23), which hold the From and To Dates, and the Database Column 2 that holds each Function Date.
HERE IS MY CODE
'-----------------------------------------------------------'
' LIST ALL FUNCTIONS IN LISTBOX1 THAT MATCH FROM & TO DATES '
' CALLED FROM: '
' - Sub cmdFilter_Click() '
'-----------------------------------------------------------'
'
Private Sub FilterFunction()
Dim fromDate, toDate As Date
If wsInput.Range("AV23") = "" Then ' If From is Blank
MsgBox "FROM Date needs to be input."
Exit Sub
End If
If wsInput.Range("BA23") = "" Then ' If To is Blank
MsgBox "TO Date needs to be input."
Exit Sub
End If
' Populate Date Range
fromDate = wsInput.Range("AV23").Value
toDate = wsInput.Range("BA23").Value
Application.ScreenUpdating = False
' Filter Database on Functions Proceeding in Selected Date Range
With db
.Visible = True
.ListObjects("tblDb").Range.AutoFilter Field:=10, Criteria1:="YES"
.ListObjects("tblDb").Range.AutoFilter Field:=2, Criteria1:=">=" & CDbl(fromDate), Operator:=xlAnd, Criteria2:="<=" & CDbl(toDate)
' Sort Database on Function Date and Main Meal Service Time following Filtering
Call SortDatabase
.Activate
.Range("A1").Select
End With
Application.ScreenUpdating = True
End Sub
'--------------------------------'
' SORT DATABASE ON DATE AND TIME '
' CALLED FROM: '
' - Sub FilterFunction() '
'--------------------------------'
'
Sub SortDatabase()
ActiveWorkbook.Worksheets("Database").ListObjects("tblDb").Sort.SortFields. _
Clear
ActiveWorkbook.Worksheets("Database").ListObjects("tblDb").Sort.SortFields. _
Add2 Key:=Range("tblDb[Function Date]"), SortOn:=xlSortOnValues, Order:= _
xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Database").ListObjects("tblDb").Sort.SortFields. _
Add2 Key:=Range("tblDb[Main Meal Service Time]"), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Database").ListObjects("tblDb").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Can someone please help me?