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

[DocAElstein]

This is post https://www.excelfox.com/forum/showt...ll=1#post19567
https://www.excelfox.com/forum/showthread.php/2837-Appendix-Thread-App-Index-Rws()-Clms()-Majic-code-line-Codings-for-other-Threads-Tables-etc-)?p=19567&viewfull=1#post19567
https://www.excelfox.com/forum/showt...ge53#post19567
https://www.excelfox.com/forum/showthread.php/2837-Appendix-Thread-App-Index-Rws()-Clms()-Majic-code-line-Codings-for-other-Threads-Tables-etc-)/page53#post19567

---

Some further explaining notes for this Thread answer
https://eileenslounge.com/viewtopic.php?f=27&t=40371
https://eileenslounge.com/viewtopic....312533#p312533

Part1
This is what the first row Header Unique ID looks like
_____ Workbook: Schedule re-alignment.xls ( Using Excel 2013 32 bit )

Unique ID	694	697
Date	01.04.2019	01.04.2019
Units	33	66

Worksheet: Part1




But we want this as the wanted results

---

We can get the results in the Excel spreadsheet CSE "Array" Type 2 way sort of way like this



We want to do that in VBA in the magical code line way of

arrOut() = Index(arrIn(), Rws(), Clms())

arrIn() is our 694 697
Interception and Implicit Intersection theory tells us that using a single 1 will effectively get the same results as the row of 1’s we want, so all we really need to do is get us that Clms() array

In other words we want to get something like this sort of "vertical" array
1
1
1
1
1
1
1
1
1
1
1
1
1
2
2
2
2
2
2
2
2
2
2
2
2
2


We can do that using the Excel spreadsheet ROW() function to get us a "vertical" array and do a bit of maths on that to get the actual indicia values. There are probably a few ways to do that.
The following way , in words , is the first way I came up with:
Using this, ROW(1:26) gets us the array of the correct dimension but it’s a list of sequential numbers, from 1 to 26
1
2
3

.
.
.
26
If I subtract 1 from those numbers, Row(1:26)-1 , I start at 0 and end in 25 instead. (I need to do that or else the following maths does not quite get the change over from 1 to 2 row correct, - it is out of step by a row)
( This sort of mathematical step works because again of Interception and Implicit Intersection theory – in this case the single 1 will work as if we had 26 1’s in the same "vertical" alignment as what Row(1:26) gives us )
If I divide the 0 to 25 numbers all by 13, (Row(1:26)-1)/13 , then the first half of the numbers are a number between 0 and 1 and the second half of numbers are a number between 1 and 2
If I add 1 to all those numbers, ((Row(1:26)-1)/13)+1 , then the first half of the numbers are a number between 1 and 2 and the second half of numbers are a number between 2 and 3.
If I take the integers of the numbers so far, INT(((Row(1:26)-1)/13)+1) , I end up with what I finally want: I end up with all 1s for the first half, and all 2s for the second half.

Inside VBA, that would look like this
Evaluate("INT(((Row(1:26)-1)/13)+1)")
That should work, and does often, but…
For Excel from about version 2016 that is OK. Earlier versions sometimes need a little trick to make sure some functions ( the INT in this case ) give us the full array of results rather than the first value. This trick is often a good one
IF({1}, ______ )

So finally for good backward Excel version compatibility we would have
Evaluate("IF({1}, INT(((Row(1:26)-1)/13) )+1)")

Here is a final macro

 Sub makrooPart1()
Dim Lr As Long, HdCnt As Long
 Let HdCnt = 2
 Let Lr = Cells(Rows.Count, 1).End(xlUp).Row
Dim NoHds As Long: Let NoHds = (Lr - 1) / HdCnt

' Clms()
Dim Clms() As Variant
 Let Clms() = Evaluate("IF({1},INT(((Row(1:26)-1)/13))+1)")
 Let Clms() = Evaluate("IF({1},INT(((Row(1:" & Lr - 1 & ")-1)/" & NoHds & "))+1)")

' arrOut()=Index(arrIn(),Rws(),Clms())
Dim arrOut() As Variant
 Let arrOut() = Application.Index(Range("B1:C1"), 1, Clms())
 Let Range("O2:O27") = arrOut()
 Let Range("O2:O27") = Application.Index(Range("B1:C1"), 1, Evaluate("IF({1},INT(((Row(1:26)-1)/13))+1)"))
 Let Range("O2:O27") = Application.Index(Range("B1:C1"), 1, Evaluate("IF({1},INT(((Row(1:" & Lr - 1 & ")-1)/" & NoHds & "))+1)"))
End Sub

It should give the results as demoed in Worksheet Part1 in the uploaded file.