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.
Now here comes the fctFillterData from tblCPET. This is the filtering macro I need help with.
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
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