Getting “Type Mismatch” with VBA PivotTables and Filter Dates

Gabriel222

Gabriel222

New Member
Joined
Oct 24, 2008
Messages
14
Office Version
  1. 365
Platform
  1. Windows
Goal: To filter each "pre-structured" pivot table with different ranges of dates, (the ranges are all continuous), the pivot tables are connected to a Tabular OLAP Cube

Script Description : In the script, (full script at the bottom of the post) I loop through the Years and then the Quarters (called Trimesters here), Months and finally the Dates as needed (the dates are calculated via a worksheet and called using a custom function), the idea here is to simulate what a user does with his mouse.

So at the moment with the below Sub, for a given PivotTable, in order to select for example all dates starting from the 1st of January 2017 up until Sunday the 8th of April included I will select :

  1. 2017
  2. then the first Quarter of 2018 (called "T1-JFM" below)
  3. then nothing for Months
  4. then all the dates of April up until the 8th included

Problem :
Once I hit the Sub that concerns the Dates ie: Cycle_Date, I get an error on the line:
Code: 
ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = Array(DIM_ARRAY)

The type of error message encountered on that line is "Run-time error ' 13': Type mismatch"

that line of code is the line that actually applies the Date filters, (prior to applying the filters the variable DIM_ARRAY is an Array that "accumulates" the strings used in the filter).

Already attempted :
  1. The full Macro at the bottom of the post (The Dim(s) are at the top and Functions at the bottom)
  2. Code: 
    .PivotFilters.Add2
    method but it doesn't seem to work seen as it is a
    Code: 
    xlPageField
    field
  3. Playing around with the quotation marks for DIM_ARRAY (both """" and Chr(32) )in multiple shapes and forms
  4. Taking off the array() function --> ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = DIM_ARRAY
  5. I also tried to apply the filter at each iteration, that works, but only the last "date" is kept, the previous ones are erased so i cannot add dates

BIG "CLUE": Hardcoding the value of DIM-ARRAY works... so for some reason

THIS DOESN'T WORK--->
Code: 
Private Sub Cycle_Date(YEAR_i_max As String, TRIMESTRE_i As String, MONTH_i As String, MONTH_i_max As Integer)

DATE_i_min = StdFilter(SheetName, "DATE_i_min")
DATE_i_max = StdFilter(SheetName, "DATE_i_max")

    If DATE_i_min = DATE_i_max Then ' Fork-out scenario
        
        i = 1
        
        DATE_i = WorksheetFunction.Proper(WorksheetFunction.Text(DateSerial(YEAR_i_max, MONTH_i_max, i), "YYYY-MM-DDTHH:MM:SS"))
        
        DIM_ARRAY = DIM_DATE_SUB & DIM_DATE_MOD_YEAR & ".&[" & YEAR_i_max & "]" & ".&[" & TRIMESTRE_i & "]" & ".&[" & MONTH_i & "]" & ".&[" & DATE_i & "]"
        
        ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = Array(DIM_ARRAY)
        
        Exit Sub
        
    End If

    For i = DATE_i_min To DATE_i_max ' Loop through

        DATE_i = Format(DateSerial(YEAR_i_max, MONTH_i_max, i), "YYYY-MM-DDTHH:MM:SS")
    
        DIM_ARRAY_elmt = DIM_DATE_SUB & DIM_DATE_MOD_YEAR & ".&[" & YEAR_i_max & "]" & ".&[" & TRIMESTRE_i & "]" & ".&[" & MONTH_i & "]" & ".&[" & DATE_i & "]"
    
        If i = DATE_i_min Then
        
                DIM_ARRAY = DIM_ARRAY_elmt

            ElseIf i = DATE_i_max Then
            
                DIM_ARRAY = DIM_ARRAY & DIM_ARRAY_elmt
                
            ElseIf DIM_ARRAY <> "" Then
                
                DIM_ARRAY = DIM_ARRAY & DIM_ARRAY_elmt

        End If

    Next

    Debug.Print "Dates -->"; DIM_ARRAY
    
    ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = Array(DIM_ARRAY)
    
End Sub

but replacing DIM_ARRAY with its value, this DOES work!! --->
(here I replaced DIM_ARRAY with exactly what is evaluated by Debug.Print DIM_ARRAY)

Code: 
Private Sub Cycle_Date(YEAR_i_max As String, TRIMESTRE_i As String, MONTH_i As String, MONTH_i_max As Integer)

DATE_i_min = StdFilter(SheetName, "DATE_i_min")
DATE_i_max = StdFilter(SheetName, "DATE_i_max")

    If DATE_i_min = DATE_i_max Then ' Fork-out scenario
        
        i = 1
        
        DATE_i = WorksheetFunction.Proper(WorksheetFunction.Text(DateSerial(YEAR_i_max, MONTH_i_max, i), "YYYY-MM-DDTHH:MM:SS"))
        
        DIM_ARRAY = DIM_DATE_SUB & DIM_DATE_MOD_YEAR & ".&[" & YEAR_i_max & "]" & ".&[" & TRIMESTRE_i & "]" & ".&[" & MONTH_i & "]" & ".&[" & DATE_i & "]"
        
        ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = Array(DIM_ARRAY)
        
        Exit Sub
        
    End If

    For i = DATE_i_min To DATE_i_max ' Loop through

        DATE_i = Format(DateSerial(YEAR_i_max, MONTH_i_max, i), "YYYY-MM-DDTHH:MM:SS")
    
        DIM_ARRAY_elmt = DIM_DATE_SUB & DIM_DATE_MOD_YEAR & ".&[" & YEAR_i_max & "]" & ".&[" & TRIMESTRE_i & "]" & ".&[" & MONTH_i & "]" & ".&[" & DATE_i & "]"
    
        If i = DATE_i_min Then
        
                DIM_ARRAY = """" & DIM_ARRAY_elmt & """"

            ElseIf i = DATE_i_max Then
            
                DIM_ARRAY = DIM_ARRAY & "," & """" & DIM_ARRAY_elmt & """"
                
            ElseIf DIM_ARRAY <> "" Then
                
                DIM_ARRAY = DIM_ARRAY & "," & """" & DIM_ARRAY_elmt & """"

        End If

    Next

    Debug.Print DIM_ARRAY
    
    ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = Array( _
    "[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-01T00:00:00]", _
    "[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-02T00:00:00]", _
    "[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-03T00:00:00]", _
    "[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-04T00:00:00]", _
    "[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-05T00:00:00]", _
    "[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-06T00:00:00]", _
    "[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-07T00:00:00]", _
    "[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-08T00:00:00]")
    
End Sub

Any ideas how I can resolve this? if you need additional information please don't hesitate

I'm on Excel VBA (MS0 365 - version 1708)

Thanks a ton !


Full Macro below:


Code: 
Option Explicit




' "_i" is for the iterating variables
' "min" and "max" are for the minimum and maximum bounds that define the iteration range
' "TRIMESTRE" is just another word for "QUARTER"




Dim DIM_DATE_MOD_YEAR As String ' for storing OLAP syntax
Dim DIM_DATE_MOD_TRIMESTRE As String ' for storing OLAP syntax
Dim DIM_DATE_MOD_MONTH As String ' for storing OLAP syntax
Dim DIM_DATE_MOD_DATE As String ' for storing OLAP syntax




Dim DIM_DATE_CREATION_BASE As String 'changes for each PivotTable "structure" but here we are using just one type of "structure" and just filtering the dates differently
Dim DIM_DATE_SUB As String 'changes for each Sub only, so only as we "loop" and/or "fork" and/or "fork/past" throughout the relevant Years Quarters Months and Dates
Dim DIM_DATE_PTF As String 'is used for the PivotTableField when the filters are applied




Dim DIM_ARRAY_elmt As String ' "feeds" DIM_ARRAY
Dim DIM_ARRAY As String ' is used as the string applied to the pivottable filters and is fed/stacked by DIM_ARRAY_elmt
Dim DIM_TEST As String




Dim i As Integer ' standard iterator




Dim YEAR_i As Integer
Dim YEAR_i_min As String
Dim YEAR_i_max As String




Dim TRIMESTRE_i As String
Dim TRIMESTRE_i_max As String
Dim TRIMESTRE_i_min As String




Dim MONTH_i As String
Dim MONTH_i_max As Integer
Dim MONTH_i_min As Integer




Dim DATE_i As Variant
Dim DATE_i_min As Integer
Dim DATE_i_max As Integer




Dim ws As Variant
Dim SheetNames As Variant
Dim SheetName As String
'Space












Sub Launch_Update()




'Application.ScreenUpdating = False




    Call Date_Filters




'Application.ScreenUpdating = True




End Sub








Private Sub Date_Filters()
   
        SheetNames = Array("NOW", "A-0 || J-7", "A-1 || à Date Equiv.", "A-1 || J-7 Atterissage") 'list of relevant sheets
        
        'setting variables:
        DIM_DATE_CREATION_BASE = "[DIM_DATE_CREATION].[CALENDRIER_CREATION]"
        
        DIM_DATE_SUB = DIM_DATE_CREATION_BASE & DIM_DATE_MOD_YEAR
        
        i = 0
        
    For Each ws In SheetNames
    
        Sheets(ws).Select
        SheetName = ActiveSheet.Name
        
            DIM_DATE_MOD_YEAR = ".[ANNEE]"
            DIM_DATE_PTF = DIM_DATE_CREATION_BASE & DIM_DATE_MOD_YEAR
            
        Call Cycle_Year
            
            i = 0
            DIM_DATE_MOD_TRIMESTRE = ".[TRIMESTRE]"
            DIM_DATE_PTF = DIM_DATE_CREATION_BASE & DIM_DATE_MOD_TRIMESTRE
            
        Call Cycle_Trimestre(YEAR_i_max)
            
            i = 0
            DIM_DATE_MOD_MONTH = ".[MOIS]"
            DIM_DATE_PTF = DIM_DATE_CREATION_BASE & DIM_DATE_MOD_MONTH
        
        Call Cycle_Month(YEAR_i_max, TRIMESTRE_i)
            
            i = 0
            DIM_DATE_MOD_DATE = ".[DATE]"
            DIM_DATE_PTF = DIM_DATE_CREATION_BASE & DIM_DATE_MOD_DATE
        
        Call Cycle_Date(YEAR_i_max, TRIMESTRE_i, MONTH_i, MONTH_i_max)
        
    Next ws
    
    MsgBox "Date Filter Sub has ended"




End Sub




Private Sub Cycle_Year()




YEAR_i_min = StdFilter(SheetName, "YEAR_i_min")
YEAR_i_max = StdFilter(SheetName, "YEAR_i_max")




    If YEAR_i_min = YEAR_i_max Then ' Fork-out scenario
        
        ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = Array("")
        
        Exit Sub
        
    End If








    For YEAR_i = YEAR_i_min To YEAR_i_max - 1 ' Loop through




        DIM_ARRAY_elmt = DIM_DATE_PTF & ".&[" & YEAR_i & "]"




            If YEAR_i = YEAR_i_min Then
                DIM_ARRAY = DIM_ARRAY_elmt
  
            ElseIf YEAR_i = YEAR_i_max - 1 Then
                DIM_ARRAY = DIM_ARRAY & "," & DIM_ARRAY_elmt
            
            ElseIf DIM_ARRAY <> "" Then
                DIM_ARRAY = DIM_ARRAY & "," & DIM_ARRAY_elmt
                    
            End If




    Next








Debug.Print "Years -->"; DIM_ARRAY




    ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = Array(DIM_ARRAY)




End Sub




Private Sub Cycle_Trimestre(YEAR_i_max As String)








TRIMESTRE_i_min = StdFilter(SheetName, "TRIMESTRE_i_min")
TRIMESTRE_i_max = StdFilter(SheetName, "TRIMESTRE_i_max")




    If TRIMESTRE_i_min = TRIMESTRE_i_max Then ' Fork-out scenario
        
        ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = Array("")
        
        Exit Sub
        
    End If








    For i = TRIMESTRE_i_min To TRIMESTRE_i_max ' Loop through
    
        If i = 1 Then
            TRIMESTRE_i = "T1 - JFM"
        
            ElseIf i = 2 Then
                TRIMESTRE_i = "T2 - AMJ"
                
            ElseIf i = 3 Then
                TRIMESTRE_i = "T3 - JAS"
                
            ElseIf i = 4 Then
                TRIMESTRE_i = "T4 - OND"
        
        End If




    
    DIM_ARRAY_elmt = DIM_DATE_SUB & DIM_DATE_MOD_YEAR & ".&[" & YEAR_i_max & "]" & ".&[" & TRIMESTRE_i & "]"
    
    
        If i = TRIMESTRE_i_min Then
            DIM_ARRAY = DIM_ARRAY_elmt
          
            ElseIf i = TRIMESTRE_i_max Then
                GoTo ApplyFilter1
                            
            ElseIf DIM_ARRAY <> "" Then
                DIM_ARRAY = DIM_ARRAY & "," & DIM_ARRAY_elmt
                    
        End If




    Next








ApplyFilter1:
    
    Debug.Print "Trimesters -->"; DIM_ARRAY
    
    ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = Array(DIM_ARRAY)




End Sub
Private Sub Cycle_Month(YEAR_i_max As String, TRIMESTRE_i As String)








MONTH_i_min = StdFilter(SheetName, "MONTH_i_min")
MONTH_i_max = StdFilter(SheetName, "MONTH_i_max")




    If MONTH_i_min = MONTH_i_max Then ' Fork-out scenario
    
        ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = Array("")
        
        MONTH_i = WorksheetFunction.Proper(WorksheetFunction.Text(DateSerial(2018, MONTH_i_min, 1), "[$-40C]MMMM"))
        
        Exit Sub
        
    End If




    For i = MONTH_i_min To MONTH_i_max ' Loop through




    MONTH_i = WorksheetFunction.Proper(WorksheetFunction.Text(DateSerial(2018, i, 1), "[$-40C]MMMM"))
    
    DIM_ARRAY_elmt = DIM_DATE_SUB & DIM_DATE_MOD_YEAR & ".&[" & YEAR_i_max & "]" & ".&[" & TRIMESTRE_i & "]" & ".&[" & MONTH_i & "]"
    
    
        If i = MONTH_i_min Then
            DIM_ARRAY = DIM_ARRAY_elmt
          
            ElseIf i = MONTH_i_max Then
                DIM_ARRAY = DIM_ARRAY_elmt
                GoTo ApplyFilter2
            
            ElseIf DIM_ARRAY <> "" Then
                DIM_ARRAY = DIM_ARRAY & "," & DIM_ARRAY_elmt
                    
        End If




    Next




ApplyFilter2:




    Debug.Print "Months -->"; DIM_ARRAY
    
    ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = Array(DIM_ARRAY)




    MONTH_i = WorksheetFunction.Proper(WorksheetFunction.Text(DateSerial(2018, i - 1, 1), "[$-40C]MMMM"))




End Sub
Private Sub Cycle_Date(YEAR_i_max As String, TRIMESTRE_i As String, MONTH_i As String, MONTH_i_max As Integer)








DATE_i_min = StdFilter(SheetName, "DATE_i_min")
DATE_i_max = StdFilter(SheetName, "DATE_i_max")




    If DATE_i_min = DATE_i_max Then ' Fork-out scenario
        
        i = 1
        
        DATE_i = WorksheetFunction.Proper(WorksheetFunction.Text(DateSerial(YEAR_i_max, MONTH_i_max, i), "YYYY-MM-DDTHH:MM:SS"))
        
        DIM_ARRAY = DIM_DATE_SUB & DIM_DATE_MOD_YEAR & ".&[" & YEAR_i_max & "]" & ".&[" & TRIMESTRE_i & "]" & ".&[" & MONTH_i & "]" & ".&[" & DATE_i & "]"
        
        ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = Array(DIM_ARRAY)
        
        Exit Sub
        
    End If




    For i = DATE_i_min To DATE_i_max ' Loop through




        DATE_i = Format(DateSerial(YEAR_i_max, MONTH_i_max, i), "YYYY-MM-DDTHH:MM:SS")
    
        DIM_ARRAY_elmt = DIM_DATE_SUB & DIM_DATE_MOD_YEAR & ".&[" & YEAR_i_max & "]" & ".&[" & TRIMESTRE_i & "]" & ".&[" & MONTH_i & "]" & ".&[" & DATE_i & "]"
    
        If i = DATE_i_min Then
        
                DIM_ARRAY = """" & DIM_ARRAY_elmt & """"




            ElseIf i = DATE_i_max Then
            
                DIM_ARRAY = DIM_ARRAY & "," & """" & DIM_ARRAY_elmt & """"
                
            ElseIf DIM_ARRAY <> "" Then
                
                DIM_ARRAY = DIM_ARRAY & "," & """" & DIM_ARRAY_elmt & """"




        End If




    Next




    Debug.Print "Dates -->"; DIM_ARRAY
    
    ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = Array( _
    "[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-01T00:00:00]", _
    "[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-02T00:00:00]", _
    "[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-03T00:00:00]", _
    "[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-04T00:00:00]", _
    "[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-05T00:00:00]", _
    "[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-06T00:00:00]", _
    "[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-07T00:00:00]", _
    "[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-08T00:00:00]")
    
End Sub
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
You must log in or register to reply here.

Similar threads

S
  • Solved
VBA for Pivot to filter on cell value not working
2
Replies
18
Views
884
Alex Blakenburg
A
S
  • Question
Keep getting an "Error 1004: item could not be found in the OLAP cube" when applying the filter to pivot table
Replies
5
Views
929
RoryA
RoryA
C
  • Question
VBA filter Pivot table based on a specific cell value
Replies
5
Views
322
Domenic
D
F
  • Solved
Problems filtering a Pivot Table with VBA
Replies
2
Views
186
Domenic
D
Agnarr
  • Question
VBA problem with creating new sheets
Replies
0
Views
341
Agnarr
Agnarr

Forum statistics

Threads
1,214,829
Messages
6,121,826
Members
449,051
Latest member
excelquestion515

Share this page

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