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.