VBA: new 1-D array from selected index on 2-D array
Page 2 of 2 FirstFirst 12
Results 11 to 20 of 20

Thread: VBA: new 1-D array from selected index on 2-D array
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,725
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    6 Thread(s)

    Default Re: VBA: new 1-D array from selected index on 2-D array

    Since you appear to be working with jagged arrays, I suggest you use a 1D array of arrays instead of a 2D array in the first place.

  2. #12
    Banned user
    Join Date
    May 2014
    Location
    Germany
    Posts
    1,336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: new 1-D array from selected index on 2-D array

    Quote Originally Posted by RoryA View Post
    Since you appear to be working with jagged arrays, I suggest you use a 1D array of arrays instead of a 2D array in the first place.
    Hi Rory,
    . Thanks for the input.
    . I do not quite follow ( NEI )
    .. I guess you mean getting finally to this point?

    Code:
    Sub Rory1DArrayofArrays()Dim OneDTwoDArr(1 To 3) As Variant
    
    
    OneDTwoDArr(1) = Array("OneOne", "Onetwo", "OneThree")
    OneDTwoDArr(2) = Array("TwoOne", "Twotwo")
    OneDTwoDArr(3) = Array("ThreeOne", "ThreeTwo", "ThreeThree")
    
    
    End Sub
    – From that point The OP has a simple method to get at his rows as per his request in Post #1 with something of the form ( as example for the second row )
    OneDArr=OneDTwoDArr(2)
    .
    . However I do not see a simple way without looping in a complicated Function to create that 1D array of 2D arrays from his original 2 D Array.
    .
    . So I think he is back where he started? Or can You elaborate what You meant , bearing in mind the OP‘s original request? : - ( If understood the OP’s initial request his start point was getting rows in a simple way from an existing 2 Dimensional Array , ( with the added requirement to ignore blanks in the rows ) )
    ….
    Quote Originally Posted by EuroSong View Post
    …. trying to find an easy way of converting a selected index within a 2-dimensional array into a 1-dimensional array. For example, if I have the following:

    TwoDArr(1,1)="OneOne"
    TwoDArr(1,2)="OneTwo"
    TwoDArr(2,1)="TwoOne"
    TwoDArr(2,2)="TwoTwo"

    ... I want to do something like this:
    OneDArr=TwoDArr(2): with the result being OneDArr(1)="TwoOne" and OneDArr(2)="TwoTwo". See what I mean?........Can anyone suggest a better way of doing this than the messy function I am using?
    Alan

  3. #13
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,725
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    6 Thread(s)

    Default Re: VBA: new 1-D array from selected index on 2-D array

    I meant he should skip creating a 2D array to start with, since it doesn't appear to be what he actually wants.

  4. #14
    Banned user
    Join Date
    May 2014
    Location
    Germany
    Posts
    1,336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: new 1-D array from selected index on 2-D array

    Quote Originally Posted by rorya View Post
    i meant he should skip creating a 2d array to start with, since it doesn't appear to be what he actually wants.
    o.k. , Thanks
    Last edited by DocAElstein; Jul 14th, 2015 at 09:16 AM.

  5. #15
    New Member
    Join Date
    Sep 2007
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: new 1-D array from selected index on 2-D array

    Doc Alan - thank you so much for your input. However it appears as complicated (if not more so) than the way I'm already doing things anyway

    Rory - I think you're onto something there! I did not realise it was possible to create a 1D "array of arrays". This is exactly why I came to this forum: new ideas that may previously have been thought as impossible. I will try it. Seems like what I need. Something like this:

    Code:
    Sub ArrayofArrays()
        Dim MasterArray() As Variant
        ReDim MasterArray(1 To 3)
        Dim x As Integer
        For x = 1 To 3
            MasterArray(x) = GrabValues(x)
        Next x
        ' And to test...
        MsgBox UBound(MasterArray(1))
        MsgBox UBound(MasterArray(2))
        MsgBox UBound(MasterArray(3))
    End Sub
    
    Function GrabValues(x As Integer) As Variant
        Select Case x
            Case 1
                GrabValues = Array("OneOne", "Onetwo", "OneThree")
            Case 2
                GrabValues = Array("TwoOne", "Twotwo")
            Case 3
                GrabValues = Array("ThreeOne", "ThreeTwo", "ThreeThree")
        End Select
    End Function
    Still interested in Rick's other methods though

  6. #16
    Banned user
    Join Date
    May 2014
    Location
    Germany
    Posts
    1,336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: new 1-D array from selected index on 2-D array

    Hi EuroSong.
    Quote Originally Posted by EuroSong View Post
    Doc Alan - thank you so much for your input. However it appears as complicated (if not more so) .......
    . your welcome . The extra complication comes from the ability to select columns which I appreciate you do not need. But that helped me to get the other function, which as a “one liner” seemed similar to Rick’s

    ..................
    Quote Originally Posted by EuroSong View Post
    ......Rory - I think you're onto something there! I did not realise it was possible to create a 1D "array of arrays". This is exactly why I came to this forum: new ideas that may previously have been thought as impossible. I will try it......
    . His idea made me think further as well.. Here, for example, what I was thinking if your start point was that you had a 2 Dimensional Array...

    . ....This function would get your one dimensional Array of Arrays from your 2 dimensional Array ( Again ignore the additional Sub included for my later reference on how I got to the function ! )

    Code:
    Function OneDArrayofArrays(Arr As Variant) As Variant
    Dim OneDTwoDArr() As Variant: ReDim OneDTwoDArr(1 To UBound(Arr, 2))
    Dim strtemp As String
    Dim j As Long, i As Long
        For j = 1 To UBound(Arr, 1)
            For i = 1 To UBound(Arr, 2)
            If Arr(j, i) <> "" Then Let strtemp = strtemp & " " & Arr(j, i)
            Next i
        OneDTwoDArr(j) = Split(Trim(strtemp), " ")
        Let strtemp = ""
        Next j
    OneDArrayofArrays = OneDTwoDArr()
    End Function
    '
    '
    '
    '
    '
    '
    '
    '
     
     
    Sub OneDArrayofArraysfromTwoDArray()
    '
    Dim TwoDArr(1 To 3, 1 To 3) As Variant
    TwoDArr(1, 1) = "OneOne"
    TwoDArr(1, 2) = "OneTwo"
    TwoDArr(1, 3) = "OneThree"
    TwoDArr(2, 1) = "TwoOne"
    TwoDArr(2, 2) = "TwoTwo"
    TwoDArr(2, 3) = "" 'Blank to be ignored
    TwoDArr(3, 1) = "ThreeOne"
    TwoDArr(3, 2) = "ThreeTwo"
    TwoDArr(3, 3) = "ThreeThree"
     
     
     
    Dim OneDTwoDArr() As Variant: ReDim OneDTwoDArr(1 To UBound(TwoDArr(), 2)) 'Dimension OneDTwoDArr as 1 Dimension Array of size equal to rows of 2DArray
    Dim strtemp As String 'Temp String for Row contents
    Dim j As Long, i As Long
        For j = 1 To UBound(TwoDArr(), 1) 'For each (Row) 1 D Array element)
            For i = 1 To UBound(TwoDArr(), 2) 'For each column in 2 dimensional Array
            If TwoDArr(j, i) <> "" Then Let strtemp = strtemp & " " & TwoDArr(j, i) 'Build string if not blank entry
            Next i
        OneDTwoDArr(j) = Split(Trim(strtemp), " ") 'Create 1 dimensional Array of Row elements in 2 dimensional Array and place in 1 Dimensional Array of Arrays
        Let strtemp = "" 'Set temp String to null for next (row) loop
        Next j
     
    End Sub
    '
    '
    .......




    And subsequently you obtain ( for example your original second ) Row in subsequent code in this form:

    Code:
    Sub TestFunctionOneDArrayofArrays()
     
    Dim TwoDArr(1 To 3, 1 To 3) As Variant
    TwoDArr(1, 1) = "OneOne"
    TwoDArr(1, 2) = "OneTwo"
    TwoDArr(1, 3) = "OneThree"
    TwoDArr(2, 1) = "TwoOne"
    TwoDArr(2, 2) = "TwoTwo"
    TwoDArr(2, 3) = "" 'Blank to be ignored
    TwoDArr(3, 1) = "ThreeOne"
    TwoDArr(3, 2) = "ThreeTwo"
    TwoDArr(3, 3) = "ThreeThree"
     
    Dim OneDTwoDArr() As Variant
    Let OneDTwoDArr() = OneDArrayofArrays(TwoDArr)
     
    Dim OneDArray As Variant
    'Example for second Row
    Let OneDArray = OneDTwoDArr(2)
     
     
    End Sub
    ...............................

    Quote Originally Posted by EuroSong View Post
    ......
    Still interested in Rick's other methods though
    . me too

    Alan

  7. #17
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,074
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: VBA: new 1-D array from selected index on 2-D array

    Quote Originally Posted by EuroSong View Post
    Still interested in Rick's other methods though
    Quote Originally Posted by DocAElstein View Post
    . me too
    Okay, here you go. If your values are always numeric or they are always text without spaces, or a combination of those two (the key being no spaces in any cells), then you can use this function...

    Code:
    Function OneD(Arr As Variant, Index As Long) As Variant
      OneD = Split(RTrim(Join(Application.Index(Arr, Index, 0))))
    End Function
    If, however, the cells could possibly contain spaces, then you would use this function instead...

    Code:
    Function OneD(Arr As Variant, Index As Long) As Variant
      OneD = Split(Replace(Replace(RTrim(Replace(Replace(Join(Application.Index(Arr, Index, _
             0), Chr(1)), " ", Chr(2)), Chr(1), " ")), " ", Chr(1)), Chr(2), " "), Chr(1))
    End Function
    Last edited by Rick Rothstein; Jul 14th, 2015 at 11:18 AM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  8. #18
    Banned user
    Join Date
    May 2014
    Location
    Germany
    Posts
    1,336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: new 1-D array from selected index on 2-D array

    Quote Originally Posted by Rick Rothstein View Post
    Okay, here you go. .........
    .. Great Rick.
    . - How you keep a clear head constructing those multiple ( nested ) replace functions is amazing..
    . I have worked through , opening up the Functions to understand them. They work great ( obviously ! ).
    . Thanks again
    Alan

    P.s. In case it helps anyone looking in, here are my opened up versions of Rick’s Functions along with a Test code

    Code:
    '
    '
    'http://www.mrexcel.com/forum/excel-questions/867942-visual-basic-applications-new-1-d-array-selected-index-2-d-array-2.html#post4213878
     
     
    Function OneDNoSpace(Arr As Variant, rwIndex As Long) As Variant
      'OneDNoSpace = Split(RTrim(Join(Application.Index(Arr, rwIndex, 0))))
      OneDNoSpace = Application.Index(Arr, rwIndex, 0) 'Slice Row( Includs space )
      OneDNoSpace = Join(OneDNoSpace, " ") 'Make string from rowcontents with space between
      OneDNoSpace = Trim(OneDNoSpace) 'RTrim(OneDNoSpace)'trim off last space
      OneDNoSpace = Split(OneDNoSpace, " ") 'Make 1 dimensional array using split Function
    End Function
    '
    Function OneDSpace(Arr As Variant, rwIndex As Long) As Variant
    'OneDSpace = Split(Replace(Replace(RTrim(Replace(Replace(Join(Application.Index(Arr, rwIndex, _
             0), Chr(1)), " ", Chr(2)), Chr(1), " ")), " ", Chr(1)), Chr(2), " "), Chr(1))
    OneDSpace = Application.Index(Arr, rwIndex, 0) 'Make string from rowcontents with space between
    OneDSpace = Join(OneDSpace, Chr(1)) 'Appears to join with two spaces ( Tab? )
    OneDSpace = Replace(OneDSpace, " ", Chr(2)) 'replaces a space with something odd
    OneDSpace = Replace(OneDSpace, Chr(1), " ") 'Replaces the ( Tab? ) with one space
    OneDSpace = Trim(OneDSpace) 'RTrim(OneDSpace)'Take off last space
    OneDSpace = Replace(OneDSpace, " ", Chr(1)) 'Replaces the space with a ( Tab? )
    OneDSpace = Replace(OneDSpace, Chr(2), " ") 'Replaces the odd thing with a space
    OneDSpace = Split(OneDSpace, Chr(1)) 'Make 1 dimensional array using split Function
    End Function
    '
    Sub TestRick2()
    Dim TwoDArr(1 To 3, 1 To 3) As Variant
    TwoDArr(1, 1) = "OneOne"
    TwoDArr(1, 2) = "OneTwo"
    TwoDArr(1, 3) = "OneThree"
    TwoDArr(2, 1) = "TwoOne"
    TwoDArr(2, 2) = "TwoTwo"
    TwoDArr(2, 3) = "" 'Blank to be ignored
    TwoDArr(3, 1) = "ThreeOne"
    TwoDArr(3, 2) = "ThreeTwo"
    TwoDArr(3, 3) = "ThreeThree"
    'Test OneDNoSpace
    Dim OneDArr As Variant
    Let OneDArr = OneDNoSpace(TwoDArr, 2)
     
    'Test OneDSpace
    TwoDArr(2, 2) = "Two Two" 'Case wanted space
    Dim OneDArr2 As Variant
    Let OneDArr2 = OneDSpace(TwoDArr, 2)
    End Sub

  9. #19
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,074
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: VBA: new 1-D array from selected index on 2-D array

    Quote Originally Posted by DocAElstein View Post
    .. Great Rick.
    . - How you keep a clear head constructing those multiple ( nested ) replace functions is amazing..
    It is actually not that hard... you do not write it from left-to-right, rather, you construct the code line from the inside out, each intermediate constructed result being the argument for the next function (which you place around it). It involves a lot of jumping from front to back as you place each new function around the intermediate one you just constructed, but it is much easier to keep the steps and syntax straight when you do it that way.


    Quote Originally Posted by DocAElstein View Post
    . I have worked through , opening up the Functions to understand them. They work great ( obviously ! ).

    OneDSpace = Join(OneDSpace, Chr(1)) 'Appears to join with two spaces ( Tab? )
    No, it is not a tab nor two spaces. Chr(1) is a single character whose ASCII code is 1 and, likewise, Chr(2) is a single character whose ASCII code is 2... I don't know what those characters are, but I do know that they will not appear in the text being processed, so I cannot inadvertently end up replacing part of the real text while I am manipulating the text at each step. If I knew for a fact that, say, and asterisk (*) and an at-sign (@) would never appear in the text, I could replace each Chr(1) with "*" and each Chr(2) with "@" and the code would work the same, but since I have no idea what characters could make up the text, I chose to use characters I am nearly 100% positive the person creating the text would not know how to include within that text. As for why Chr(1) appears as multiple characters... it is just the way VB interprets when it tries to print it.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  10. #20
    Banned user
    Join Date
    May 2014
    Location
    Germany
    Posts
    1,336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: new 1-D array from selected index on 2-D array

    Quote Originally Posted by Rick Rothstein View Post
    ..you do not write it from left-to-right, rather, you construct code line from inside out, each intermediate constructed result being the argument for the next function (which you place around it). It involves a lot of jumping from front to back as you place each new function around the intermediate one you just constructed, but it is much easier to keep the steps and syntax straight when you do it that way....
    ..

    .... Thanks, I have done a similar sort of thing sometimes for much simpler examples, such as in the extra codes I presented here in the code windows containing my Functions. And I sort of “open up” your Functions working in reverse to this method.. but I still lack the experience to come up with the complicated Replace(Replace(Replace things that I have noticed is one of your specialities. Thanks for the valuable insight into your way of thinking.


    Quote Originally Posted by Rick Rothstein View Post
    .....a tab nor two spaces. Chr(1) is a single character whose ASCII code is 1 and, likewise, Chr(2) is a single character whose ASCII code is 2... I don't know what those characters are, but I do know that they will not appear in the text being processed, .....since I have no idea what characters could make up the text, I chose to use characters I am nearly 100% positive the person creating the text would not know how to include within that text. As for why Chr(1) appears as multiple characters... it is just the way VB interprets when it tries to print it.
    .... Thanks that clears that up nicely – by googling I saw they had no text value, so with your explanation it all makes sense now.

    ................
    . Thanks for coming back to the Thread again and sharing that extra info.

    Alan Elston

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
  •