[VBA] Adding string to each element in an Array
Results 1 to 4 of 4

Thread: [VBA] Adding string to each element in an Array
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question [VBA] Adding string to each element in an Array

    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("[Property].[Property].[All].[Element1]", "[Property].[Property].[All].[Element2]", ...)

    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.

  2. #2
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,666
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    2 Thread(s)

    Default Re: [VBA] Adding string to each element in an Array

    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
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  3. #3
    New Member
    Join Date
    Aug 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: [VBA] Adding string to each element in an Array

    Quote Originally Posted by pgc01 View Post
    Hi
    Welcome to the board

    See if this helps:
    Hi, thank you so much, it works like a dream!

  4. #4
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,666
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    2 Thread(s)

    Default Re: [VBA] Adding string to each element in an Array

    You're welcome. Thanks for the feedback.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •