Date COlumn AutoFiltering Issue!

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:

Code:

---

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!

Hi

Use DATEVALUE

DateValue(sReportingFromDate)

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

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