Page 3 of 3 FirstFirst 123
Results 21 to 29 of 29

Thread: VBA - copy 2-dimensional array into 1-dimensional (single column)
Thanks Thanks: 0 Likes Likes: 0

  1. #21
    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 - copy 2-dimensional array into 1-dimensional (single column)

    Hi
    Thanks for the feedback 
    Quote Originally Posted by astrbac View Post
    ......
    No problem, I know how it feels when you go through the effort of literally taking someone by the hand and leading them only to have them not even say thanks, let alone write some decent feedback.
    ...
    _ i try to learn as well when i answer threads. – That made my fist code a bit over the Top!! Sorry.. But i think the Thread is developing in a nice one that we ( and others ) can both come back to for later reference. ( I find the Threads a good place to store my Codes, thoughts, theories etc. – better sometimes than my own chaotic storage system, ( or a least a good back up for them!!. ) ) . I try not to post too often, -quality rather than quantity. - Always do a good Google first, then post when the problem does not seem to have been solved anywhere yet, ( which is not often !! ). I think this Forum is great, but a minor criticism is the number of similar short Threads all answering the same problem.. Clutters things a bit. - Or as a great solution is not well explained so someone cannot understand enough to adapt to his requirement. I try to help offset that a bit by helping to clarify the great solution the Experts sometimes give me....

    But all god learning “Fun” !! ( .....Still struggling on pgc’s last post!!.. but getting there... maybe. )

    Alan

  2. #22
    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 - copy 2-dimensional array into 1-dimensional (single column)

    @ pgc
    Quote Originally Posted by pgc01 View Post
    ......
    I see you have really been reflecting on this theme.
    I don't think this type of solution is very practical, ...... but I also think that it is good to understand the syntax of the functions we use, and in that spirit .........
    ......
    Hi pgc
    _ thanks for coming back again.


    ::::....
    _ 1 ) My ramblings!!
    I confess i go a bit over the top trying to understand thing. For one thing i like to understand things even if i may not use them. But also it was the general ”................ use of Application.Index with Look Up Rows and Columns Arguments as VBAArrays. …
    ______...... arrOut() = Application.Index(arrIn(), rws(), clms())…
    ...“_
    ___________......bit i was trying to understand. It has been troubling me for some time as i have used it a lot in answering threads very effectively and am hoping to use it to get easily and quickly values out of a very large Spreadsheet for a personal project. It is the only code i use that i do not ( or did not ?? ** ) understand. But my explanation and Threads inevitably get a bit long and puts anyone off reading. ** Only If you did have a chance, if could you comment on my general Theory I would be very grateful. I mean does my__ “lines shooting out and picking out the Elements intercepted, along with “offsets used in the computer memory “ Theory__ make some sense.
    There are good sites that confirm my experiments and explain what happens, here is the best i know:
    VBA for smarties: Arrays
    But no one seems to know “how” the Index is working here...or cares..That makes me nervous in using it should something goes wrong later... If for example, it is just a coincidence that it works, a change could render it useless, if for example some change was made to the type of arguments accepted, preventing the acceptance of one or more of the rws() or clms() arguments. That is one possible explanation why ____Application.WorksheetFunction.Index___ does not work ( Although Rory suggested it could be due to a similar reason why Arrays are often the only things which cannot be used in many VBA things – that reason being the complex “offset” process used to reference the Elements of an Array.. that is what lead to part of my__ “lines shooting out and picking out the Elements intercepted, along with offsets used in the computer memory “__ Theory, ( as well as my ideas here:
    Trouble writing huge array into worksheet range [SOLVED]
    Where I was saying that this complex offset referencing could not be “supported” or “taken in” at the various places a __Application.WorksheetFunction. ___ reference stopped off at compared with the places stopped off at by the more direct ___Application. ___).....

    I have already seen some published ideas with the .Index of things that presumably did work , but do not appear to be now. Some change maybe in how the Array was held in Memory chucked a “Spanner in the works..” ..here for example, some of the later replies are suggesting something went wrong....and no one noticed
    https://usefulgyaan.wordpress.com/20...ication-index/


    _ ......................................................


    -2) Your new “Blog” !!
    I always try to include all arguments. I missed or did not understand about the Index 4th Argument. Have not found a “google on that” This looks very interesting! Thanks.
    Questions , when / If you have the time

    _2 a ) I was stumbling a bit at the outset in understanding it. I have never used named ranges. I am / was confused with
    Quote Originally Posted by pgc01 View Post
    ........
    For my test I used the range with the areas B2:E3, D6:G7 and A11:D12. I defined the name rAreas as the name of this range.
    .... ..
    Is this equivalent?


    Code:
    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("pgcArrays")
    Dim rng_rAreas As Range
    Set rng_rAreas = Union(ws.Range("B2:E3"), ws.Range("D6:G7"), ws.Range("A11:D12"))
    ( It does appear to “work” )

    _ 2b) Still have not Googled anything on this 4 argument thing. ... But i get it.. I think ?

    __ This __=mod(row(1:12)-1,2)+1__ does “not work” in one cell in a spreadsheet or in the _Evaluate(“___”)_ to get an Array. ( or rather It just returns a long number equal to the first Value in a cell ) The__ If(row(_____ stuff makes it “work” . This far I am already. ( a lot thanks to stuff from you, Rory, etc, in other threads!!). Also i have learned the highlighting a 1row x 12column Range in a Spreadsheet and doing the C S E Stuff makes it work in a Spreadsheet
    _ Note in passing that if i highlight more than 12 rows, the extra ones get an error in it.

    __ Now what is new to me ... and very interesting. If i do the C S E over 12 rows but extended columns it “repeats” the column indicia...

    and your
    ______if(row(1:12)*column(A:B),______) is doing the same!!!! ( for two columns in this case )

    ____Correct? ( that is to say are you doing something similar to the C S E tning here, as far as Excel is concerned i mean.. if that makes sense ? )

    __2 c) You are “Doing it in transpose” again... all arguments are Transposed... OK, no problem i get that. Let me define if I may “Thick” as
    _(i) the “row” “depth” when you don’t transpose,
    _(ii) the “column” “width” when you do transpose.

    _ How “thick” the Output returned from .Index is determined by your new bit... ( for your example___ *column(A:B) __ )..... in the _____ If(row(_____ stuff
    ____Correct?

    _ ( I wonder if this is a “trick” that is to say a result of my “ __ “lines shooting out and picking out the Elements intercepted, along with “offsets used in the computer memory “ Theory__ “ and was never planned or documented ? ) The logic ties up nicely with my “Theories”


    __2 d) If 2 c) is correct.....then... For the Four argument index to work, the first Argument must be a union ( or a named range) which refers to multiple Ranges, and the 4th Argument meerly says which Range is being referred to (, counting in order of that given in the Union line,
    Psuedo: ( __Application.Union(___pos 1____,____pos2___,____pos3____)___) __ ) - ( or for a named range presumably how one adds it in some list ? )
    Correct?

    _3) Application.Union
    _ 3 a) It seems the Range Object from Application.Union works differently to a Normal Range object..it will not let me paste out all the range values ( I think)
    Correct?

    _3 b) I cannot use__ Application.Union__ to “link” Ranges in different sheets..
    Correct?


    _4)
    Quote Originally Posted by pgc01 View Post
    .... .........
    You could, of course, also use the formula directly in the worksheet.
    Yes i think I see the logic in that
    _.... So i guess i need to learn about Named ranges first, and then
    _ 4a) Paste the Formula in with the C S E Stuff ( second C S E type.. after highlighting Range "I2:J13" etc..etc... )
    Correct?
    And / or
    _ 4 b) Is there a worksheet equivalent to my
    ___Set rng_rAreas = Union(ws.Range("B2:E3"), ws.Range("D6:G7"), ws.Range("A11:D12"))
    So i could check without doing a named range thing ?

    [B]Thanks again, and no rush with an answer. ( answers!!! )/B]
    This is gonna help me a lot with my project... for a while....****



    Alan

    P.s.
    This is all great learning stuff,....and i think... regarding...this****
    Quote Originally Posted by pgc01 View Post
    ....., I would probably not use it unless it's a simple case,.... ..
    _ ...not too sure about that. Maybe mastering this is a good alternative to some C S E stuff. It seems to be following a similar logic. And is giving an amazing insight into how Exel is working, ... and maybe explain a bit my __ “lines shooting out and picking out the Elements intercepted, along with “offsets used in the computer memory “ Theory__

    P.P.s
    Just to help anyone following.. I have played around a bit with the pgc code, to help me understand it..so here the Screen shot, code, and File... I think i may also upload a file here... I don’t think Peter SSs will be upset..

    Using Excel 2007
    Row\Col
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    1
    Area 1
    2
    A1 R1C1
    A1 R1C2
    A1 R1C3
    A1 R1C4
    A1 R1C1 A1 R1C2
    3
    A1 R2C1
    A1 R2C2
    A1 R2C3
    A1 R2C4
    A1 R2C1 A1 R2C2
    4
    A1 R1C3 A1 R1C4
    5
    Area 2
    A1 R2C3 A1 R2C4
    6
    A2 R1C1
    A2 R1C2
    A2 R1C3
    A2 R1C4
    A3 R1C1 A3 R1C2
    7
    A2 R2C1
    A2 R2C2
    A2 R2C3
    A2 R2C4
    A3 R2C1 A3 R2C2
    8
    A3 R1C3 A3 R1C4
    9
    A3 R2C3 A3 R2C4
    10
    Area 3
    A2 R1C1 A2 R1C2
    11
    A3 R1C1
    A3 R1C2
    A3 R1C3
    A3 R1C4
    A2 R2C1 A2 R2C2
    12
    A3 R2C1
    A3 R2C2
    A3 R2C3
    A3 R2C4
    A2 R1C3 A2 R1C4
    13
    A2 R2C3 A2 R2C4
    14
    15
    1
    1
    1
    2
    1
    1
    A1 R1C1 A1 R1C2 A1 R1C3 A1 R1C4
    16
    2
    2
    1
    2
    1
    1
    A1 R2C1 A1 R2C2 A1 R2C3 A1 R2C4
    17
    1
    1
    3
    4
    1
    1
    18
    2
    2
    3
    4
    1
    1
    19
    1
    1
    1
    2
    2
    2
    20
    2
    2
    1
    2
    2
    2
    21
    1
    1
    3
    4
    2
    2
    22
    2
    2
    3
    4
    2
    2
    23
    1
    1
    1
    2
    3
    3
    24
    2
    2
    1
    2
    3
    3
    25
    1
    1
    3
    4
    3
    3
    26
    2
    2
    3
    4
    3
    3
    pgcArrays

    Code:

    Code:
    '
    Sub TransposeMultiAreaRangepgc() '  http://www.mrexcel.com/forum/excel-questions/908760-visual-basic-applications-copy-2-dimensional-array-into-1-dimensional-single-column-2.html#post4375560
    'Worksheets Info
    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("pgcArrays")
    Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Worksheets("pgcArrays2")
    Dim rng_rAreas As Range
    'Set rng_rAreas = Application.Union(ws.Range("B2:E3"), ws.Range("D6:G7"), ws2.Range("A11:D12"))'Don't work
    Set rng_rAreas = Application.Union(ws.Range("B2:E3"), ws.Range("A11:D12"), ws.Range("D6:G7"))
            '    Dim arrIn() As Variant: Let arrIn() = rng_rAreas.Value 'Gives 2"row" x 4"column" Array ?????
            '    ws.Range("J15").Resize(UBound(arrIn(), 1), UBound(arrIn(), 2)).Clear
            '    Let ws.Range("J15").Resize(UBound(arrIn(), 1), UBound(arrIn(), 2)).Value = arrIn() 'Gives First Range ????
    
    'last 3 Arguments for 4 Arguments in .Index!!!
    Dim arg1() As Variant, arg2() As Variant, arg3() As Variant
    Let arg1() = Evaluate("if(row(1:12)*column(A:B),mod(row(1:12)-1,2)+1)")
    Let ws.Range("A15").Resize(UBound(arg1(), 1), UBound(arg1(), 2)).Value = arg1()
    
    Let arg2() = Evaluate("if(row(1:12)*column(A:B),2*int(mod(row(1:12)-1,4)/2)+column(a:b))")
    Let ws.Range("D15").Resize(UBound(arg2(), 1), UBound(arg2(), 2)).Value = arg2()
    
    Let arg3() = Evaluate("if(row(1:12)*column(A:B),int((row(1:12)-1)/4)+1)")
    Let ws.Range("G15").Resize(UBound(arg3(), 1), UBound(arg3(), 2)).Value = arg3()
    
    'Output Results
    Dim arrOut() As Variant
    Let arrOut() = Application.Index(rng_rAreas, arg1(), arg2(), arg3())
    ws.Range("I2").Resize(UBound(arrOut(), 1), UBound(arrOut(), 2)).Clear
    ws.Range("I2").Resize(UBound(arrOut(), 1), UBound(arrOut(), 2)).Value = arrOut()
    
            '    Range("I2:J13") = Application.Index(Range("rAreas"), _
            '        Evaluate("if(row(1:12)*column(A:B),mod(row(1:12)-1,2)+1)"), _
            '        Evaluate("if(row(1:12)*column(A:B),2*int(mod(row(1:12)-1,4)/2)+column(a:b))"), _
            '        Evaluate("if(row(1:12)*column(A:B),int((row(1:12)-1)/4)+1)"))
    
    End Sub
    File: ( Code in Worksheet Code Modul of Worksheet ” pgcArrays” )
    ( XL 2007 “Workbook1SlicingArrayErrorsWTFAtameArronGyanEuroSong.xlsm” )
    https://app.box.com/s/1xsfq2z5cpfa91xh8cujjsdfxmait8sb



    P.P.Ps.
    ....I think I had this Theory over a year ago.. in a Aladin Akyurek Thread...
    http://www.mrexcel.com/forum/excel-q...ml#post3984359
    shame Aladin don’t like me... he may have made a good contribution, then or now!!!!!! .....Wonk Poo!!!!
    Last edited by DocAElstein; Dec 21st, 2015 at 06:02 PM.

  3. #23
    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 - copy 2-dimensional array into 1-dimensional (single column)

    Hi
    Quick..Edit:
    I am getting the general syntax idea about the multiple Ranges as first argument ( In a bracket ) , and that the 4th Argument does indeed select which of those Ranges is looked at...There seemed to be no VBA stuff about that.. but there is loads on the normal Spreadsheet Index, so I am looking at that and seeing the parallel to VBA...
    Alan

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

    Default Re: VBA - copy 2-dimensional array into 1-dimensional (single column)

    Hi

    Just checking before bedtime.

    I agree with you. Seeing how it works in the worksheet helps understanding how the code works..

    A vary basic form of a formula solution is

    - Define the named range rAreas, refers to: =Sheet1!$B$2:$E$3,Sheet1!$D$6:$G$7,Sheet1!$A$11:$D$12

    - Select I2:J13

    - paste in the formula bar:

    =INDEX(rAreas,MOD({0;1;2;3;4;5;6;7;8;9;10;11},2)+1,2*INT(MOD({0;1;2;3;4;5;6;7;8;9;10;11},4)/2)+{1,2},INT({0;1;2;3;4;5;6;7;8;9;10;11}/4)+1)

    - confirm with CSE

    You should see the same result as with the code.

    Remark: as I said this is a basic form of the formula to understand how it works, you can then improve it.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  5. #25
    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 - copy 2-dimensional array into 1-dimensional (single column)

    Hi pgc,
    Quote Originally Posted by pgc01 View Post
    ...Just checking before bedtime.
    ..I agree with you. Seeing how it works in the worksheet helps understanding how the code works..
    ...
    Hope you slept well. Thanks once again. This is really interesting and good learning stuff.

    Actually the point i was getting at ( easily overlooked in the massive ramblings!! ) was that you had taken the old___If(row(_____ coecing stuff ___ a bit further taking it to a new dimension where I and many men had never gone before!!!!! - ( My _2b) along with my „Thick“ bit from _2c) ).
    This was all again somehow giving some validity to my “lines shooting out and picking out the Elements intercepted, along with “___.."offsets used in the computer memory “ Theory__ “ and that this sort of applied to C S E stuff very similarly : So I was talking about the 2nd and 3rd Arguments in your code, and the strange “extending out” trick ( “Thick making” ) effect they do, which I was able to also do in C S E stuff by taking a Formula which would normally be C S E ' ed in by highlighting a column, - but doing the same but extending the “column” thickness”.
    Both of these seemed possibly to be revealing ( making visible ) my “lines shooting out” and “coercing” as it were the “interception” to extend over the defined “Thickness” in the extra bit you added to the __ If(row(_____ coercing__ ( - your __ *column(A:B)__ is a parallel to my extending the area “Thickness” i select for the C S E Formula to go in..
    _................................................................................


    BUT: That extra input of a formula was very welcome....thanks...and...so....
    ____.......I took the plunge and learnt about named ranges as an aside to help with the example you have given....
    __... –Not too difficult, but I could not google doing it for your multiple “......the range with the areas B2:E3, D6:G7 and A11:D12. I defined the name rAreas as the name of this range.......”. So i just fiddled and found a “Name Manager” , fiddled with the defining to have ( In German Excel ) ___ =pgcArrays!$B$2:$E$3;pgcArrays!$D$6:$G$7;pgcArrays!$A$11:$D$12 ___
    Then you original VBA code from Post #17 works for me..
    ( Another thing learned! - I do hesitate to learn a bit, because i am just scared of over filling my limited brain capacity – easily done with Excel because is so vast!!

    Then i remembered I did come across this before
    http://www.mrexcel.com/forum/excel-q...ml#post4332606
    And there are some problems or subtleties with exactly how you reference it!
    ___..again not documented i think !


    __Further i would suggest IMHO that Excel/VBA is again a bit badly documented. Just as i noted that The Range Object form a Application.Union is something different from a normal Range Object, ..it is also the case that Giving a name to multiple Ranges is a different thing to a Named Range.
    In short I would say we have two different things.
    First:
    _Rem 1) A Named Range, parallel to a Range in VBA
    And then something different:
    _Rem 2) A Named List of Ranges, parallel to A Range defined by a Application.Union ( Maybe we could unify it as a __Unioned Named Ranges Name )__....thing Wonk!

    This code shows that up.... in the last line is a naive attempt to do a consolidation of Ranges into 1 Array. It does not work.We have to resort to the techniques we have been discussing in this Thread to do that!!

    Code:
    Sub UnionedNamedRanges()
    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("pgcArrays")
    Rem 1) 'Named Range
    ws.Range("A1:A3").Value = "1"
    ws.Range("A1:A3").Interior.Color = 15204351 'Works!
    Dim arrA1A3() As Variant: Let arrA1A3() = ws.Range("A1:A3").Value 'Works!
    
    Rem 2) 'Named List of Ranges ( Unioned Named Ranges Name ) ( In name manager I have made for rAreas    pgcArrays!$B$2:$E$3;pgcArrays!$D$6:$G$7;pgcArrays!$A$11:$D$12  )
    Dim arrpgcrAreas() As Variant
    ws.Range("rAreas").Interior.Color = 15204351 'Works!
    Dim arrAreas() As Variant: Let arrAreas() = ws.Range("rAreas").Value 'Don't Work. Only gives first Range
    End Sub
    '( Oh and BTW. the Names Manager seems case insensitive - ties up - we can say  __a1__ or ___A1__etc )
    _ ............

    Anyways...

    So But, The formula variation was a nice addition. Thanks very much. I played around a lot with it, and got the results, so that was a great learning experience. I did get it to work, but had to play around a lot with the translation
    http://www.mrexcel.com/forum/test-he...mega-bits.html
    ___...my "Tool" did not translate correctly ( I think ). The problem was the third argument. Your original third argument formula

    ______=2*int(mod(row(1:12)-1,4)/2)+column(a:b)
    Was translated correctly:
    ______=2*GANZZAHL(REST(ZEILE(1:12)-1;4)/2)+SPALTE(A:B)

    But your version of the formula from Post #24
    ______=2*INT(MOD({0;1;2;3;4;5;6;7;8;9;10;11},4)/2)+{1,2}
    was not:
    ______=2*GANZZAHL(REST({0;1;2;3;4;5;6;7;8;9;10;11};4)/2)+{1;2}

    __so i had to modify the last bit to give:
    ______=2*GANZZAHL(REST({0;1;2;3;4;5;6;7;8;9;10;11};4)/2)+{1\2}

    ____

    Then the entire formula worked perfectly

    And just because of my reluctance to use the “Named Range” here ( as i think it is something different – A Named List of Ranges , I did this version of the code for completenes- This maybe shows up a bit better that the First argument is something different here – not realy A Named Range ( I think!? )

    =INDEX((B2:E3;D6:G7;A11:D12);REST({0;1;2;3;4;5;6;7;8;9;10;11};2)+1;2*GANZZAHL(REST({0;1;2;3;4;5;6;7;8;9;10;11};4)/2)+{1\2};GANZZAHL({0;1;2;3;4;5;6;7;8;9;10;11}/4)+1)
    Row\Col
    O
    2
    =INDEX((B2:E3,D6:G7,A11:D12),MOD({0;1;2;3;4;5;6;7;8;9;10;11},2)+1,2*INT(MOD({0;1;2;3;4;5;6;7;8;9;10;11},4)/2)+{1,2},INT({0;1;2;3;4;5;6;7;8;9;10;11}/4)+1)



    So more good stuff Thanks!
    Alan


    P. s. I should like to take the translation problem further with the owner of the Translator. But I cannot be too sure how / if my German Excel is somehow having an effect. I do know that in the past English Excel Files supplied to me with many complicated formulas have always translated perfectly, ( as appears to be the case with the Forum tools converting back from my corrected German Formulas back to your original ).
    _.....I was wondering therefore if you are able and have the time you are able to upload me a file with that last formula of yours from Post #24? I think under the circumstances that could be a reasonable acceptable exception to the Spirit of Forum Rule 4 which discourages attachments : - I have already presented all the relevant Info in the Thread, and will report my findings as to the Translation problem
    __ But this last request is not too important. This Thread continues to be a great learning experience for me ( and I suspect others ) .- thanks again for your contributions..

  6. #26
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,698
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VBA - copy 2-dimensional array into 1-dimensional (single column)

    Hi Alan

    Instead of using your Translator you can use the fact that vba works with the English version of excel and apply the formula using the Range properties .Formula or, for the array formulas, with .FormulaArray.

    For the 2 examples in your last post:

    Code:
    ActiveCell.Formula = "=2*INT(MOD({0;1;2;3;4;5;6;7;8;9;10;11},4)/2)+{1,2}"
    
    Range("I2:J13").FormulaArray = _
        "=INDEX(rAreas,MOD({0;1;2;3;4;5;6;7;8;9;10;11},2)+1,2*INT(MOD({0;1;2;3;4;5;6;7;8;9;10;11},4)/2)+{1,2},INT({0;1;2;3;4;5;6;7;8;9;10;11}/4)+1)"
    This is a quick and easy way of writing formulas of the English version into any other excel installation in another language.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  7. #27
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,698
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VBA - copy 2-dimensional array into 1-dimensional (single column)

    I don't agree with what you say about ranges. It seems that you think that a range is a "rectangular, contiguous group of cells".

    That's not how I see a range. That's not also how excel sees it.

    The way I understand it, a Range object is a group of cells in a worksheet.
    The list of the cells of the range is organised in rectangular groups of cells, each one named an Area.

    For ex. a range can have the cells A2:A4, E2:E4 and P1.

    You define it in the worksheet as for ex.

    Range_1: =Sheet1!A2:A4,Sheet1!E2:E4,Sheet1!P1

    in vba you use Application.Union to add the areas to the range.

    If the range has more than 1 area you can access through a name or directly, enclosing the areas in parentheses in the worksheet.
    Some functions, as you know, accept the use of multi-area ranges, for ex. Large().

    Ex.: =LARGE((A2:A4,E2:E4,P1),3)

    Notice that even if the range has only one area you can use it when you adress a cell.

    This is OK, you are accessing cell row 3 column 2 of Area 3 of the range:

    Code:
    Range("A2:A4,E2:E4,F5:G14").Areas(3).Cells(3, 2).Value = "Hi!"
    but this is also OK, although you don't need it because Area 1 is the default,

    Code:
    Range("A2:A4").Areas(1).Cells(2, 1).Value = "Hi!"

    A simple way to select a multi-area range in the worksheet is to use CTRL-CLICK

    Ex.

    Select A1:C4

    Now press CTRL and keep it down. With the mouse select F1:G4

    Now you see both areas highlighted.

    This is a 2 areas range. With it highlighted, type XX in the formula bar and confirm with CTRL-ENTER. You'll see that all the range (both areas) is populated.


    The fact that we usually use ranges with only 1 area does not mean that the range can only have 1 area.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  8. #28
    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 - copy 2-dimensional array into 1-dimensional (single column)

    Hi pgc,
    Quote Originally Posted by pgc01 View Post
    ......
    Instead of using your Translator you can use the fact that vba works with the English version of excel and apply the formula using the Range properties .Formula or, for the array formulas, with .FormulaArray.........
    This is a quick and easy way of writing formulas of the English version into any other excel installation in another language.
    Thanks for that Tip. That is certainly very handy.. ( . I should have twigged to that....I recently picked up the same but in reverse Tip, from Joe4 here
    http://www.mrexcel.com/forum/excel-q...ml#post4367502
    ... )

    Thanks
    Alan

    P.s. I note in passing that something what came out from here
    http://www.mrexcel.com/forum/excel-q...ml#post4295788
    ___.. for a simple formula, ___ .Value __ works the same as __.Formula __. – It seems the__ =”=__” is the important bit that tells VBA to put ( or Excel Spreadsheet to take ) a Formula


    Code:
    '
    Sub pgcTranslateFormula()
    'pgc   http://www.mrexcel.com/forum/excel-questions/908760-visual-basic-applications-copy-2-dimensional-array-into-1-dimensional-single-column-3.html#post4377042
    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("pgcArrays")
    ws.Range("J18").Formula = "=2*INT(MOD({0;1;2;3;4;5;6;7;8;9;10;11},4)/2)+{1,2}"
    ws.Range("Q2:R13").FormulaArray = _
        "=INDEX(rAreas,MOD({0;1;2;3;4;5;6;7;8;9;10;11},2)+1,2*INT(MOD({0;1;2;3;4;5;6;7;8;9;10;11},4)/2)+{1,2},INT({0;1;2;3;4;5;6;7;8;9;10;11}/4)+1)"
    
    '  .Value also Translates!    'http://www.mrexcel.com/forum/excel-questions/887822-formula-link-cell-array-instead-cell-value-based-cell-r-c-co-ordinates-2.html#post4295788
    ws.Range("J19").Formula = "=2*INT(MOD({0;1;2;3;4;5;6;7;8;9;10;11},4)/2)+{1,2}"
    Dim arrFF(1 To 1) As Variant: Let arrFF(1) = "=2*INT(MOD({0;1;2;3;4;5;6;7;8;9;10;11},4)/2)+{1,2}"
    ws.Range("J20").Value = arrFF(1)
        
    '  Macro Recording of Putting   =2*GANZZAHL(REST({0;1;2;3;4;5;6;7;8;9;10;11};4)/2)+{1\2}    in a cell
    '   ......  .FormulaR1C1 = "=2*INT(MOD({0;1;2;3;4;5;6;7;8;9;10;11},4)/2)+{1,2}"
    'Joe4   http://www.mrexcel.com/forum/excel-questions/907227-visual-basic-applications-code-lookup-value-2.html#post4367502
    End Sub
    Last edited by DocAElstein; Dec 22nd, 2015 at 11:36 AM.

  9. #29
    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 - copy 2-dimensional array into 1-dimensional (single column)

    Hi pgc,
    Quote Originally Posted by pgc01 View Post
    I don't agree with what you say about ranges. It seems that you think that a range is a "rectangular, contiguous group of cells".
    That's not how I see a range. That's not also how excel sees it.
    The way I understand it, A Range Object is a group of cells in a worksheet.
    The list of the cells of the range is organised in rectangular groups of cells, each one named an Area....
    The fact that we usually use ranges with only 1 area does not mean that the range can only have 1 area.
    .....
    ___...Ahh......... That. was the bit I had missed !! So I was stupidly thinking the single rectangular, contiguous group of cells was what i had thought up to now was a Range Object , and that the thing got, for example, from the Application.Union or your rAreas was a new type of thing that i had not encountered. I see now that what i said there is rubbish! ......

    Got it, - Thanks!!
    _.........................

    Quote Originally Posted by pgc01 View Post
    .......
    This is OK, you are accessing cell row 3 column 2 of Area 3 of the range:
    Code:
    Range("A2:A4,E2:E4,F5:G14").Areas(3).Cells(3, 2).Value = "Hi!"
    but this is also OK, although you don't need it because Area 1 is the default,
    Code:
    Range("A2:A4").Areas(1).Cells(2, 1).Value = "Hi!"
    .....

    Got it, - Thanks!!


    ___......It is just, as you say, that up until now I was always effectively doing something like this,
    _______Range(_______).Areas.Item(1)._____
    When i wrote something like this
    _______Range(_______)._____
    __that all makes sense now. Of course, Googling i see that now... but I would never of twigged where or what to look for, - so thanks!. ( BTW. – People on Forums are going to get another reason to get annoyed with me for a while. .. as I am always going to write .....__Range.Item(1).__......for a while.. to remind me of the areas thing... I will blame you and that might help put off me being banned for a few weeks at least...)

    _ ...................................

    Quote Originally Posted by pgc01 View Post
    ........

    A simple way to select a multi-area range in the worksheet is to use CTRL-CLICK
    Ex.
    Select Area 1
    Now press CTRL and keep it down. With the mouse select Area 2....
    Now you see both areas highlighted.
    This is a 2 areas range. With it highlighted, type XX in the formula bar and confirm with CTRL-ENTER. You'll see that all the range (both areas) is populated........
    _.. This sounds like the answer to a few Threads i saw that never got answered!! I did not know you could do that...So for Fun at the end of my code below i use your Technique to take in a 2 Area Range with a Application.InputBox:
    __.... 2 versions.
    One takes in as a string
    and
    one takes a ( 2 Area ) Range Object.

    _____– ...The latter helps me to get the point that the two area “thing” is a Range Object.. as you have so well explained and put me straight ( again! )

    Many Thanks, again (.... P.s. – Maybe I should have twigged by your appropriately named Variable, rAreas ...)

    Alan

    Code Summarising what was explained in this post:

    Code:
    Sub pgcRangeDotAreas()
    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("pgcArrays")
    
    'Named Range Object of 3 Areas( In Name Manager I have made for rAreas    pgcArrays!$B$2:$E$3;pgcArrays!$D$6:$G$7;pgcArrays!$A$11:$D$12  )
    Dim arrpgcrAreas() As Variant
    ws.Range("rAreas").Interior.Color = 15204351 'Works!
    Dim arrAreas() As Variant: Let arrAreas() = ws.Range("rAreas").Value 'Works... Only gives first Range...
    Let arrAreas() = ws.Range("rAreas").Areas(1).Value 'Works... Gives first Range
    Let arrAreas() = ws.Range("rAreas").Areas.Item(2).Value 'Works... Gives second Range
    
    'Referrence a cell using .Areas
    ws.Range("B2:E3,$D$6:$G$7,D12").Areas(3).Cells(2, 2).Value = "Hi! hanging at Bottom Left of 3rd Area"
    
    '.."..Select a multi-area range:Select Area 1,  Hold Ctrl,  Select Area 1,  ( confirm with CTRL-ENTER ) "
    'pgc  http://www.mrexcel.com/forum/excel-questions/908760-visual-basic-applications-copy-2-dimensional-array-into-1-dimensional-single-column-3.html#post4377067
    Dim strAreas As String: Let strAreas = Application.InputBox(Prompt:="Select Area1, Hold CTRL , Select Area2, Edit off = at start", Title:="2 Area strRange Select", Default:="i.e. I23:J24;K27:L28", Left:=5, Top:=5, Type:=2) '..ask for the top left cell in which the new round data is in file A as this could change in future rounds
    Let strAreas = Replace(strAreas, ";", ",") 'Bodge for German Excel
    Let ws.Range(strAreas).Areas.Item(2).Value = "Hi in Area_2!"
    Dim rngAreas As Range: Set rngAreas = Application.InputBox(Prompt:="Select Area1, Hold CTRL , Select Area2", Title:="2 Area range Object Select", Default:="F24", Left:=5, Top:=5, Type:=8)  '..ask for the top left cell in which the new round data is in file A as this could change in future rounds
    Let rngAreas.Areas(2).Value = "Hi in Area_2!"
    
    End Sub
    Last edited by DocAElstein; Dec 22nd, 2015 at 02:03 PM.

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
  •