zakizelani
New Member
- Joined
- Mar 3, 2016
- Messages
- 25
i cant seem to find whats my mistake from the code. when i do manually filter i can get the table to filter the data base on my criteria. howver when i run my vba code, it cannot filter the data with the criteria.
Code:
Sub CopyFilter()'clear the contents
Sheet12.Range("B4:P10000").ClearContents
'copy and paste the range
Sheet7.Range("Database").SpecialCells(xlCellTypeVisible).Copy _
Destination:=Sheet12.Range("B4")
End Sub
Code:
Sub Between2Dates()'declare the variables
Dim DateBegin As Date
Dim DateEnd As Date
Dim Rng As Range
'set error handler
'On Error GoTo errHandler:
'stop screen flicker
Application.ScreenUpdating = False
'set the variables
Set Rng = Sheet7.Range("B8")
DateBegin = Format(Sheet12.Range("B2").Value, "mm/dd/yy")
DateEnd = Format(Sheet12.Range("C2").Value, "mm/dd/yy")
'check the dates if all is OK run the filter
If Sheet12.Range("B2").Value >= Sheet12.Range("C2").Value Then
MsgBox " Your start value is wrong"
Exit Sub
Else
If Not IsEmpty(DateBegin) And Not IsEmpty(DateEnd) Then
'run the filter
With Rng
.AutoFilter Field:=3, Criteria1:=">=" & DateBegin, _
Operator:=xlAnd, Criteria2:="<=" & DateEnd
'copy values
CopyFilter
'show all data
Showall
End With
End If
End If
'error block
'On Error GoTo 0
Exit Sub
'errHandler:
MsgBox "There is no data"
Showall
End Sub