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

[astrbac]

Hello,

I have a (probably) simple problem but could not solve it for the life of me. I need to copy 2-dimensional array from one sheet into a single column on another sheet.

Example:

Sheet1, data is in a range C2:N225

needs to be copied (transposed) onto

Sheet2, in a range D2:D2680

> First row of the array (Sheet1!C2:N2) goes into Sheet2!D2:D14
> Second row (Sheet1!C3:N3) goes into Sheet2!D15:D27
> ... and so forth

Many thanks!

 

[DocAElstein]

Hi
Thanks for the feedback 

......
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

 

[DocAElstein]

@ pgc

......
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/2...cing-an-array-without-loop-application-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

........
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?

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)

.... .........
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 ?

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****

....., 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
[Table="width:, class:head"][tr=bgcolor:skyblue][th]_Row\^Col[/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][th]

J​

[/th][th]

K​

[/th][th]

L​

[/th][th]

M​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]

1​

[/td][td]

​

[/td][td]

Area 1​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]

2​

[/td][td]

​

[/td][td=bgcolor:#FFFFB9]

A1 R1C1​

[/td][td=bgcolor:#FFFFB9]

A1 R1C2​

[/td][td=bgcolor:#FFFFB9]

A1 R1C3​

[/td][td=bgcolor:#FFFFB9]

A1 R1C4​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]A1 R1C1[/td][td]A1 R1C2[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]

3​

[/td][td]

​

[/td][td=bgcolor:#FFFFB9]

A1 R2C1​

[/td][td=bgcolor:#FFFFB9]

A1 R2C2​

[/td][td=bgcolor:#FFFFB9]

A1 R2C3​

[/td][td=bgcolor:#FFFFB9]

A1 R2C4​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]A1 R2C1[/td][td]A1 R2C2[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]

4​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]A1 R1C3[/td][td]A1 R1C4[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]

5​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

Area 2​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]A1 R2C3[/td][td]A1 R2C4[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]

6​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td=bgcolor:#FFFFB9]

A2 R1C1​

[/td][td=bgcolor:#FFFFB9]

A2 R1C2​

[/td][td=bgcolor:#FFFFB9]

A2 R1C3​

[/td][td=bgcolor:#FFFFB9]

A2 R1C4​

[/td][td]

​

[/td][td]A3 R1C1[/td][td]A3 R1C2[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]

7​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td=bgcolor:#FFFFB9]

A2 R2C1​

[/td][td=bgcolor:#FFFFB9]

A2 R2C2​

[/td][td=bgcolor:#FFFFB9]

A2 R2C3​

[/td][td=bgcolor:#FFFFB9]

A2 R2C4​

[/td][td]

​

[/td][td]A3 R2C1[/td][td]A3 R2C2[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]

8​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]A3 R1C3[/td][td]A3 R1C4[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]

9​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]A3 R2C3[/td][td]A3 R2C4[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]

10​

[/td][td]

Area 3​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]A2 R1C1[/td][td]A2 R1C2[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]

11​

[/td][td=bgcolor:#FFFFB9]

A3 R1C1​

[/td][td=bgcolor:#FFFFB9]

A3 R1C2​

[/td][td=bgcolor:#FFFFB9]

A3 R1C3​

[/td][td=bgcolor:#FFFFB9]

A3 R1C4​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]A2 R2C1[/td][td]A2 R2C2[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]

12​

[/td][td=bgcolor:#FFFFB9]

A3 R2C1​

[/td][td=bgcolor:#FFFFB9]

A3 R2C2​

[/td][td=bgcolor:#FFFFB9]

A3 R2C3​

[/td][td=bgcolor:#FFFFB9]

A3 R2C4​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]A2 R1C3[/td][td]A2 R1C4[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]

13​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]A2 R2C3[/td][td]A2 R2C4[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]

14​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]

15​

[/td][td]

1​

[/td][td]

1​

[/td][td]

​

[/td][td]

1​

[/td][td]

2​

[/td][td]

​

[/td][td]

1​

[/td][td]

1​

[/td][td]

​

[/td][td]A1 R1C1[/td][td]A1 R1C2[/td][td]A1 R1C3[/td][td]A1 R1C4[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]

16​

[/td][td]

2​

[/td][td]

2​

[/td][td]

​

[/td][td]

1​

[/td][td]

2​

[/td][td]

​

[/td][td]

1​

[/td][td]

1​

[/td][td]

​

[/td][td]A1 R2C1[/td][td]A1 R2C2[/td][td]A1 R2C3[/td][td]A1 R2C4[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]

17​

[/td][td]

1​

[/td][td]

1​

[/td][td]

​

[/td][td]

3​

[/td][td]

4​

[/td][td]

​

[/td][td]

1​

[/td][td]

1​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]

18​

[/td][td]

2​

[/td][td]

2​

[/td][td]

​

[/td][td]

3​

[/td][td]

4​

[/td][td]

​

[/td][td]

1​

[/td][td]

1​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]

19​

[/td][td]

1​

[/td][td]

1​

[/td][td]

​

[/td][td]

1​

[/td][td]

2​

[/td][td]

​

[/td][td]

2​

[/td][td]

2​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]

20​

[/td][td]

2​

[/td][td]

2​

[/td][td]

​

[/td][td]

1​

[/td][td]

2​

[/td][td]

​

[/td][td]

2​

[/td][td]

2​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]

21​

[/td][td]

1​

[/td][td]

1​

[/td][td]

​

[/td][td]

3​

[/td][td]

4​

[/td][td]

​

[/td][td]

2​

[/td][td]

2​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]

22​

[/td][td]

2​

[/td][td]

2​

[/td][td]

​

[/td][td]

3​

[/td][td]

4​

[/td][td]

​

[/td][td]

2​

[/td][td]

2​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]

23​

[/td][td]

1​

[/td][td]

1​

[/td][td]

​

[/td][td]

1​

[/td][td]

2​

[/td][td]

​

[/td][td]

3​

[/td][td]

3​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]

24​

[/td][td]

2​

[/td][td]

2​

[/td][td]

​

[/td][td]

1​

[/td][td]

2​

[/td][td]

​

[/td][td]

3​

[/td][td]

3​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]

25​

[/td][td]

1​

[/td][td]

1​

[/td][td]

​

[/td][td]

3​

[/td][td]

4​

[/td][td]

​

[/td][td]

3​

[/td][td]

3​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]

26​

[/td][td]

2​

[/td][td]

2​

[/td][td]

​

[/td][td]

3​

[/td][td]

4​

[/td][td]

​

[/td][td]

3​

[/td][td]

3​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]pgcArrays[/td][/tr][/table]

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-...s-matching-unique-criteria-3.html#post3984359
shame Aladin don’t like me... he may have made a good contribution, then or now!!!!!! .....Wonk Poo!!!!

 

[DocAElstein]

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

 

[pgc01]

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.

 

[DocAElstein]

Hi pgc,

...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-...s-[a1-a5]-benefits-dangers-2.html#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!!

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-here/910212-testinpgc-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)
[Table="width:, class:head"][tr=bgcolor:skyblue][th]_Row\^Col[/th][th]

O​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]

2​

[/td][td]

=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)​

[/td][/tr]
[/table]


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..

 

[pgc01]

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:

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.

 

[pgc01]

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:

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,

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.

 

[DocAElstein]

Hi pgc,

......
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-...ications-code-lookup-value-2.html#post4367502
... )

Thanks
Alan

P.s. I note in passing that something what came out from here
http://www.mrexcel.com/forum/excel-...ased-cell-r-c-co-ordinates-2.html#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

'
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

 

[DocAElstein]

Hi pgc,

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!!
_.........................

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

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,

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...)

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

........

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:

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