1
\$\begingroup\$
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?

\$\endgroup\$
1
\$\begingroup\$

Invariants, variables which don't change in a loop, should be set before entering the loop. Additionally, if you want to store the value of a Boolean expression, just store the expression's return value:

Sub SearchSlicer()
    Dim oSc As SlicerCache
    Dim oSi As SlicerItem
    Dim searchword As Variant

    searchword = InputBox("Enter a searchword")
    searchword = "*" & UCase(searchword) & "*"
    Set oSc = ThisWorkbook.SlicerCaches("Slicer_SEARCH_WORDS")

    For Each oSi In oSc.SlicerItems
        oSi.Selected = oSi.Name Like searchword
    Next oSi
End Sub

But I doubt this will speed up the loop much. Most likely the 'like' operator is where most of the time is spent (and I don't see any alternative to it).

\$\endgroup\$
  • \$\begingroup\$ I think, in this particular case, the like might be better off with instr - this of course is not compatible with your new structure. \$\endgroup\$ – Raystafarian Aug 22 '18 at 4:25
  • \$\begingroup\$ Thanks for the response! It did speed up the code a little bit. This will be very helpful for other tables with less rows in and I will just have to find a way to work around the table with many rows. \$\endgroup\$ – C L Aug 22 '18 at 12:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.