Writing the values from slicer to range

[Guinaba]

Hi experts,

I am using the function below to get the selected values from a pivot slicer into an array, which is perfectly ok, however I'd like to get the values from the array back to a range. I am trying the code below but without success. Any suggestion?

Sub TestExample()
    Dim MyArr() As Variant
    MyArr = ArrayListOfSelectedAndVisibleSlicerItems("Slicer_Product_Group")
    'now variable MyArr keeps all items in an array
    'Declare the integer to store the number of rows
    
   Dim iRw As Integer
   'loop through the values in the array
   For iRw = LBound(MyArr) To UBound(MyArr)
   
   'populate a different range with the data
      Cells(iRw, 22).Value = MyArr(iRw, 1)
   Next iRw
    
    
End Sub

Public Function ArrayListOfSelectedAndVisibleSlicerItems(Slicer_Product_Group As String) As Variant
    'This function returns an array of the limited set of items in Slicer A
    'Limitation is due to both:
    '(1) direct selection of items by user in slicer A
    '(2) selection of items in slicer B which in consequence limits the number of items in slicer A

    Dim ShortList() As Variant
    Dim i As Integer: i = 0 'for iterate

    Dim sC As SlicerCache
    Dim sI As SlicerItem 'for iterate

    Set sC = ThisWorkbook.Application.ActiveWorkbook.SlicerCaches(Slicer_Product_Group)
    
    For Each sI In sC.SlicerItems
        If sI.Selected = True And sI.HasData = True Then 'Here is the condition!!!
'            Debug.Print sI.Name
            ReDim Preserve ShortList(i)
            ShortList(i) = sI.Name
            i = i + 1
        End If
    Next sI
    ArrayListOfSelectedAndVisibleSlicerItems = ShortList
End Function

 

[Domenic]

Since your array is a zero-based, one-dimensional array, try...

   Dim iRw As Integer
   'loop through the values in the array
   For iRw = LBound(MyArr) To UBound(MyArr)
  
   'populate a different range with the data
      Cells(iRw + 1, 22).Value = MyArr(iRw)
   Next iRw

By the way, it can be re-written as follows...

    Cells(1, 22).Resize(UBound(MyArr) + 1).Value = Application.Transpose(MyArr)

Hope this helps!

 

[Guinaba]

Since your array is a zero-based, one-dimensional array, try...

   Dim iRw As Integer
   'loop through the values in the array
   For iRw = LBound(MyArr) To UBound(MyArr)
 
   'populate a different range with the data
      Cells(iRw + 1, 22).Value = MyArr(iRw)
   Next iRw

By the way, it can be re-written as follows...

    Cells(1, 22).Resize(UBound(MyArr) + 1).Value = Application.Transpose(MyArr)

Hope this helps!

Thanks @Domenic!! It works perfectly!