[VBA] Adding string to each element in an Array

[sanfrandear]

Hi everyone,

I'm new to VBA so I apologise if the terms I'm using are incorrect. I'm trying to filter an OLAP PivotField based on the ranges created in second worksheet. The number of filters to perform depend on the number of columns in that worksheet, and only the items in that column are made visible. Once this is done, it saves the chart that is linked to the pivot table and jumps onto the next column for filtering.

For i = 1 To lastcolumnlastrow = arrayws.Cells(Rows.Count, i).End(xlUp).Row
Set aggrange = arrayws.Range(Cells(2, i), Cells(lastrow, i))
myarray = Application.Transpose(aggrange.Value)


    With pf
        .ClearAllFilters
        .VisibleItemsList = myarray
    End With


Next i

I've managed to get the code above to work because I manually added the syntax in bold into the columns. But I only want the elements to be visible to the end user so that they don't have to bother with the syntax.

pt.PivotFields("[Property].[Property].[Property]").visibleitemslist = Array("[B][Property].[Property].[All].[[/B]Element1[B]][/B]", "[B][Property].[Property].[All].[[/B]Element2[B]][/B]", ...)

I've figured that I could save them into strings and somehow join them into the front and back of each element in the array in the code. But I can't figure it out or find any similar code on the internet.

str1 = "[Property].[Property].[All].["
str2 = "]"

I hope this isn't confusing and thank you in advance for the help.

 

[pgc01]

Hi
Welcome to the board

See if this helps:

Sub Test()
Dim v As Variant
Dim str1 As String, str2 As String

str1 = "[Property].[Property].[All].["
str2 = "]"

' test array
v = Array("a", "b", "c")

v = Join(v, "|")
v = str1 & Replace(v, "|", str2 & "|" & str1) & str2

' confirm the new elements in the array just for the test
MsgBox v

' rebuild the array
v = Split(v, "|")

' ... use the array


End Sub

 

[sanfrandear]

Hi
Welcome to the board

See if this helps:

Hi, thank you so much, it works like a dream!

 

[pgc01]

You're welcome. Thanks for the feedback.