[VBA] Adding string to each element in an Array

sanfrandear

New Member
Joined
Aug 20, 2019
Messages
4
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.

Code:
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.

Code:
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.
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,781
Hi
Welcome to the board

See if this helps:

Code:
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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,851
Messages
5,507,703
Members
408,645
Latest member
AndreG06

This Week's Hot Topics

Top