Macro for using autofilter between two dates returns empty table

Miil1991

New Member
Joined
Feb 17, 2017
Messages
3
Hi guys,
as mentioned in subject, macro I use for autofilter table after double click on cell returns the table empty, although in the column the Date filter "Between" is active (there ARE the beginning and end of the month). After I press OK in the date filter, then it shows right values.
The macro is not so straightforward (I inherited it).

First things first. The double click is in Error matrix (overview of errors) and the table is then filtered based on the values (Team, Internal, External, Human mistake, etc..)
Most important is field and criteria. This should be correctly setup, I am just showing it in case I overlooked something. It is only part of the whole code.
Code:
'ERROR MATRIX
If Intersect(Target, Range("BL44:BN51")) Is Nothing Then
 Else
    strTarg = Target.Address
    splitTarg = Split(strTarg, "$", , vbTextCompare)
    rowTarg = splitTarg(2)
    lineTarg = splitTarg(1)
    strType = Range("BG" & rowTarg).Value
    strOrigin = Range(lineTarg & "43").Value
    strTeam = ActiveWorkbook.Names("teamFilter").RefersToRange.Value
    If strTeam = "Securities Services" Then
       strTeam = "*"
    Else
       strTeam = strTeam
    End If
    If strType = "Total" Then
       strType = "*"
    End If
    If strOrigin = "Total" Then
       strOrigin = "*"
    End If
    
    datMonth = ActiveWorkbook.Names("DateFilter").RefersToRange.Value
    
    criteria = Array(strType, strOrigin, strTeam, datMonth)
    fields = Array(7, 14, 5, 9)
    
    Call fctFilterTableData("tblCPET", criteria, fields)
 End If

Now here comes the fctFillterData from tblCPET. This is the filtering macro I need help with.

Code:
Public Sub fctFilterTableData(tableRef As String, criteria As Variant, fields As Variant)
    Dim ACell As Range
    Dim i As Integer
    Dim v As Variant
    Call Unhide(tableRef)
    
    'check worksheet protected
    If ActiveSheet.ProtectContents = True Then
        Call SheetUnlock(tableRef)
'        MsgBox "This macro will not work when the worksheet is write-protected.", _
'               vbOKOnly, "Filter table"
'        Exit Sub
    End If
    
    'Set reference cell in sheet
    Set ACell = ActiveSheet.Cells(3, 4)
    'Test to see if ACell is in a table or list.
    On Error Resume Next
    ActiveCellInTable = (ACell.ListObject.Name <> "")
    On Error GoTo 0
    'If the cell is in a list or table, run the code.
    If ActiveCellInTable = True Then
        'Show all data in the table or list.
        On Error Resume Next
        ActiveSheet.ShowAllData
        On Error GoTo 0
        
        i = 0
    
        [B]For Each v In criteria
            If VarType(v) = vbString Then
                ACell.ListObject.Range.AutoFilter _
                Field:=fields(i), _
                criteria1:="=" & v
         ' This will filter based on Date of occurence
            ElseIf VarType(v) = vbDate Then
                ACell.ListObject.Range.AutoFilter _
                Field:=fields(i), _
                criteria1:=">=" & DateSerial(Year(v), Month(v), Day(v)), _
                Operator:=xlAnd, _
                criteria2:="<=" & WorksheetFunction.EoMonth(v, 0)
            End If
            i = i + 1
        Next
        i = 0
    Else
' this is the part I am not sure about[/B]
    ' shows message box with "OK" button
        MsgBox "No table found at the specified location.", _
        vbOKOnly, "Filter Table"
    End If
    
    If fctCRUD = False Then
        SheetLock (tableRef)
    End If
End Sub

As I said I am wondering, why it shows the table empty, althoug the Filter for dates between is there checked, with correct dates. I tried step by step and everything works until there is date included. So I thought it might be some problem with date format? Any idea?

Thank you very much
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Forum statistics

Threads
1,215,737
Messages
6,126,577
Members
449,318
Latest member
Son Raphon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top