Date COlumn AutoFiltering Issue!

[labkhand]

Hi All,

I have been trying to resolve the following issue which is driving me crazy! In a nutshell, I am trying to autofilter a date column based on a date range (from, to) which is provided by users from main menu. When I use string varibales to pass these dates to the FilterDate sub, I get wrong results but if I hard code the date range, I get good results. Below are more details....

I have a VBA sub which is use to filter out my data in a seperate worksheet as follows:

Private Sub FilterData(bRemoveExistingFilter As Boolean, _
Sht As Worksheet, _
Rng As Range, _
iField As Integer, _
Optional vCriteria1 As Variant, _
Optional vCriteria2 As Variant)
Sht.Select
With Sht
'Remove any existing filters
If (bRemoveExistingFilter) Then
Rng.AutoFilter
End If

If IsMissing(vCriteria1) Then
Rng.AutoFilter Field:=iField
Else
If (IsMissing(vCriteria2)) Then
Rng.AutoFilter Field:=iField, Criteria1:=vCriteria1, Operator:=xlFilterValues
Else
Rng.AutoFilter Field:=iField, Criteria1:=vCriteria1, Operator:=xlAnd, Criteria2:=vCriteria2
End If
End If
End With
End Sub


The sub is working. The problem is that if I call this sub using my program variables (sReportingFromDate, and sReportingToDate) as below, the target date column is not filtered correctly and I get wrong results:


Call FilterData(False, oData, oData.Range("A2").CurrentRegion, iDatesFilteringColumn, ">=" & sReportingFromDate, "<=" & sReportingToDate)


The problem seems to be the ">=" & sReportingFromDate, "<=" & sReportingToDate" part of the above call. The sReportingFromDate and sReportingToDate are STRING variables which users pass the date range they need to do reporting.

If I call the sub using hard coded dates like below, I get the correct result:

Call FilterData(False, oData, oData.Range("A2").CurrentRegion, iDatesFilteringColumn, ">=11/1/2013", "<=11/30/2013")


Can you please help me resolve this issue which is preventing me from moving forward with the rest of my program logic? thanks!

[Admin]

Hi

Use DATEVALUE

DateValue(sReportingFromDate)

[labkhand]

Thanks for your response, but I get "Type Mismatch" error when I use datevalue function since the following line in my FIlterData sub expects Variant values. Additionally, I use this sub in other places to filter data columns based on passed Variant/Array values and thus cannot change the sub declaration to anything else.

Rng.AutoFilter Field:=iField, Criteria1:=vCriteria1, Operator:=sOperator, Criteria2:=vCriteria2

[labkhand]

I was able to resolve this issue using the solution you provided. Thanks for all your help.