@ 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?
Rich (BB 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)
.... .........
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:
Rich (BB 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!!!!