Posts by gabriel222

  • Getting “Type Mismatch” with VBA PivotTables and Filter Dates

    Hi all I am encountering a very frustrating problem with some VBA, especially seen as the hardcoded value of the variable works well, but not the variable itself !


    The 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


    The 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 :

    • 2017
    • then the first Quarter of 2018 (called "T1-JFM" below)
    • then nothing for Months
    • 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 :

    • The full Macro at the bottom of the post (The Dim(s) are at the top and Functions at the bottom)
    • Code
      .PivotFilters.Add2

      method but it doesn't seem to work seen as it is a

      Code
      xlPageField

      field

    • Playing around with the quotation marks for DIM_ARRAY (both """" and Chr(32) )in multiple shapes and forms
    • Taking off the array() function --> ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = DIM_ARRAY
    • 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--->


    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)



    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: