+ Reply to Thread
Results 1 to 27 of 27

Copy 1 dim array to and 2 dim array

  1. #1
    Registered User
    Join Date
    10-29-2013
    Location
    Sweden
    MS-Off Ver
    Excel 2010
    Posts
    17

    Copy 1 dim array to and 2 dim array

    Hello,

    How do I copy an 1 dim array into and 2 dim array without using looping?

    /P

  2. #2
    Valued Forum Contributor vba_php's Avatar
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    1,671

    Re: Copy 1 dim array to and 2 dim array

    the only way I know of in VBA is to use the getRows() method, but that is an ms access function. that throws 2-dimensional data from a table into a 2-dimensional array. I'm not aware of any other method in basic that does the same thing. although, this is possible:

    https://www.google.com/search?q=vba+...cel+equivalent

    or this, which may be even better:

    https://www.google.com/search?q=vba+...+2+dimensional
    -Adam (please don't call me ""sir"" or ""vba_php"")!

  3. #3
    Registered User
    Join Date
    10-29-2013
    Location
    Sweden
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Copy 1 dim array to and 2 dim array

    Thanks for the answer!

    Dim Array1(2, 2) As Integer
    Dim Array2(2) As Integer

    So, if you got two array's like this there is no way to copy the values from Array2 into Array1?

    /P
    Last edited by Per_; 09-30-2020 at 02:24 AM.

  4. #4
    Valued Forum Contributor vba_php's Avatar
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    1,671

    Re: Copy 1 dim array to and 2 dim array

    Your question that you just asked Is a duplicate of what you asked originally. I've already given an answer to that in my response to you.yes you can do it and if you check out the links that I posted more than likely there's some sort of an answer there but if those links don't provide any answer the bottom line is that you're going to have to write loops to do it to throw element by element from your one-dimensional to your two-dimensional. You don't have a choice

  5. #5
    Registered User
    Join Date
    10-29-2013
    Location
    Sweden
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Copy 1 dim array to and 2 dim array

    Okay. I have checked those links and it gives no answer if it is possible to do it without looping. So, I suppose it is not possible.

  6. #6
    Valued Forum Contributor vba_php's Avatar
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    1,671

    Re: Copy 1 dim array to and 2 dim array

    That would be my guess and to be honest with you I've never seen your type of question asked in 20 years of writing code my lifetime. And I've written visual basic 1 2 3 4 5 6 and VBA for more than 30 years. And as a matter of fact I don't even think this is possible in any web-based language that I even know either including PHP c sharp anything and even javascript.

  7. #7
    Registered User
    Join Date
    10-29-2013
    Location
    Sweden
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Copy 1 dim array to and 2 dim array

    Okay. Sorry to ask the question.

  8. #8
    Valued Forum Contributor vba_php's Avatar
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    1,671

    Re: Copy 1 dim array to and 2 dim array

    You don't have to be sorry for asking the question. I wouldn't worry about that period there's no such thing as a dumb question. I was simply trying to tell you that it's a very unique question it doesn't mean that it's wrong and it doesn't mean that there's no answer. and if you want to do a little bit of research on how close you can get to an answer, take a look at the website called: www.php.net

    That's the official documentation for the PHP web language. If you look up the concept of arrays there are probably 10 or more different built-in functions that are related to that concept and you might learn from that page what more sophisticated languages are capable of doing with data inside of arrays, one-dimensional and two-dimensional.

  9. #9
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    UK
    MS-Off Ver
    2016 primarily
    Posts
    4,988

    Re: Copy 1 dim array to and 2 dim array

    Quote Originally Posted by Per_ View Post
    Thanks for the answer!

    Dim Array1(2, 2) As Integer
    Dim Array2(2) As Integer

    So, if you got two array's like this there is no way to copy the values from Array2 into Array1?

    /P
    Can you give an example of what the start and end arrays are supposed to look like?

    Also, what's the problem with looping?
    Rory
    I drink, and I know things

  10. #10
    Valued Forum Contributor vba_php's Avatar
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    1,671

    Re: Copy 1 dim array to and 2 dim array

    Hey rorya, is there any way that visual basic for applications has the ability to do work like this by putting mathematical resources to work like say for instance bitwise operations, which probably aren't relevant here, or anything to do with like a matrix oriented layout in mathematics like an academia for instance matrices? I don't know what your specialty is or how many years of experience you've got doing this type of work, but based on all of your posts I thought I would ask you this because I have no idea just curious.

  11. #11
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    UK
    MS-Off Ver
    2016 primarily
    Posts
    4,988

    Re: Copy 1 dim array to and 2 dim array

    Quote Originally Posted by vba_php View Post
    the only way I know of in VBA is to use the getRows() method, but that is an ms access function.
    Just FYI, that is not an Access function. It's a method of the DAO or ADO Recordset object, so applies to any supported back end database.

  12. #12
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    UK
    MS-Off Ver
    2016 primarily
    Posts
    4,988

    Re: Copy 1 dim array to and 2 dim array

    Pure VBA doesn't have that much in the way of mathematical functions, but if you're in Excel, you can leverage anything available there. Bitwise ops are also fairly limited (And, Or for example).

  13. #13
    Valued Forum Contributor vba_php's Avatar
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    1,671

    Re: Copy 1 dim array to and 2 dim array

    Quote Originally Posted by rorya View Post
    Just FYI, that is not an Access function. It's a method of the DAO or ADO Recordset object, so applies to any supported back end database.
    LOL. Yes I'm aware of that. thank you. but yes, you got me in my error or not being specific. what a rare case! FYI to you as well, never in my 20 years have I ever written this in access:
    Please Login or Register  to view this content.
    this has always worked:
    Please Login or Register  to view this content.

  14. #14
    Valued Forum Contributor vba_php's Avatar
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    1,671

    Re: Copy 1 dim array to and 2 dim array

    Quote Originally Posted by rorya View Post
    Bitwise ops are also fairly limited (And, Or for example).
    you ever written custom algorithms that use BW?

  15. #15
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    UK
    MS-Off Ver
    2016 primarily
    Posts
    4,988

    Re: Copy 1 dim array to and 2 dim array

    Quote Originally Posted by vba_php View Post
    FYI to you as well, never in my 20 years have I ever written this in access:
    Please Login or Register  to view this content.
    this has always worked:
    Please Login or Register  to view this content.
    I find that unlikely. GetRows is a method of a Recordset object, as I said, so you'd need that object qualifier, not just getrows.

  16. #16
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    UK
    MS-Off Ver
    2016 primarily
    Posts
    4,988

    Re: Copy 1 dim array to and 2 dim array

    Quote Originally Posted by vba_php View Post
    you ever written custom algorithms that use BW?
    I've written code using bitwise ops, certainly.

  17. #17
    Valued Forum Contributor vba_php's Avatar
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    1,671

    Re: Copy 1 dim array to and 2 dim array

    I might be wrong about getrows(). it's been so long since I've written office code, so yeah good call. oh, ok, so now I realize my error. I have never written:
    Please Login or Register  to view this content.
    i've always written, to be EXACT, per memory:
    Please Login or Register  to view this content.
    so take it easy on me, will ya? I'm not perfect.

    regarding bitwise ops, I figured you had if you're a professional in this field. Whereas I am not. But my question to you was about writing algorithmic (mathematical security-based stuff, for instance) code using BW. simply writing BW in a code block does not mean it's algorithmic logic. IMO.

    perhaps we should stop this back and forth. we're starting to hijack this thread....

  18. #18
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    UK
    MS-Off Ver
    2016 primarily
    Posts
    4,988

    Re: Copy 1 dim array to and 2 dim array

    I'm not a professional Excel developer if that's what you mean. But agreed, this is all very OT, so apologies to the OP.

  19. #19
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010
    Posts
    1,577

    Re: Copy 1 dim array to and 2 dim array

    If you have a one-dimensional VBA array, you can convert it to a two-dimensional array by transposing it...
    Please Login or Register  to view this content.
    TwoDimensionalArray(1, 1) ==> 1
    TwoDimensionalArray(2, 1) ==> 2
    TwoDimensionalArray(3, 1) ==> 3
    TwoDimensionalArray(4, 1) ==> 4
    TwoDimensionalArray(5, 1) ==> 5
    Last edited by Rick Rothstein; 09-30-2020 at 04:39 AM.

  20. #20
    Valued Forum Contributor vba_php's Avatar
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    1,671

    Re: Copy 1 dim array to and 2 dim array

    Quote Originally Posted by Rick Rothstein View Post
    If you have a one-dimensional VBA array, you can convert it to a two-dimensional array by transposing it...
    Please Login or Register  to view this content.
    TwoDimensionalArray(1, 1) ==> 1
    TwoDimensionalArray(2, 1) ==> 2
    TwoDimensionalArray(3, 1) ==> 3
    TwoDimensionalArray(4, 1) ==> 4
    TwoDimensionalArray(5, 1) ==> 5
    Rick,

    nice. however, this person obviously has the data already in the 1-dim array var. so....writing literals in a split() function would not be an equivalent. does your process also work if a var is put into split()? this KB by MS doesn't make it clear that it would work: https://docs.microsoft.com/en-us/off...split-function

    it is assumed not.

  21. #21
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010
    Posts
    1,577

    Re: Copy 1 dim array to and 2 dim array

    That first line of code was for example purposes only... it does not matter how the one-dimensional array is created, all that matters is the variable contains one. It was the second line of code that is one to focus on... it takes any one-dimensional array and converts it to a two-dimensional array. The resulting two-dimensional array will always be one-based (indexes always start at 1 even if the one-dimensional array doesn't).

  22. #22
    Valued Forum Contributor vba_php's Avatar
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    1,671

    Re: Copy 1 dim array to and 2 dim array

    thanks. makes perfect sense. I think we should stop now, as the question asker here is probably wondering what is going on! we're off topic. I've never been that great with 2-dimensionals, as I have had very little use for them. the only way I can understand what goes on inside of them is to print out the internals using a debugger in any given IDE. that helps me a great deal.

  23. #23
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,160

    Re: Copy 1 dim array to and 2 dim array

    Quote Originally Posted by Per_ View Post
    Hello,

    How do I copy an 1 dim array into and 2 dim array without using looping?

    /P
    After speed reading all the intermediate posts wouldn't one solution be to write the 1 dim array into a simple list range in Excel, say A1:Ann.
    The read the range A1:Bnn into the VBA 2 dim array?
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  24. #24
    Valued Forum Contributor vba_php's Avatar
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    1,671

    Re: Copy 1 dim array to and 2 dim array

    Quote Originally Posted by Richard Buttrey View Post
    After speed reading all the intermediate posts wouldn't one solution be to write the 1 dim array into a simple list range in Excel, say A1:Ann.
    The read the range A1:Bnn into the VBA 2 dim array?
    that's pretty much the same thing as looping. not much of a time saver.

  25. #25
    Registered User
    Join Date
    10-05-2019
    Location
    East Coast, USA
    MS-Off Ver
    Mostly 2010 ( Windows 10 & 365 Insider Fast )
    Posts
    25

    Re: Copy 1 dim array to and 2 dim array

    I am not totally sure what the OP is asking.
    Is the OP asking
    (i) _ to put values into an existing array where that existing array already has values in it
    or
    (ii)_ changing the array dimension and positioning of elements in an array
    or
    (iii)_ maybe its lost in the translation and/ or the OP is not sure him/herself.
    The initial answer to (i)_ I think we seem clear about:- It will likely in VBA require a code line for each element to be “moved” from one array to the other , so likely looping will be involved for a multi element array.
    The Thread title and OPs first question infers to me converting a 1 D array to a 2 D array, without looping.
    If the existing array with values already in it is a dynamic array, then overwriting along with re dimensioning means that those (i)_ and (ii)_ are somewhat merged in meaning anyway.
    So I am not totally clear what is going on here, but I think it there is a discussion of generally … …”1 D arrays to 2 D arrays
    So lets say we are talking generally about …”1 D arrays to 2 D arrays” and leave it loosely defined for now and go with that…

    Frederick has shown in his second code line that a characteristic of the Transpose function is that if a 1 D array is given to the Transpose function then the transposed array becomes a 2 D array , all be it a quasi “1 column array” ***
    Transpose does that, as it does the opposite way converting a single column 2D array to a 1D array.
    I think most of us are not quite sure why it has been wired to do that. Some other things seem to default to making a “one row” thing be a 1D array rather than a 2D array, even when the thing it may have been given to work on was a 2D array. ( It does not screw things up to badly when playing with spreadsheets since that transposed in its final 1 D form will be “seen” by Excel as if it was a single row 2 Dimensional array when applied to a spreadsheet range. So usually a “row” becomes a row, if you catch my drift).
    We can go the other way. ( If we do that with Rick’s example , we will see a small difference, the 1 D array returned will have indices of 1 2 3 4 5 as opposed to the 0 1 2 3 4 , (since the Split function Rick used returns those starting a base 0 ) . I am not sure why Excel chooses to start a t 1 in this case: Possibly it was just made that way because its more often to do with worksheet/spreadsheet stuff, and we think about rows and columns starting at 1, and something like a row of 1 is a bit stupid. )

    Index with arrays as co ordinate arguments
    This stuff is worth knowing about:
    A further function that can be very helpful in doing this sort of manipulation of arrays without looping is the Index Function. It becomes so useful because it will accept arrays in place of the more conventional single value indices in its second ( row ) and third ( column ) arguments. The evaluation is then done in the conventional Excel way, “along the columns of a row” , then down to repeat at the next row: along the columns of that row, then down to repeat at the next row: along the columns of that row, then down to repeat at the next row: along the columns of that row , ….etc. Usually VBA will do its best to give out the results in an array dimensioned appropriate for the array dimensions supplied in those second and third arguments, following the conventional “along the columns of a row” , then down to repeat at the next row: along the columns of that row, ………

    As example we can do that Transpose code line in this pseudo way
    Please Login or Register  to view this content.
    We are doing 5 calculations there, talking each time the first row and consecutive columns, the result coming out in a form that the Excel calculations are done - .. “along the columns of a row” , then down to repeat at the next row… but we only have one column in this case, so that is actually just going down the rows, 5 times. Hence our output is the 90degree transpose of OneDimensionalArray()

    That was just one example, but the important point is that you can supply different arrays in the Index second ( “row” ) and third ( “column” ) arguments. So you can pretty well take any1 or 2 D array in the Index first argument, and in one code line, without looping , put all or some of the values from that array in some other order in any other 1 or 2 D array. That could be what the OP was asking for ….
    Dim Array1(2, 2) As Integer
    Dim Array2(2) As Integer
    …………… way to copy the values from Array2 into Array1?

    The restriction is that we can’t make use of this to put values into Array1( ) if it already existed. You would have to be in like having
    Dim Array1() As Variant
    Dim Array2(2) As Integer
    -……..
    Array1()= Index ( Array2(2) , { _.... } , { _... } )

    ( Variant is needed in the first declaration as the index chucks its output values housed in Variant types. AFAIK the first argument can be any sort of 1 D or 2 D array, ( or it can be any range object ) )

    Another not looping option to assist in a conversion could be to remove rows or columns of a 2 D array with a single code line. Best look at some posts of Rick ( Frederick Rothstein ‘s ) , stuff for that ( https://excelfox.com/forum/showthrea...-Variant-Array )


    One last curiosity , a weird thing I only recently came across. An array of arrays, sometimes refereed as a “jagged array”, is peculiarly treated in some cases by Index as a 2 D array. This gives us some interesting further one liner code line possibilities.
    Example, If I had a 1 D array of 1 D arrays, something of this sort of form
    { { “Head1” , 2, 3 } , {“Head3”, 4, 5 } , {“Haed2”, 7, 9} }
    then I can convert that, for example, to re ordered in data columns like this
    Please Login or Register  to view this content.
    I can do that using like a Index one code liner pseudo
    Please Login or Register  to view this content.


    I put some more details of all I have been saying , in a macro in the uploaded file. Probably its best to step through the macro in Debug mode ( do that by hitting Key F8 after clicking anywhere in the macro )





    Hello Adam.
    I expect you are referring specifically to the idea of putting existing values from an array into another existing array, although I am not fully clear if the OP wanted that: Possibly the language barrier prevented the OP getting anything out of the links you gave him…. The best thing probably, as Rory asked for, was an example from the OP of what he wanted to do…
    Anyway, you probably know all the following, but I thought I’d add it to the Thread, while I am in the mood…
    Generally questions along the lines of “1 D array to 2 D array” or visa versa are quite common in Excel VBA. I expect this is because
    _ a) a lot of things done “internally” in coding involve 1 D arrays,
    but/ and
    _ b) a range from a spreadsheet will often likely end up in an array of 2 Dimensions, I think Excel does this so that we can make the distinction what is a row and what is a column.***
    So things might not always work as we wanted, for example a problem might occur when a 1 D array appears when a 2 D array was expected/ wanted, and visa versa. To solve the problem a conversion from a 1D to 2D or visa versa might get us out of trouble.
    Example: we got a Join function that is something like the reverse of the Split function mentioned in this Thread . Basically you can use it to join the contents of an array into a string. The bummer is that it only accepts a 1 D array. So if I give it a column or row of data to Join it will error. You’ll need to change the 2D array got from a spreadsheet single row or a spreadsheet single column to a 1D array for join to work on it. ( One way you can do that is with some of the one liner codings I been talking about – I added a example for you in the uploaded macro ### )

    ***I suppose a 2 D array does not really have “rows” and “columns”, it simply has 2 dimensions. But Excel conventionally puts a spreadsheet row into the fist dimension, and a spreadsheet column into the second dimension. So after using Excel VBA arrays a lot you often get to think of a 2 D array in terms of like arr(row, column) or in terms of orientation like arr(horizontal, vertical). Its just a convenient frame of reference perception.
    A 1 D array has no orientation. I can’t really perceive that unless I have drunk a lot of Jack Daniels, as the world starts spinning around, then it becomes very clear, relatively speaking. I suppose Excel can’t get drunk, and as mentioned, a 1 D array seems to be often regarded as like a 2 D array of first dimension size of 1, or pseudo 1 “row” 2 D array.


    Molly

    Ref
    https://www.excelforum.com/excel-new...ml#post4571172
    https://www.excelforum.com/tips-and-...ml#post5408376
    http://www.eileenslounge.com/viewtop...271035#p271035
    https://www.ozgrid.com/forum/index.p...41#post1239241 , https://eileenslounge.com/viewtopic....d06fc31#p27436
    https://eileenslounge.com/viewtopic....265384#p265384
    Attached Files Attached Files
    Last edited by MollyBrennholz; 10-18-2020 at 03:30 AM.
    .

  26. #26
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010
    Posts
    1,577

    Re: Copy 1 dim array to and 2 dim array

    Quote Originally Posted by MollyBrennholz View Post
    Frederick has shown in his second code line...

    Best look at some posts of Rick ( Frederick Rothstein ‘s ), stuff for that...
    You posted a lot of good stuff there Molly, hopefully readers will not be put off by the size of it. However, I have a question for you. Why are you referring to me as Frederick? Yes, Frederick is my give name, but I rarely if ever use it... I go by Rick pretty much everywhere. I am just wondering where you saw that I use Frederick when referring to myself?

  27. #27
    Valued Forum Contributor vba_php's Avatar
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    1,671

    Re: Copy 1 dim array to and 2 dim array

    speaking about being put off. I don't really have the time to read all of it. LOL. but I might get back to it.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] VBA copy value from array to another array - Type mismatch error
    By gargantuLars in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-30-2019, 01:13 PM
  2. Replies: 5
    Last Post: 02-22-2018, 01:47 AM
  3. If Condition is True, Save Data in Array, and Copy Array Data to other Worksheet
    By skelly8117 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-10-2013, 10:18 AM
  4. [SOLVED] Quick Array question - Copy array to another array then resize?
    By mc84excel in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 05-02-2013, 01:17 AM
  5. Array - Copy Formula, Paste as Array
    By lilanngel in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-08-2010, 08:20 AM
  6. select variables ranges, copy to array, paste the array in new workbook
    By Mathew in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-01-2005, 05:06 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1