Appendix Thread. App Index Rws() Clms() Majic code line Codings for other Threads, Tables etc.)

[DocAElstein]

Some notes for this main forum post
https://eileenslounge.com/viewtopic.php?f=27&t=39588
This uses something quite smart stumbled across here
https://eileenslounge.com/viewtopic....266691#p266691

If we have a 1 D array of arrays , like form example { {"a", "b"} , { "c", "d" } } , then strangely it acts in our famous App Index Rws() Clms() Magic code line just as if it was an array like this
{"a", "b"
"c", "d" }

Strange , but true.

So in Hans macro from here,
http://www.eileenslounge.com/viewtop...306785#p306785
, or rather the modified one from here ,
http://www.eileenslounge.com/viewtop...306880#p306880
, instead of pasting a 1 D array out each time, so pasting out a line each time, we add that array to an array of arrays, then finally paste out that final array using the App Index Rws() Clms() Magic code line.

Effectively we are doing like this

Sub WonDeeArrayOfArrays() ' https://eileenslounge.com/viewtopic.php?p=266691#p266691
Dim arr1D(1 To 2) As Variant
 Let arr1D(1) = Array("a", "b")
 Let arr1D(2) = Array("c", "d")
Dim arrOut() As Variant
 Let arrOut() = Application.Index(arr1D(), Evaluate("=ROW(1:2)"), Array(1, 2))
 Let arrOut() = Application.Index(arr1D(), Evaluate("=ROW(1:2)"), Evaluate("=COLUMN(A:B)"))
End Sub

Option Explicit
Const SourceDivCol = 1
Const SourcePosCol = 2
Const SourceRepCol = 3
Const SourceLevCol = 4
Const SourceEmpCol = 5
Const SourceCodCol = 6
Const TargetDivCol = 15
Const TargetLevCol = 16
Const TargetPosCol = 17
Const TargetEmpCol = 18
Const TargetCodCol = 19
Dim SourceRow As Long
Dim TargetRow As Long
Dim Cnt As Long
Dim WunDeeArrayOfArrays() As Variant



Sub CreateReportHansAlan2() '
 ReDim WunDeeArrayOfArrays(1 To Cells(1).CurrentRegion.Rows.Count - 2)
    Dim Boss As Range
    Dim Adr As String
    Dim Pos As String
    Application.ScreenUpdating = False
    TargetRow = 2
    Set Boss = Columns(SourceLevCol).Find(What:=1, LookAt:=xlWhole)
    Adr = Boss.Address
    Do
        SourceRow = Boss.Row
        TargetRow = TargetRow + 1
     Let Cnt = Cnt + 1: Let WunDeeArrayOfArrays(Cnt) = Application.Index(Cells, SourceRow, Array(SourceDivCol, SourceLevCol, SourcePosCol, SourceEmpCol, SourceCodCol))
'     Let Range("O" & TargetRow & ":S" & TargetRow & "").Value2 = Application.Index(Cells, SourceRow, Array(1, 4, 2, 5, 6))
'     Let Range("O" & TargetRow & ":S" & TargetRow & "").Value2 = Application.Index(Cells, SourceRow, Array(SourceDivCol, SourceLevCol, SourcePosCol, SourceEmpCol, SourceCodCol))
'        Cells(TargetRow, TargetDivCol).Value = Cells(SourceRow, SourceDivCol).Value
'        Cells(TargetRow, TargetLevCol).Value = Cells(SourceRow, SourceLevCol).Value
'        Cells(TargetRow, TargetPosCol).Value = Cells(SourceRow, SourcePosCol).Value
'        Cells(TargetRow, TargetEmpCol).Value = Cells(SourceRow, SourceEmpCol).Value
'        Cells(TargetRow, TargetCodCol).Value = Cells(SourceRow, SourceCodCol).Value
        Pos = Cells(SourceRow, SourcePosCol).Value
        Call AddKids(Pos)
        Set Boss = Columns(SourceLevCol).Find(What:=1, After:=Boss, LookAt:=xlWhole)
        If Boss Is Nothing Then Exit Do
    Loop Until Boss.Address = Adr
    Application.ScreenUpdating = True
 
 Let Range("O3").Resize(Cells(1).CurrentRegion.Rows.Count - 2, 5).Value2 = Application.Index(WunDeeArrayOfArrays, Evaluate("=ROW(1:" & Cells(1).CurrentRegion.Rows.Count - 2 & ")"), Evaluate("=COLUMN(A:E)"))
End Sub

Sub AddKids(BossPos As String) '
    Dim Child As Range
    Dim Adr As String
    Dim Pos As String
    Set Child = Columns(SourceRepCol).Find(What:=BossPos, LookAt:=xlWhole)
    If Child Is Nothing Then Exit Sub
    Adr = Child.Address
    Do
        SourceRow = Child.Row
        TargetRow = TargetRow + 1
     Let Cnt = Cnt + 1: Let WunDeeArrayOfArrays(Cnt) = Application.Index(Cells, SourceRow, Array(SourceDivCol, SourceLevCol, SourcePosCol, SourceEmpCol, SourceCodCol))
'     Let Range("O" & TargetRow & ":S" & TargetRow & "").Value2 = Application.Index(Cells, SourceRow, Array(SourceDivCol, SourceLevCol, SourcePosCol, SourceEmpCol, SourceCodCol))
'        Cells(TargetRow, TargetDivCol).Value = Cells(SourceRow, SourceDivCol).Value
'        Cells(TargetRow, TargetLevCol).Value = Cells(SourceRow, SourceLevCol).Value
'        Cells(TargetRow, TargetPosCol).Value = Cells(SourceRow, SourcePosCol).Value
'        Cells(TargetRow, TargetEmpCol).Value = Cells(SourceRow, SourceEmpCol).Value
'        Cells(TargetRow, TargetCodCol).Value = Cells(SourceRow, SourceCodCol).Value
        Pos = Cells(SourceRow, SourcePosCol).Value
        Call AddKids(Pos)
        Set Child = Columns(SourceRepCol).Find(What:=BossPos, After:=Child, LookAt:=xlWhole)
        If Child Is Nothing Then Exit Do
    Loop Until Child.Address = Adr
End Sub

Ref
https://eileenslounge.com/viewtopic....266691#p266691
https://www.excelforum.com/excel-new-users-basics/1099995-application-index-with-look-up-rows-and-columns-arguments-as-vba-arrays.html#post4571172
https://www.excelforum.com/tips-and-tutorials/758402-vba-working-with-areas-within-2d-arrays.html#post5408376
https://www.excelforum.com/excel-programming-vba-macros/1328703-copy-1-dim-array-to-and-2-dim-array.html
http://www.eileenslounge.com/viewtopic.php?p=271035#p271035
https://www.ozgrid.com/forum/index.php?thread/1227920-slicing-a-2d-array/&postID=1239241#post1239241
https://eileenslounge.com/viewtopic.php?p=274367&sid=6b84ff6917c71e849aaeaa281d06fc31#p27436
https://eileenslounge.com/viewtopic.php?f=30&t=34217&p=265384#p265384
Ref
https://www.excelforum.com/excel-new...ml#post4571172
https://www.excelforum.com/tips-and-...ml#post5408376
https://www.excelforum.com/excel-pro...dim-array.html
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
https://eileenslounge.com/viewtopic....266691#p266691