Sub SearchSlicer()
Dim oSc As SlicerCache
Dim oSi As SlicerItem
Dim searchword As Variant
searchword = InputBox("Enter a searchword")
Set oSc = ThisWorkbook.SlicerCaches("Slicer_SEARCH_WORDS")
For Each oSi In oSc.SlicerItems
If oSi.Name Like "*" & UCase(searchword) & "*" Then
oSi.Selected = True
Else: oSi.Selected = False
End If
Next
End Sub
So I have a pivot table with two columns - a column for a verb and a column for search words containing synonyms/similar phrases. For example, if a word in the first column is 'broke', the second column will contain the list 'fell apart, broken, split'.
Running the above code asks the user for a search word which will be used to loop through the search word column and filter the search word pivot table and slicers.
This search word slicer is linked to first column slicer as the search word slicer is not visible on the input sheet.
The reason for using slicers is because I have another code for when the user clicks on a slicer choice, it automatically pastes it into the active cell.
I have a table with 350 rows and this code is already taking very long to run. How can I make this more optimal?