Filtering a OLAP cube Pivot Table with a slicer VBA (overflow message workarounds)

[drkballaerospace]

Hi all,

Thanks for your help in advance!

I am trying to use a slicer to filter through my OLAP cube pivot table. When I loop through all Slicer Items I get an overflow message so I replaced .Count with .VisibleSlicerItems.Count and now I am getting an "Object doesn't support this property or method" error message. Any advice? Or maybe there is a better approach to take. My end goal is to allow a user to input two keywords and search through the "Part Description" field for entries that contain both or one of those values. This is a trial case using just one value.

My current code:

Dim sC As SlicerCache
Dim sL As SlicerCacheLevel
Dim sI As SlicerItem
Dim aArray() As Variant
Dim i As Long
Dim pdk1 As String
pdk1 = PD1.Value
'this value is coming from a userform

ActiveWorkbook.SlicerCaches.Add2(ActiveSheet.PivotTables("NEWAR"), _
        "[Part].[Part Description]").Slicers.Add ActiveSheet, _
        "[Part].[Part Description].[Part Description]", "Part Description", _
        "Part Description", 306.75, 786, 144, 198.75

Set sC = ActiveWorkbook.SlicerCaches("Slicer_Part_Description")
Set sL = sC.SlicerCacheLevels(1) 'this will start with the first item in the slicer

 With sL
           For i = 1 To .VisibleSlicerItems.Count
               'initally For i = 1 to .Count but that gave overflow message
                  If sL.SlicerItems.Item(i).Name Like "*" & pdk1 & "*" Then
                      'check if pdk1 is contained anywhere in the slicer item caption
                                      ReDim Preserve aArray(0 To i) As Variant
                                     aArray(i) = sL.SlicerItems.Item(i).Name
                                     'add that slicer item name to an array
                 End If
Next i
            
sC.VisibleSlicerItemsList = aArray 'this set the visible items
                                               '= to the array you just created
            
'ActiveSheet.Shapes("Part Description").Visible = False
            'to hide this slicer, uncomment the line above
    End With