Pivot table array - PivotItemExists

[Souljacked]

Hello,

I have a functioning array that generates dates from a start date to end date. The array is then passed to the pivot table and everything functions correctly.

However occasionally there are dates generated that are not included in my pivot table and when the routine trys to select them I get an error.

What I want to do is when a pivot item does not exist, the routine will just carry on and try to apply the remaining values in the array.

I've tried to read around the subject but so far found no solutions.

Can anyone help?

'create trade date array
    numDays = enDate - stDate
    
    ReDim dateArr_TradeDate(numDays + 1)
    
    dateArr_TradeDate(0) = "[Trade Date].[Year -  Quarter -  Month -  Date].[Year].&[20" & Right(stDate, 2) & _
    "].&[" & Application.WorksheetFunction.RoundUp(month(stDate) / 3, 0) & "].&[" & (Mid(stDate, 4, 2) * 1) & _
    "].&[20" & Right(stDate, 2) & "." & Mid(stDate, 4, 2) & "." & Left(stDate, 2) & "]"
    
    For x = 1 To numDays
    
    dateArr_TradeDate(x) = "[Trade Date].[Year -  Quarter -  Month -  Date].[Year].&[20" & Right(stDate + x, 2) & _
    "].&[" & Application.WorksheetFunction.RoundUp(month(stDate + x) / 3, 0) & "].&[" & (Mid(stDate + x, 4, 2) * 1) & _
    "].&[20" & Right(stDate + x, 2) & "." & Mid(stDate + x, 4, 2) & "." & Left(stDate + x, 2) & "]"
    
    Next x

'set trade date criteria
    pt.PivotFields("[Trade Date].[Year -  Quarter -  Month -  Date].[Date]").VisibleItemsList = _
        Array(dateArr_TradeDate())

Cheers,
Souljacked

 

[nphadro]

Jerry, If I just want to take a name range (a list of account numbers) and I want to filter by those how to I convert that list into an array?

 

[JN13BH]

Hello,
The code above works great.

I am trying to modify it slightly to accept a named range with about 208 different zip codes to update the filter.

Currently using the method this is using now I am limited to the 24 line limit in continuous lines so I'd like to use the range.

So far the attempts I've made to insert the named range results in a continuous loop. In this case I've defined a range and made varArrIn:= to that.

My VBA knowledge is very basic still. Any thoughts on how I can take named range in BU1:BU208 and use this code to update on those fields? In addition some of the items in the named range may not exist in the member properties of the Cube.

Thank you,

 

[Jerry Sullivan]

Welcome to MrExcel,

This post includes a function named "sOLAP_FilterByItemList" that you can use for that purpose.

http://www.mrexcel.com/forum/excel-...ate-range-slicer-using-macro.html#post4443504

If you need any help adapting that, I'd suggest you start a new thread since this thread is rather old. If you do that, please add a post to this thread with a link to that new thread.

 

[JN13BH]

Hi Jerry,
Thank you so much for the link. I am having some issues adapting the code in the suggested thread to my specific purpose.

Here is a link to a new thread I've created. What I've tried so far ends with a "No Matches Found" error.


http://www.mrexcel.com/forum/excel-...ange-olap-slicer-using-macro.html#post4495018

Best Regards,