drkballaerospace
New Member
- Joined
- Jul 18, 2017
- Messages
- 3
Hi all,
Thank you for your help ahead of time!
Below is my current code. My goal is to filter a very large pivot table (connected to an OLAP Cube) with a user defined keyword. The keyword is entered through a separate userform and stored in variable pdk1. When I run this code I get an overflow message for this line: For i = 1 To .Count. I want to be bale to filter the table through a pivot table so that I can ultimately get two user defined keywords and display all entries that contain keyword #1 and/or keyword #2. If there is a better way to accomplish this please let me know! Again I appreciate all of your help.
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
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 .Count
If sL.SlicerItems.Item(i).name Like "*" & pdk1 & "*" Then
'GoTo nextiteration 'this will skip over anything
'like oran when saving to the array
ReDim Preserve aArray(0 To i) As Variant
aArray(i) = sL.SlicerItems.Item(i).name
End If
'nextiteration:
Next i
sC.VisibleSlicerItemsList = aArray 'this set the visible items
'= to the array you just created
'ActiveSheet.Shapes("Status").Visible = False
'to hide this slicer, uncomment the line above
End With
Thank you for your help ahead of time!
Below is my current code. My goal is to filter a very large pivot table (connected to an OLAP Cube) with a user defined keyword. The keyword is entered through a separate userform and stored in variable pdk1. When I run this code I get an overflow message for this line: For i = 1 To .Count. I want to be bale to filter the table through a pivot table so that I can ultimately get two user defined keywords and display all entries that contain keyword #1 and/or keyword #2. If there is a better way to accomplish this please let me know! Again I appreciate all of your help.
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
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 .Count
If sL.SlicerItems.Item(i).name Like "*" & pdk1 & "*" Then
'GoTo nextiteration 'this will skip over anything
'like oran when saving to the array
ReDim Preserve aArray(0 To i) As Variant
aArray(i) = sL.SlicerItems.Item(i).name
End If
'nextiteration:
Next i
sC.VisibleSlicerItemsList = aArray 'this set the visible items
'= to the array you just created
'ActiveSheet.Shapes("Status").Visible = False
'to hide this slicer, uncomment the line above
End With
