[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

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,790
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,106,306
Messages
5,510,507
Members
408,792
Latest member
S_s_s

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top