Home VBA - OLAP Cube Filter - Test if Items exist
 I have a filter on an OLAP Cube table in excel  ActiveSheet.PivotTables("cohortCLVTwoYear").CubeFields(17).EnableMultiplePageItems = _ True ActiveSheet.PivotTables("cohortCLVTwoYear").PivotFields( _ "[data1].[CUST_COMBINEDmonthsFromOrder].[CUST_COMBINEDmonthsFromOrder]"). _ VisibleItemsList = Array( _ "[data1].[CUST_COMBINEDmonthsFromOrder].&[01-03_Months]", _ "[data1].[CUST_COMBINEDmonthsFromOrder].&[04-06_Months]", _ "[data1].[CUST_COMBINEDmonthsFromOrder].&[07-09_Months]", _ "[data1].[CUST_COMBINEDmonthsFromOrder].&[10-12_Months]", _ "[data1].[CUST_COMBINEDmonthsFromOrder].&[13-18_Months]", _ "[data1].[CUST_COMBINEDmonthsFromOrder].&[19-24_Months]")  The data set that this is filtering is a length of time a person has been a customer. On some of the brands I work on that are newer, they haven't existed for as long, so don't have some of the later items to filter on such as 19-24_months I tried to create some error handling that if the above doesn't work, try  ActiveSheet.PivotTables("cohortCLVTwoYear").CubeFields(17).EnableMultiplePageItems = _ True ActiveSheet.PivotTables("cohortCLVTwoYear").PivotFields( _ "[data1].[CUST_COMBINEDmonthsFromOrder].[CUST_COMBINEDmonthsFromOrder]"). _ VisibleItemsList = Array( _ "[data1].[CUST_COMBINEDmonthsFromOrder].&[01-03_Months]", _ "[data1].[CUST_COMBINEDmonthsFromOrder].&[04-06_Months]", _ "[data1].[CUST_COMBINEDmonthsFromOrder].&[07-09_Months]", _ "[data1].[CUST_COMBINEDmonthsFromOrder].&[10-12_Months]", _ "[data1].[CUST_COMBINEDmonthsFromOrder].&[13-18_Months]")  Eventually taking the filter down to ...  ActiveSheet.PivotTables("cohortCLVTwoYear").CubeFields(17).EnableMultiplePageItems = _ True ActiveSheet.PivotTables("cohortCLVTwoYear").PivotFields( _ "[data1].[CUST_COMBINEDmonthsFromOrder].[CUST_COMBINEDmonthsFromOrder]"). _ VisibleItemsList = Array( _ "[data1].[CUST_COMBINEDmonthsFromOrder].&[01-03_Months]")  01-03_months will always work. However as soon as an error is hit when a filter item doesn't exist, i get the following error. Run-Time error '1004: The Item could not be found in the OLAP Cube So this is where i am at now, i need to be able to check if the items exist, if they are all there, do the first filter, if one is missing filter without it. Could anyone help me write that checker? Cheers John