VBA Select slicer item based based on a cell ref, but if the item is not in the slicer then select a specific slicer item

[Boytjie79]

Hi, I am using the below VBA to select a slicer item based on a cell ref. This works fine, however if the item in the cell is not in the slicer then i want it to select an item in the slicer called "No Data"

The code i'm currently using is below, could someone please help me with auto selecting the item "No Data" if the cell ref is not an option in the slicer?

Many Thanks

Sub Temp1()

' Uber Eats Slicer
Dim UEselected As String
UEselected = ActiveSheet.Range("B5").Value

With ActiveWorkbook.SlicerCaches("Slicer_Brand1")
.ClearManualFilter
For Each oSlicerItem In .SlicerItems
If oSlicerItem.Name = UEselected Then
oSlicerItem.Selected = True
Else
oSlicerItem.Selected = False
End If
Next oSlicerItem
End With

End Sub

 

[Domenic]

Try the following...

Sub Temp1()

    ' Uber Eats Slicer
    Dim UEselected As String
    UEselected = ActiveSheet.Range("B5").Value
    
    With ActiveWorkbook.SlicerCaches("Slicer_Brand1")
        .ClearManualFilter
        On Error Resume Next
        Dim TargetSlicerItem As SlicerItem
        Set TargetSlicerItem = .SlicerItems(UEselected)
        If TargetSlicerItem Is Nothing Then
            Set TargetSlicerItem = .SlicerItems("No Data")
        End If
        On Error GoTo 0
        If Not TargetSlicerItem Is Nothing Then
            Dim oSlicerItem As SlicerItem
            For Each oSlicerItem In .SlicerItems
                If oSlicerItem.Name = TargetSlicerItem.Name Then
                    oSlicerItem.Selected = True
                Else
                    oSlicerItem.Selected = False
                End If
            Next oSlicerItem
        End If
    End With

End Sub

Hope this hleps!

 

[Boytjie79]

Try the following...

Sub Temp1()

    ' Uber Eats Slicer
    Dim UEselected As String
    UEselected = ActiveSheet.Range("B5").Value
   
    With ActiveWorkbook.SlicerCaches("Slicer_Brand1")
        .ClearManualFilter
        On Error Resume Next
        Dim TargetSlicerItem As SlicerItem
        Set TargetSlicerItem = .SlicerItems(UEselected)
        If TargetSlicerItem Is Nothing Then
            Set TargetSlicerItem = .SlicerItems("No Data")
        End If
        On Error GoTo 0
        If Not TargetSlicerItem Is Nothing Then
            Dim oSlicerItem As SlicerItem
            For Each oSlicerItem In .SlicerItems
                If oSlicerItem.Name = TargetSlicerItem.Name Then
                    oSlicerItem.Selected = True
                Else
                    oSlicerItem.Selected = False
                End If
            Next oSlicerItem
        End If
    End With

End Sub

Hope this hleps!

Amazing, thank you so much, this has worked perfectly and has added to my VBA knowledge. Thank you.

 

[Domenic]

You're very welcome, glad I could help, cheers!