Item way of thinking as alternative to conventional row column looping.

Printable View

03-01-2022, 12:10 AM
DocAElstein

Item way of thinking as alternative to conventional row column looping.

URL links to here: This is post: https://www.excelfox.com/forum/showt...1D-arr2D-arr1D
https://www.excelfox.com/forum/showthread.php/2789-Item-way-of-thinking-as-alternative-to-conventional-row-column-looping-arr1D-arr2D-arr1D?p=16456&viewfull=1#post16456
https://www.excelfox.com/forum/showt...ll=1#post16456
https://excelfox.com/forum/showthread.php/2789-Item-way-of-thinking-as-alternative-to-conventional-row-column-looping
https://excelfox.com/forum/showthrea...ping#post16456
https://excelfox.com/forum/showthread.php/2789-Item-way-of-thinking-as-alternative-to-conventional-row-column-looping#post16456

Computers v Humans

It can help a lot in understanding and producing advanced VBA solutions in thinking about the difference in
_ Computers, and
_ how we prefer to see things.

A computer is just a long string of characters. Very quickly. We find that difficult.
Excel makes it a bit easier for us to look at: it puts a bit of it on a row, notches that up ( "Line feed" ) , goes back ( "Carriage Return" ), and puts the next bit on the next row etc. Then it lets us see it all on a screen. (Or it is updating each bit so us stupid humans quickly that we think it’s there all the time. Some of my better solutions are based on tapping into that fast updating to do something most efficiently, making it seem like something across a large range or table is done all at once).

Its handy to have in your tool box at hand a way to move easily between the two ,
_ the long single string, and
_ the 2 Dimensional display.

That is what this series of Blogs/ This Thread, is all about

Thread Contents
It’s easy to get mixed up as we are mixing up Excel Spreadsheet and VBA things, so here is an overview to help get quicker to things you may be looking for.

Post #2 ( and Post #4 ) Item way of thinking as alternative to conventional row column looping in VBA
The traditional way to loop all cells is 2 loops, one inside the other, going for example along the columns for a row, then next row etc., pseudo
__ For Each row, r
____ For Each column, c, in the row
_____Cell (r, c)
____ Next column
__ Next row
All that is done in and with, VBA. We are looping through all cells
Post #2 primarily talks about an alternative single loop idea. This is possible as many things in Excel are also ordered in a sequential single item number, (sometimes referred to as index, to help confuse things later). This is done in a specific way for a range of cells, so because we know what this specific way is, it this allows to alternatively loop the item nubers instead, calculating if needed the row and column number, pseudo like
__For all items
___Cell(rcalculated from item number , ccalculated from item number )
You see, for each item number we are able to determine/ calculate the row and column associated with it. Each cell is assigned an item number, so looping the item numbers we can also be looping all cells, since we can calculate which cell is by the calculated row and column number for that item number.
__Next item
All that is done in and with, VBA
This Item way of thinking is generally useful to know about, and can have practice advantages, but it’s unlikely to have great performance advantages. If for example we have a two row, 3 column range, the convention of the item number is like
1 2 3
4 5 6
, then the conventional way does 3 columns twice and the item way does 6 items, so we effectively loop 6 times in either way. Simple example: For 6 items, 1 2 3 4 5 6 , then at each item loop, I needed to get at each item number loop, ( 1 to 6 ),
_ the row indices of 1 1 1 2 2 2 and
_ the column of where those things were was 1 2 3 1 2 3

The main purpose of Post #2 and # 4 is to calculate those row and column indicies

Post #5 and Post #6 and Post #7 and Post #8 VBA Range Evaluate(" ") ( 2Darray() to 1DArray() )
The relevance to this Thread is a bit shirttail, and this subject is tackled many tines elsewhere. It fits in here as
_ we share similar mathematics to the last post
_ It fits well to the concept of a different, arguably often better, approach to looping/ reduced looping, - in this case, .. none!!
We tap into the way Excel tries to put something almost simultaneously in a 2 dimensional picture that to us humans looks like a static immediate picture.
Taking again the 2 row 3 column example we take an array of numbers in an item type list, { 1 2 3 4 5 6 } and get the corresponding array
_ for the rows { 1 1 1 2 2 2 } , and
_ the columns { 1 2 3 1 2 3 }
We can mess about with Excel things and functions that more usually take single values, and give them arrays of values instead. We are not technically doing then what mathematically is strictly array calculations**, although we are doing something similar to get an array of results instead of a single value result. (** It’s often called array calculations which can be a bit misleading)
Post #5 is just one example of this VBA Range Evaluate(" ") idea, but is included here as it happens to use very similar mathematics to Post #2 and Post #4
03-01-2022, 12:10 AM
DocAElstein

Item way of thinking as alternative to conventional row column looping.

URL links to here: This is post: https://www.excelfox.com/forum/showt...ll=1#post23384
https://www.excelfox.com/forum/showthread.php/2789-Item-way-of-thinking-as-alternative-to-conventional-row-column-looping-arr1D-arr2D-arr1D?p=23384&viewfull=1#post23384
https://www.excelfox.com/forum/showt...rr1D#post23384
https://www.excelfox.com/forum/showthread.php/2789-Item-way-of-thinking-as-alternative-to-conventional-row-column-looping-arr1D-arr2D-arr1D#post23384

Item way of thinking as alternative to conventional row column looping

Current conventional way of thinking
This Blog post assumes that you have some basic Excel VBA programming experience, and that you are familiar with this general idea used typically when filling or getting data from cells
__For Rw = 1 To maximumRows
____For Clm = 1 To maximumColumns
_____( Rw, Clm )
____Next Clm
__ Next Rw
This basic process you should make sure you fully understand before reading further.
This Blog post suggest a slightly different way about thinking about that process

Introduction
Excel Spreadsheets can be turned into pretty pictures, computer graphics and games and “dashboards”.
But they are usually not the best at doing those things.
They are best for doing things with 2 dimensional “tables” of data.
They bridge the gap between
_ dead stores of data or long fast streams of string data (Computers),
and
_ relatively slow and static living humans interacting with them

I expect we are not a long way from interacting in a 3 dimensional way, but it will settle down, I think, to be useful in a limited way. More than 3 dimensions are difficult for humans to react easily with.
A 2 dimensional desktop is particularly convenient for us.
Smartphones are a trend that I think might go back a bit to being just glorified telephones, walkmans, live cameras and simple computer games.
I personally would say, live actively physically in 3 dimensions, forget smart phones, and sit down occasionally and watch the telly or a 2 dimensional PC or laptop to get some stuff done in your computer corner.

We are basically talking in this Thread about organising a string of data travelling at the speed of light into a static line or row of the data, then carrying the rest of the data back, feeding in another row or line or “going down”, then doing the same again.

I think for Excel formulas and VBA we should not take the core of the thinking much further than this.
For furthering the study and manipulation of static table data something else , another concept, such as Power Query / Get and Transform might be more appropriate.

Looping along columns for every row The conventional way
__For Rw = 1 To maximumRows
____For Clm = 1 To maximumColumns
_____do stuff for each Cell( Rw, Clm )
____Next Clm
__ Next Rw
This is what we have got used to, from reading books, to the workings of old printers, to how a screen is filed with data. It is an efficient and logical consequence that a spreadsheet, or a rectangular range in a spreadsheet, is filled and otherwise updated in the same way.
Usually we look at a cell when doing formula calculations, but if we increase the exposed area during a screen update, we can get an efficient, almost simultaneous, calculation across an entire range. This latter is the basis of the workings of the so called array formulas, ( “CSE” stuff ). We will come on to that in the next post

Looping the Items
Many things in VBA, and computers generally, are actually ordered somewhere by something similar to a unique item number , 1 2 3 4 5 6 … etc. ( Note: in some computer conventions the first may start at 0 so we have 0 1 2 3 4 5… etc. )
Conventionally, the Range Item numbers are also so organised, and further follow the generally accepted convention of
Across
_____<- back (Carriage Return)
Down (Line Feed)
Across
_____<- back (Carriage Return)
Down (Line Feed)
………etc.

( Conventionally in computer stream strings we may refer to those
backs as carriage returns, vbCr ,
and
the downs as line feeds, vbLf.
These may be referred to as “invisible characters”, as we do not typically see them, but they must be there in the electronic string stream to indicate the start of a new line or new row )

For example for a 2 row x 3 column range, this is where the item numbers are/ what cells the item number refers to
1 2 3 vbCr & vbLf
4 5 6
Furthermore, for many things that do not have such an item number they may have a consistent order such that we can assume or assign some simple count mechanism to give them an index that we can use in a similar way to the Item number

The crux of my suggestion in this Thread is to think of the basic process shown at the start of this Blog, in a slightly different way.
A couple of reasons I can think of for doing this
_ It may be helpful to think back to the more fundamental single string idea of what computers are, as the table type arrangement is only an interface for us, and is less representative of what is actually going on in a computer
_ In some situations the alternative looping may have some advantages. For example we would not be limited to a rectangular range. (See Post #3)

Some simple required maths
What we want to do is this sort of thing as an alternative to the basic process
__For Itm = 1 To maximumItems
___( Row, Column)
__Next Itm
Before looking at the actual solution, lets simply put in words a simple solution idea, and consider again a 2 row, 3 column range
1 2 3
4 5 6
In this case, our maximumRows will be 2 and the maximumColumns will be 3
We have those sequentially numbers, and we want to convert then to row and column indices that would give us that spreadsheet range.

Row Number from Item Number
If we were to divide the actual item number by the maximumColumns , 3 , and take the integer of the result we would almost be there. But that would actually give us
0 0 1
1 1 2
If we were to first subtract 1 from the Item number, then do the same again, divide that modified number by the maximumColumns , 3, and take the integer of the result we would have,
Integer ( ( ItmNumber -1 ) / maximumColumns ) =
0 0 0
1 1 1
So we now see that we only have to add 1 to the final results and we have the row number what we want.
RowNumber = [ Integer ( ( ItmNumber -1 ) / maximumColumns ) ] + 1
1 1 1
2 2 2

Column Number from Item Number
This is slightly more tricky. But not much. I do this second as we need some of the ideas from the previous maths.
An observation:
To get the column number from the item number, in the second row the item number needs to be reduce by 1 x maximumColumns , and
if we had a third row the item number would need to be reduced by 2 x maximumColumns, (and
for the first row, the item number needs to be reduce by nothing , so by 0 x maximumColumns ) … and so on.
Note also, that it also fits this same logic to apply a reduction of 0 x maximumColumns to the first row.
So we are looking for a multiplication factor based on the Item number which gives us that sort of number sequence like 0 0 0 ; 1 1 1 ; 2 2 2 etc. sequence
We can see from one of the stages in the maths from the last section where we determined the row from the item number, that we did actually already have it:
It was at this stage: -
__________________Integer ( ( ItmNumber -1 ) / maximumColumns )
___=___0 0 0
_______1 1 1
and that would have further gone on for more rows to
0 0 0 ; 1 1 1 ; 2 2 2 ; 3 3 3 … etc.

So we can see one formula possibility:
ItemNumber - [ Integer ( ( ItmNumber -1 ) / maximumColumns ) X maximumColumns ]
This would result in our required column Indicies of
1 2 3
1 2 3

_.______

Simple VBA macro examples

Code:

Sub ItmNumbersLoopToRng() Dim Ws1 As Worksheet: Set Ws1 = ThisWorkbook.Worksheets("Sheet1") Dim ItmIndx As Long Dim maxClms As Long, maxRws As Long: Let maxClms = 3: Let maxRws = 2 For ItmIndx = 1 To 5 Dim Rw As Long, Clm As Long Let Rw = Int((ItmIndx - 1) / maxClms) + 1 Let Clm = ItmIndx - (Int((ItmIndx - 1) / maxClms) * maxClms) Let Ws1.Cells.Item(Rw, Clm).Value = " (" & Rw & ", " & Clm & ")" Next ItmIndx End Sub Sub ItmNumbersLoopToRng2() Dim Ws1 As Worksheet: Set Ws1 = ThisWorkbook.Worksheets("Sheet1") Dim ItmIndx As Long Dim maxClms As Long, maxRws As Long: Let maxClms = 3: Let maxRws = 2 For ItmIndx = 1 To 5 ' Dim Rw As Long, Clm As Long ' Let Rw = Int((ItmIndx - 1) / maxClms) + 1 ' Let Clm = ItmIndx - (Int((ItmIndx - 1) / maxClms) * maxClms) Let Ws1.Cells.Item(Int((ItmIndx - 1) / maxClms) + 1, ItmIndx - (Int((ItmIndx - 1) / maxClms) * maxClms)).Value = " (" & Int((ItmIndx - 1) / maxClms) + 1 & ", " & ItmIndx - (Int((ItmIndx - 1) / maxClms) * maxClms) & ")" Next ItmIndx End Sub

__Result:_____
_____ Workbook: OekyDoekyAmelynn.xls ( Using Excel 2007 32 bit )

_Row\^Col
A

B

C

D

1
(1, 1) (1, 2) (1, 3)

2
(2, 1) (2, 2)

3

Worksheet: Sheet1
_.____
03-01-2022, 12:10 AM
DocAElstein

Item way of thinking as alternative to conventional row column looping.

This is post
https://www.excelfox.com/forum/showt...ll=1#post16457
https://www.excelfox.com/forum/showt...ping#post16457

There is one interesting observation already that may help us understand some of the secret workings of Excel:
Note that we have not needed to use the parameter of maximumRows
Now, Consider a range object of 3 columns x 2 rows anywhere in a spreadsheet
x x x vbCr & vbLf
x x x
Reasonably we would say that has 6 items, and conventionally we have, and we could confirm by experiment, that we have item 6 as shown here, X:
x x x vbCr & vbLf
x x X
What is somewhat surprising perhaps, is that we can go on to reference any further item without causing any errors, and we remain in the maximum columns restriction but seem to have no limit, ( within reason ), in the rows.
For example, referring to item 12, would get us here X:
x x x vbCr & vbLf
x x x vbCr & vbLf
x x x vbCr & vbLf
x x X

This could be by design, or might just be an accident due to some internal calculations having a similar form to those I have suggested and discussed, - those that do not have any consideration of maximum row

I intend adding some further post on this thread later to discuss the ideas and develop them further.

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwTUdEgR4bdt6crKXF4AaABAg. 9xmkXGSciKJ9xonTti2sIx
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwWw16qBFX39JCRRm54AaABAg. 9xnskBhPnmb9xoq3mGxu_b
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9xon1p2ImxO
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgybZfNJd3l4FokX3cV4AaABAg. 9xm_ufqOILb9xooIlv5PLY
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9y38bzbSqaG
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgyWm8nL7syjhiHtpBF4AaABAg. 9xmt8i0IsEr9y3FT9Y9FeM
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg. 9xhyRrsUUOM9xpn-GDkL3o
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNe_XC-jK
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNPOdiDuv
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgN7AC7wAc
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://eileenslounge.com/viewtopic.php?p=316254#p316254
https://eileenslounge.com/viewtopic.php?p=316280#p316280
https://eileenslounge.com/viewtopic.php?p=315915#p315915
https://eileenslounge.com/viewtopic.php?p=315512#p315512
https://eileenslounge.com/viewtopic.php?p=315744#p315744
https://www.eileenslounge.com/viewtopic.php?p=315512#p315512
https://eileenslounge.com/viewtopic.php?p=315680#p315680
https://eileenslounge.com/viewtopic.php?p=315743#p315743
https://www.eileenslounge.com/viewtopic.php?p=315326#p315326
https://www.eileenslounge.com/viewtopic.php?f=30&t=40752
https://eileenslounge.com/viewtopic.php?p=314950#p314950
https://www.eileenslounge.com/viewtopic.php?p=314940#p314940
https://www.eileenslounge.com/viewtopic.php?p=314926#p314926
https://www.eileenslounge.com/viewtopic.php?p=314920#p314920
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837
https://www.eileenslounge.com/viewtopic.php?f=21&t=40701&p=314836#p314836
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314621#p314621
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
03-03-2022, 04:36 PM
DocAElstein

This is post
https://www.excelfox.com/forum/showt...ll=1#post16457
https://www.excelfox.com/forum/showt...ping#post16457

Slightly different way of getting the row and column number
This post is almost the same as post #2. I am just getting the row and column number for a specific item number in a slightly different and less usual way. This is just helpful as a prelude to post #5, since the mathematics is similar.
We are still looking at VBA looping. But, from within VBA, as an alternative to VBA functions, we use similar Excel spreadsheet functions.
Question - How is this possible?
Answer ( very simplified ) – We may use Excel spreadsheet function, formulas and things , here , in this VBA code thing
____ = Evaluate("here")

For this post I want an alternative, using that , for these bits from post #2
Rw = Int((ItmIndx - 1) / maxClms) + 1
Clm = ItmIndx - (Int((ItmIndx - 1) / maxClms) * maxClms)

In the first Macro below, the solution using Evaluate(" ") is developed from one of the final macros used in Post #2.

Code:

' https://www.excelfox.com/forum/showthread.php/2789-Item-way-of-thinking-as-alternative-to-conventional-row-column-looping?p=16457&viewfull=1#post16457 Sub ItmNumbersLoopToRng4() ' https://www.excelfox.com/forum/showthread.php/2789-Item-way-of-thinking-as-alternative-to-conventional-row-column-looping?p=16457&viewfull=1#post16457 Dim Ws1 As Worksheet: Set Ws1 = ThisWorkbook.Worksheets("Sheet1") Dim ItmIndx As Long Dim maxClms As Long, maxRws As Long: Let maxClms = 3: Let maxRws = 2 For ItmIndx = 1 To 6 Dim Rw As Long, Clm As Long 'Let Rw = Int((ItmIndx - 1) / maxClms) + 1 ' Rw = Int(( ItmIndx - 1) / maxClms ) + 1 Let Rw = Evaluate("INT((" & ItmIndx & " -1) / " & maxClms & ") + 1") 'Let Clm = ItmIndx - (Int((ItmIndx - 1) / maxClms) * maxClms) ' Clm = ItmIndx - (Int(( ItmIndx - 1) / maxClms ) * maxClms ) Let Clm = Evaluate("" & ItmIndx & " - (INT((" & ItmIndx & " - 1) / " & maxClms & ") * " & maxClms & ")") Let Ws1.Cells.Item(Rw, Clm).Value = " (" & Rw & ", " & Clm & ")" Next ItmIndx End Sub

Points to notice.
_ 1 Basically we use the Spreadsheet formula, building it up as a string, as the syntax requires a string. In other words we have like
Dim strEval As String
Let strEval ="here what you would write in a cell"
______ = Eval(strEval)
This allows us to use VBA coding syntax to build up that string from actual text required in a cell, or text/ numbers that may be in a VBA variable.
_ 2a) In this case the integer function in VBA and Excel spreadsheet have the same name and is used in the same way. That is not always the case, and in addition, just to make things confusing, in VBA we might need use a thing that, whilst a VBA thing, has its origin in a spreadsheet/ worksheet thing and so may/ must be referenced in VBA with a preceding Application. or Application.WorksheetFunction.
_2b) In either VBA or Excel we can use any combination of Lowercase or Uppercase characters in a valid function name, such as, in this case, we could use in the spreadsheet or in normal VBA coding InT. In VBA, it would be changed automatically typically to have lowercase in all but the first character, so that example would come out as Int. In a spreadsheet it would be changed to all capital letters, after you typed the Enter key. This change however to the string to be put in the cell inside the Evaluate("___") would not occur because , in simple terms, the entering into a cell is effectively done at run time. However, we conventionally choose to use Uppercase, as I have done, to help avoid confusion and show that it is representing something that would be in a spreadsheet cell
_3a) In VBA syntax, a single space is often put in automatically between things, or it may be required to avoid a syntax error, such that for example typing in a VBA formula such as =1+1 would result in VBA changing it to = 1 + 1 . In a spreadsheet cell , or inside the Evaluate("___") we can often have more or no spaces. But there is an unfortunate limit of how many characters that the string in Evaluate , strEval , can be ( 255 ) , which can be difficult to get over , so it is sensible to get in the habit of keeping it to the minimum, like this

Code:

Sub ItmNumbersLoopToRng4b() ' https://www.excelfox.com/forum/showthread.php/2789-Item-way-of-thinking-as-alternative-to-conventional-row-column-looping?p=16457&viewfull=1#post16457 Dim Ws1 As Worksheet: Set Ws1 = ThisWorkbook.Worksheets("Sheet1") Dim ItmIndx As Long Dim maxClms As Long, maxRws As Long: Let maxClms = 3: Let maxRws = 2 For ItmIndx = 1 To 6 Dim Rw As Long, Clm As Long 'Let Rw = Evaluate("INT((" & ItmIndx & " -1) / " & maxClms & ") + 1") : Debug.Print Len("INT((" & ItmIndx & " -1) / " & maxClms & ") + 1") ' 19 Let Rw = Evaluate("INT((" & ItmIndx & "-1)/" & maxClms & ")+1") : Debug.Print Len("INT((" & ItmIndx & "-1)/" & maxClms & ")+1") ' 14 'Let Clm = Evaluate("" & ItmIndx & " - (INT((" & ItmIndx & " - 1) / " & maxClms & ") * " & maxClms & ")") : Debug.Print Len("" & ItmIndx & " - (INT((" & ItmIndx & " - 1) / " & maxClms & ") * " & maxClms & ")") ' 26 Let Clm = Evaluate("" & ItmIndx & "-(INT((" & ItmIndx & "-1)/" & maxClms & ")*" & maxClms & ")") : Debug.Print Len("" & ItmIndx & " - (INT((" & ItmIndx & "-1)/" & maxClms & ")*" & maxClms & ")") ' 18 Let Ws1.Cells.Item(Rw, Clm).Value = " (" & Rw & ", " & Clm & ")" Next ItmIndx End Sub

The string used by Evaluate has reduced from 19 to 14 and from 26 to 18. What I basically did to the original code lines, was to remove all spaces in the Excel spreadsheet bits used inside the Evaluate(" ")
Note that the length of the VBA parts , things such as " & ItmIndx & " is irrelevant. The important thing as far as the length is concerned is the final string coming out of it. In that example it would be a length of just one character since it would be one of the item numbers, 1 2 3 4 5 or 6
For clarity, in the Evaluate code lines I Have shown the VBA things in dark blue and the Spreadsheet things in dark green , like this
Evaluate("INT((" & ItmIndx & "-1)/" & maxClms & ")+1")
Evaluate("" & ItmIndx & "-(INT((" & ItmIndx & "-1)/" & maxClms & ")*" & maxClms & ")")
You can see that I have removed any spaces in the Excel spreadsheet bits used inside the Evaluate(" ") , as I am able to. (If I tried to change the space in the VBA bits, then either I would get a syntax error, or it would automatically be corrected to the typical syntax of a single space between things. )

Although we have no restriction on the size of the text used for a VBA variable, here is another version, where the main change is just using some other variable names as they might more typically be seen

Code:

Sub ItmNumbersLoopToRng4c() ' https://www.excelfox.com/forum/showthread.php/2789-Item-way-of-thinking-as-alternative-to-conventional-row-column-looping?p=16457&viewfull=1#post16457 Dim Ws1 As Worksheet: Set Ws1 = ThisWorkbook.Worksheets("Sheet1") : Ws1.Cells(1).Resize(2, 3).Clear Dim Ix As Long Dim Lc As Long : Let Lc = 3 'Dim Lr As Long: Let Lr = 2 For Ix = 1 To 6 Dim Rw As Long, Clm As Long Let Rw = Evaluate("INT((" & Ix & "-1)/" & Lc & ")+1") Let Clm = Evaluate("" & Ix & "-(INT((" & Ix & "-1)/" & Lc & ")*" & Lc & ")") : Let Ws1.Cells.Item(Rw, Clm).Value = " (" & Rw & ", " & Clm & ")" Next Ix End Sub

_Row\^Col	A	B	C
1	(1, 1)	(1, 2)	(1, 3)
2	(2, 1)	(2, 2)
3

This is post https://www.excelfox.com/forum/showt...ll=1#post23285
https://www.excelfox.com/forum/showthread.php/2789-Item-way-of-thinking-as-alternative-to-conventional-row-column-looping-arr1D-arr2D-arr1D?p=23285&viewfull=1#post23285
https://www.excelfox.com/forum/showt...rr1D#post23285
https://www.excelfox.com/forum/showthread.php/2789-Item-way-of-thinking-as-alternative-to-conventional-row-column-looping-arr1D-arr2D-arr1D#post23285

The mathematics in this post are similar to those for the theme of Item way of thinking as alternative to conventional row column looping , although we are finally achieving something more in the opposite direction, we are never the less doing something similar:
In simple words, based on the long string character count (Item number ), we get the required row and column indices.

This post is about:

Use in Application.Index with Look Up Rows and Columns Arguments as VBA Arrays,
arrOut() = Application.Index(arrIn(), rws(), clms()),
https://www.excelforum.com/excel-new...vba-arrays.htm
-
1DarrOut() = Application.Index1D(arrIn(), rws(), clms())
arr2D – arr1D

arr2D() – arr1D() using Index Function
The application will only be described briefly here.
Brief Application explanation
We can explain/ demonstrate ( Examples https://www.excelforum.com/excel-new...vba-arrays.htm
https://www.excelfox.com/forum/showt...ex-application)
https://eileenslounge.com/viewtopic....yDoeky#p265384
https://www.excelfox.com/forum/showt...ll=1#post12072 ) that sometime in Excel and VBA, Excel Index functions when supplied arrays as the second/third row/column argument , ( instead of the more typical single row, and single column pair ) will not error, but rather give an output that has the dimensions of the row and column arrays, and the values as determined by the elements of those row and column arrays.

Excel Spreadsheet example
The full discussion of this is discussed in detail at the other links, but summarised, the macro below will give us something like this

a b c d e f =Index( { a b c , { 1 1 1 2 2 2 } , { 1 2 3 1 2 3 } )

d e f }

What is basically going on is the typical "array type order of working" is done in the typical all columns in row, and then next row, etc., but in this case the value returned as defined by indicia in the row and column arrays are all in a single row which consequently gives the final output in a single row, pseudo like

Rows() Columns()

a b c d e f =Index( { a b c , { 1 1 1 2 2 2 } , { 1 2 3 1 2 3 } )

d e f }

row \ column 1 2 3

1, 1 = a 1 ,2=b 1, 3 = c 2, 1=d 2, 2=e 2, 3= f 1 1, 1 = a 1 ,2=b 1, 3 = c

2 2, 1=d 2, 2=e 2, 3= f

In other words, the row and column locator indicia pairs ( co ordinates) are applied sequentially to the main input array ( which is at the first argument of Index) , so as to give the value at that location.

Code:

Sub Arr1DFromArr2D() ' https://www.excelfox.com/forum/showthread.php/2789-Item-way-of-thinking-as-alternative-to-conventional-row-column-looping-arr1D-arr2D-arr1D?p=23285&viewfull=1#post23285 Rem 0 Some text in spreadsheet just to help explanation Range("A30:Y31").Clear Let Range("G30") = " =Index( {": Range("K30") = " , {": Range("K31") = " }": Range("R30") = " } , { ": Range("Y30") = " } ) " ' arrIn() Let Range("H30:J31").FormulaArray = Evaluate("{""a"",""b"",""c"";""d"",""e"",""f""}") ' Row indicies to apply to arrIn() Let Range("L30:Q30").FormulaArray = Evaluate("{1,1,1,2,2,2}") ' Column indicies to apply to arrIn() Let Range("S30:X30").FormulaArray = Evaluate("{1,2,3,1,2,3}") ' Index formula with array indicies for row and column arguments Let Range("A30:F30").FormulaArray = "=INDEX(H30:J31,L30:Q30,S30:X30)" End Sub

Now here’s the thing
The first post followed an order of doing convention like this,
1 2 3
4 5 6
, then if we looped 1 2 3 4 5 6 , ( the item number )
, then the row of where those things are was 1 1 1 2 2 2 and the column of where those things were was 1 2 3 1 2 3
The first post got us the maths to get those numbers from the item number, 1 2 3 4 5 6
We need Excel "Array" type calculations to get
1 1 1 2 2 2 from 1 2 3 4 5 6
and
1 2 3 1 2 3 from 1 2 3 4 5 6
We are relying on Excel somehow almost doing the loop through 1 2 3 4 5 6 pretty well simultaneously. That is what we really mean by Excel "Array" type calculations

So the main work in this Thread is getting those two required arrays
, for the rows, {1,1,1,2,2,2} and
for the columns, {1,2,3,1,2,3}

If you read the last post, then we can see that each pair of values was got (by an Excel spreadsheet formula, constructed as a string for use in VBA via _ Evaluate(" ")
For the rows Evaluate("INT((" & Ix & "-1)/" & Lc & ")+1")
For the columns Evaluate("" & Ix & "-(INT((" & Ix & "-1)/" & Lc & ")*" & Lc & ")")

They get us a single value in each of the 6 loops,
1 1 1 2 2 2
and
1 2 3 1 2 3
using 1 2 3 4 5 6

We want those all in one go, like arrays

{1,2,3,1,2,3}
and
{1,1,1,2,2,2}

So we will do that in the next post…

09-03-2023, 06:52 PM
DocAElstein

This is post #6 https://www.excelfox.com/forum/showt...ll=1#post23393
https://www.excelfox.com/forum/showthread.php/2789-Item-way-of-thinking-as-alternative-to-conventional-row-column-looping?p=23393&viewfull=1#post23393
https://www.excelfox.com/forum/showt...ping#post23393
https://www.excelfox.com/forum/showthread.php/2789-Item-way-of-thinking-as-alternative-to-conventional-row-column-looping#post23393

This follows on from, and is a direct continuation of, the last post. But having a break here is not such a bad idea, as we are going on to do something that can easily know us Humans out of whack and confuse.

Introduction. Some Clarity/ Revision
So to help offset that confusion and summarise the story so far, the story that in total is post #5 and post #6
We are talking about messing with a function that most usually is used like this, pseudo line

Code:

c = Index( {a, b , 2 , 1 ) c, d }

but with the difference that we can get more than one result value out, for example with like

Code:

{d, c} = Index( {a, b , {2, 2} , {2, 1} ) c, d }

In words:
_ in the first case we got the value out of the first Index array argument that was at the co ordinate, 2, 1 , and
_ in the second case we got the values out of the first Index array argument that was at the co ordinates, 2, 2 and 2, 1

Now
_ first off, forget about the first argument array in Index. That is, was, and always will be an array or spreadsheet range, regardless of me and my array things or not. The whole point about what the Index does is to get at things that are in that array ,( grid, range, worksheet , Table, or whatever you want to call it), and, what specifically it gets is based on the co ordinates in the second and third arguments. It is just those row and column arguments that I am messing with
_ secondly, a lot of the last post was talking about how we can make things give us more than the more usual one result via the Excel "array like" calculations. In this post we specifically want to get somehow conveniently the second and third arguments as arrays. Now this is the bit that can confuse. To do that I am further going to use the thing about how we can make things give us more than the more usual one result via the Excel array like calculations. Just because it happens to be a nice convenient way to do it, that’s all. To clarify the point I am trying to make here, consider something different but specifically demonstrating the point I am trying to make.
Say the last post talked about this idea "addition", as opposed to Excel "array like" calculations
6= 1 + 2 + 3
The this post does something like this to get the second and third parts
6= 1 + (1 + 1) + (2 + 1)
So we are like doping an idea twice, that is nested inside that same idea.
In other words, to get the arrays we need in the second and third arguments of Index to get the Excel "array like" calculations we use Excel "array like" calculations.

Back to where we where at the end of the last post
We want to get these arrays,
{1,2,3,1,2,3}
and
{1,1,1,2,2,2}
, and we know we can get them, if we looped the values of Ix of 1 2 3 4 5 6 in these sort of formulas
For the rows Evaluate("INT((" & Ix & "-1)/" & Lc & ")+1")
For the columns Evaluate("" & Ix & "-(INT((" & Ix & "-1)/" & Lc & ")*" & Lc & ")")
( ,where Lc is the number of columns in our range or array first argument of Index)

If you have followed and understood the review/ Introduction, then the following may be very obvious. Instead of
Evaluate("INT((" & Ix & "-1)/" & Lc & ")+1")
, we use this
Evaluate("INT(({1,2,3,4,5,6}-1)/" & Lc & ")+1")
, and instead of
Evaluate("" & Ix & "-(INT((" & Ix & "-1)/" & Lc & ")*" & Lc & ")")
, we use this
Evaluate("{1,2,3,4,5,6}-(INT(({1,2,3,4,5,6}-1)/" & Lc & ")*" & Lc & ")")

So, I think it’s fairly easy to see that the VBA thing that was looped 6 times, " & Ix & " is replaced by a {1,2,3,4,5,6}, which will not be looped. That’s the whole point: An array will be returned, in other words, all 6 values in one go will be returned without looping,( at least without looping in the conventional high level computing sense that we might imagine: An array will be returned with all 6 answers in it. That is because we have tapped into the all columns in a row , then next row, updating done for us Human’s every time something is changed, even in a single cell.

That’s it, basically. Well not quite. Just a couple of things:
…. .next post
09-03-2023, 06:52 PM
DocAElstein

This is post #7 https://www.excelfox.com/forum/showt...ll=1#post23394
https://www.excelfox.com/forum/showthread.php/2789-Item-way-of-thinking-as-alternative-to-conventional-row-column-looping?p=23393&viewfull=1#post23394
https://www.excelfox.com/forum/showt...ping#post23394
https://www.excelfox.com/forum/showthread.php/2789-Item-way-of-thinking-as-alternative-to-conventional-row-column-looping#post23394

Almost finished. Just a couple of things:
_1) ____ IF({1},This)
It’s a bit out of the scope of this Thread, ( see here for more details, https://www.excelfox.com/forum/showt...eturning-array ) . It seems that sometimes things we expect to return us an array of results don’t. No one knows for sure what is going on, least of all anyone at Microsoft. It could be to do with range references being returned rather than values, and that if then a value is required, then the top left range reference is first given, and then the value from that will be given. That is just a Laymen gut feeling. In any case, something somewhere in a dependency chain of events is preventing an array being passed. Usually fiddling around with something that has no direct effect on a result, but that as by product passes an array due to the changed dependency chain route, solves the problem. It is rare that simply multiplying by an array or doing some other simple mathematical extra bit solves the problem. It seems that functions who are generally wired to return a range object ( or reference ) have something in them that may go one way of the other, returning an array of values or a reference depending on how they are used. It's often a bit empirical, or based on some gut feeling of mine. This is one , or slight variations of it, that seems to often work: Say This is what annoyingly is not giving you back the array of results, and most likely just the first one instead. The the trick that usually works is
____ IF({1},This)
As we would expect, generally This and IF({1},This) give the same result, since the general way the IF in the form used here works is pseudo like
__ IF ( if this is true , do this ) ____ ' ( if it's not true then a False is returned
In the Excel / computer way of thinking, something like 1 or {1} is true. So do this is just done as do this would be. But somehow, in a way no one understands for sure, the use of IF({1},here) , or some variation of it causes the thing embedded in it here to give an array of values in a situation when it annoyingly did not on it's own.
So here we are so far:

Code:

Sub TooDarrayTo1Darray() ' https://www.excelfox.com/forum/showthread.php/2789-Item-way-of-thinking-as-alternative-to-conventional-row-column-looping?p=23393&viewfull=1#post23393 https://www.excelfox.com/forum/showthread.php/2789-Item-way-of-thinking-as-alternative-to-conventional-row-column-looping?p=23285&viewfull=1#post23285 Dim Ws1 As Worksheet: Set Ws1 = ThisWorkbook.Worksheets("Sheet1"): Ws1.Cells(1).Resize(2, 3).Clear Dim Ix As Long Dim Lc As Long: Let Lc = 3 'Dim Lr As Long: Let Lr = 2 ' For Ix = 1 To 6 Rem 1 Rows ' Dim Rw As Long, Clm As Long ' Let Rw = Evaluate("INT((" & Ix & "-1)/" & Lc & ")+1") Dim Rws() As Variant, vTemp As Variant ' Let Rws() = Evaluate("INT(({1,2,3,4,5,6}-1)/" & Lc & ")+1") ' Errror Type mismatch - a single value is returned Let vTemp = Evaluate("INT(({1,2,3,4,5,6}-1)/" & Lc & ")+1") ' 1 Let vTemp = Evaluate("{1}*INT(({1,2,3,4,5,6}-1)/" & Lc & ")+1") ' 1 Let vTemp = Evaluate("IF({1},INT(({1,2,3,4,5,6}-1)/" & Lc & ")+1)") ' {1,1,1,2,2,2,} Watch : + : vTemp : : Variant/Variant(1 to 6) Let vTemp = Evaluate("T(IF({1},INT(({1,2,3,4,5,6}-1)/" & Lc & ")+1))") ' "" Watch : : vTemp : "" : Variant/String Let vTemp = Evaluate("N(IF({1},INT(({1,2,3,4,5,6}-1)/" & Lc & ")+1))") ' 1 Watch : : vTemp : 1 : Variant/Double Let vTemp = Evaluate("INT((IF({1},{1,2,3,4,5,6})-1)/" & Lc & ")+1") ' 1 Let vTemp = Evaluate("INT((N(IF({1},{1,2,3,4,5,6}))-1)/" & Lc & ")+1") ' 1 Let Rws() = Evaluate("IF({1},INT(({1,2,3,4,5,6}-1)/" & Lc & ")+1)") ' {1,1,1,2,2,2,} Rem 2 Columns ' Let Clm = Evaluate("" & Ix & "-(INT((" & Ix & "-1)/" & Lc & ")*" & Lc & ")"): Dim Clms() As Variant ' Let Clms() = Evaluate("{1,2,3,4,5,6}-(INT(({1,2,3,4,5,6}-1)/" & Lc & ")*" & Lc & ")") ' Error Type mismatch a songlöe value is returned Let vTemp = Evaluate("{1,2,3,4,5,6}-(INT(({1,2,3,4,5,6}-1)/" & Lc & ")*" & Lc & ")") ' 1 Let vTemp = Evaluate("{1,2,3,4,5,6}-IF({1},(INT(({1,2,3,4,5,6}-1)/" & Lc & "))*" & Lc & ")") ' {1,2,3,1,2,3} Let Clms() = Evaluate("{1,2,3,4,5,6}-IF({1},(INT(({1,2,3,4,5,6}-1)/" & Lc & "))*" & Lc & ")") ' {1,2,3,1,2,3} ' Let Ws1.Cells.Item(Rw, Clm).Value = " (" & Rw & ", " & Clm & ")" ' Next Ix End Sub

_2) Get {1,2,3,4,5,6} dynamically
Of course, for convenience of explanation, we are using a specific size range, ( 2 rows and 3 columns ). We want generally a solution for any range of known size, (columns and row number). We are half the way there as in the last coding we had used Lc, the column size. The only thing left to be got dynamically is {1,2,3,4,5,6}
A convenient way to get this is via the excel COLUMN function. This is a very useful function for getting arrays of 1 dimension like the one we want. There is no similar function in VBA, but no matter, we can once again make use of the Evaluate(" ") , like this, to get the array we want
Evaluate("COLUMN(A:F)")
This gives us the column numbers as an array, {1,2,3,4,5,6} , just as we want. The actual amount of numbers, 6 in this case, is the number of items, in other words the number of cells in the range, the Item.Count, the Rng.Cells.Count etc. etc.
Getting that number is very simple, - 6 in our case, and generally it will be the number of columns x the number of rows, Lc x Lr. Inconveniently we want the column Letter for that number. There are a few ways to get at this, ( ) . I would generally prefer to make a custom function for that, and call that. But for the purposes of completeness and independence of a compact solution, the "address way" will be done: The Cells.Item(r, c) property , ( can be, and usually is, written shortened as Cells(r, c) – Excel will often guess in this case correctly what you mean ) of any range or worksheet# will return for its Address property a text string, for our example of $F$1 for this Cells.Item(1, 6).Address or this shortened version Cells(1, 6).Address.
We can
_ omit any leading qualifying range or worksheet in this case, since it will default to some range or worksheet, and the result will be the same#
_ Use any row number, - I used 1 in that example for no particular reason
The important thing is the column number, and we can use in VBA that last bit like this
Cells(1, Lr + Lc).Address
We can do some string manipulation to get the column letter out, such as by Splitting by the $ to a get a 1 dimensional array.
( https://learn.microsoft.com/en-us/of...split-function Split function returns a zero-based, one-dimensional array containing a specified number of substrings.
That 1 dimensional array should have 3 elements, an empty first, for our example the second element will be F , and the third element 1. So we just take the second element, noting that such a one dimensional array has a base (first element indicia ) of 0.
So we would have this sort of result
Split(Expression:=Cells(1, Lc * Lr).Address, Delimiter:="$", Limit:=3, Compare:=vbBinaryCompare)(0) = ""
Split(Expression:=Cells(1, Lc * Lr).Address, Delimiter:="$", Limit:=3, Compare:=vbBinaryCompare)(1) = "F"
Split(Expression:=Cells(1, Lc * Lr).Address, Delimiter:="$", Limit:=3, Compare:=vbBinaryCompare)(2) = "1"
We can simplify the second of those like this
Split(Cells(1, Lc * Lr).Address, "$")(1)

Rem 4 Final Row and Column arrays
So here is the Final rows() and columns() arrays

Code:

Rem 4 Final Row and Column arrays Let Rws() = Evaluate("IF({1},INT(({1,2,3,4,5,6}-1)/" & Lc & ")+1)") Let Rws() = Evaluate("IF({1},INT((COLUMN(A:" & Split(Cells(1, Lc * Lr).Address, "$")(1) & ")-1)/" & Lc & ")+1)") Let Clms() = Evaluate("{1,2,3,4,5,6}-IF({1},(INT(({1,2,3,4,5,6}-1)/" & Lc & "))*" & Lc & ")") ' {1,2,3,1,2,3} Let Clms() = Evaluate("COLUMN(A:" & Split(Cells(1, Lc * Lr).Address, "$")(1) & ")-IF({1},(INT((COLUMN(A:" & Split(Cells(1, Lc * Lr).Address, "$")(1) & ")-1)/" & Lc & "))*" & Lc & ")") ' {1,2,3,1,2,3}

Code so far

Code:

Sub TooDarrayTo1Darray3() ' https://www.excelfox.com/forum/showthread.php/2789-Item-way-of-thinking-as-alternative-to-conventional-row-column-looping?p=23394&viewfull=1#post23394 Dim Ws1 As Worksheet: Set Ws1 = ThisWorkbook.Worksheets("Sheet1"): Ws1.Cells(1).Resize(2, 3).Clear Dim Ix As Long Dim Lc As Long: Let Lc = 3 Dim Lr As Long: Let Lr = 2 Rem 1 Rows Dim Rws() As Variant, vTemp As Variant ' Let Rw = Evaluate("INT((" & Ix & "-1)/" & Lc & ")+1") Let Rws() = Evaluate("IF({1},INT(({1,2,3,4,5,6}-1)/" & Lc & ")+1)") ' {1,1,1,2,2,2,} Rem 2 Columns Dim Clms() As Variant ' Let Clm = Evaluate("" & Ix & "-(INT((" & Ix & "-1)/" & Lc & ")*" & Lc & ")"): Let Clms() = Evaluate("{1,2,3,4,5,6}-IF({1},(INT(({1,2,3,4,5,6}-1)/" & Lc & "))*" & Lc & ")") ' {1,2,3,1,2,3} Rem 3 {1,2,3,4,5,6} Let vTemp = Evaluate("COLUMN(A:F)") Let vTemp = Evaluate("COLUMN(A:" & Split(Expression:=Cells(1, Lc * Lr).Address, Delimiter:="$", Limit:=3, Compare:=vbBinaryCompare)(1) & ")") ' {1,2,3,4,5,6} Let vTemp = Evaluate("COLUMN(A:" & Split(Cells(1, Lc * Lr).Address, "$")(1) & ")") ' {1,2,3,4,5,6} ' we want finally this inside existing Evaluate(" ") bits COLUMN(A:" & Split(Cells(1, Lc * Lr).Address, "$")(1) & ") Rem 4 Final Row and Column arrays Let Rws() = Evaluate("IF({1},INT(({1,2,3,4,5,6}-1)/" & Lc & ")+1)") Let Rws() = Evaluate("IF({1},INT((COLUMN(A:" & Split(Cells(1, Lc * Lr).Address, "$")(1) & ")-1)/" & Lc & ")+1)") Let Clms() = Evaluate("{1,2,3,4,5,6}-IF({1},(INT(({1,2,3,4,5,6}-1)/" & Lc & "))*" & Lc & ")") ' {1,2,3,1,2,3} Let Clms() = Evaluate("COLUMN(A:" & Split(Cells(1, Lc * Lr).Address, "$")(1) & ")-IF({1},(INT((COLUMN(A:" & Split(Cells(1, Lc * Lr).Address, "$")(1) & ")-1)/" & Lc & "))*" & Lc & ")") ' {1,2,3,1,2,3} End Sub

So we are almost there. As we did a lot, and it was easy to get confused, we will do the last bit in another Post, and summarise at the start
09-05-2023, 02:20 PM
DocAElstein

This is post https://www.excelfox.com/forum/showt...ll=1#post23287
https://www.excelfox.com/forum/showthread.php/2789-Item-way-of-thinking-as-alternative-to-conventional-row-column-looping-arr1D-arr2D-arr1D?p=23287&viewfull=1#post23287

Use in Application.Index with Look Up Rows and Columns Arguments as VBA Arrays,
arrOut() = Application.Index(arrIn(), rws(), clms()),
https://www.excelforum.com/excel-new...vba-arrays.htm
-
2DarrOut() = Application.Index1D(arrIn(), rws(), clms())
arr1D - arr2D

arr1D() – arr2D() using Index Function
This application idea is very relevant even if at first glance not quite so…
The application will only be described briefly here.
Brief Application explanation
We can explain/ demonstrate ( Examples https://www.excelforum.com/excel-new...vba-arrays.htm
https://www.excelfox.com/forum/showt...ex-application)
https://eileenslounge.com/viewtopic....yDoeky#p265384
https://www.excelfox.com/forum/showt...ll=1#post12072 ) that sometime in Excel and VBA, Excel Index functions when supplied arrays as the second/third row/column argument , ( instead of the more typical single row, and single column pair ) will not error, but rather give an output that has the dimensions of the row and column arrays, and the values as determined by the elements of those row and column arrays.

Excel Spreadsheet example
The full discussion of this is discussed in detail at the other links, but summarised, the macro below will give us something like this
a b = Index( { a b c d } , { 1 1 , { 1 2 )

c d 1 1 } 3 4 }

In other words the 1x4 1 dimensional array, (or 1 "Row” array),
{a, b, c, d}
, is converted to a 2x2 2 dimensional array,
{a, b
c, d}
The final dimension is determined by the dimensions of the row and column element arrays, and the final values are determined by looking at the co ordinate pairs applied to the 1 D source / input array , in the typical convention of along all columns in a row , then the same for the next row and so on. The position in the final array mirrors the sequence
(1, 1)=a

(1, 2)=b

(1, 3)=c

(1, 4)=d

https://i.postimg.cc/14bX6mNF/Index-...-to-arr2-D.jpg

Code:

Sub ArraysInExcel2() ' https://www.excelfox.com/forum/showthread.php/2789-Item-way-of-thinking-as-alternative-to-conventional-row-column-looping-arr1D-arr2D-arr1D?p=23285&viewfull=1#post23285 Cells.ClearContents Range("C1").Value = " = Index( {": Range("H1").Value = "} , { ": Range("K1").Value = " , { ": Range("K2").Value = "} ": Range("N1").Value = " )": Range("N2").Value = "} " ' Some formating to help explanation of Pseudo coding Range("D1:G1,I1:J2,L1:M2,A1:B2").ClearContents ' data range Let Range("D1:G1").FormulaArray = "={""a"",""b"",""c"",""d""}" Debug.Print Range("D1:G1").FormulaArray ' =INDEX(D1:G1,I1:J2,L1:M2) Debug.Print Range("D1:G1").Formula ' =INDEX(D1:G1,I1:J2,L1:M2) Debug.Print Range("D1:G1").FormulaLocal ' =INDEX(D1:G1;I1:J2;L1:M2) ' Rws() Clms() ItemIndicies Let Range("I1:J2").FormulaArray = "={1, 1;1, 1}" Debug.Print Range("I1:J2").FormulaArray ' =INDEX(D1:G1,I1:J2,L1:M2) Debug.Print Range("I1:J2").Formula ' =INDEX(D1:G1,I1:J2,L1:M2) Debug.Print Range("I1:J2").FormulaLocal ' =INDEX(D1:G1;I1:J2;L1:M2) Let Range("L1:M2").FormulaArray = "={1, 2;3, 4}" Debug.Print Range("L1:M2").FormulaArray ' =INDEX(D1:G1,I1:J2,L1:M2) Debug.Print Range("L1:M2").Formula ' =INDEX(D1:G1,I1:J2,L1:M2) Debug.Print Range("L1:M2").FormulaLocal ' =INDEX(D1:G1;I1:J2;L1:M2) ' Index array formulas Let Range("A1:B2").FormulaArray = "=INDEX(D1:G1,I1:J2,L1:M2)" Debug.Print Range("A1").FormulaArray ' =INDEX(D1:G1,I1:J2,L1:M2) Debug.Print Range("A1").Formula ' =INDEX(D1:G1,I1:J2,L1:M2) Debug.Print Range("A1").FormulaLocal ' =INDEX(D1:G1;I1:J2;L1:M2) Let Range("A1:B2").FormulaArray = "=INDEX({""a"",""b"",""c"",""d""},{1, 1;1, 1},{1, 2;3, 4})" ' https://i.postimg.cc/dtVybDs5/Index-arr1-D-to-arr2-D.jpg Debug.Print Range("A1").FormulaArray ' =INDEX({"a","b","c","d"},{1,1;1,1},{1,2;3,4}) Debug.Print Range("A1").Formula ' =INDEX({"a","b","c","d"},{1,1;1,1},{1,2;3,4}) Debug.Print Range("A1").FormulaLocal ' =INDEX({"a"."b"."c"."d"};{1.1;1.1};{1.2;3.4}) ' https://i.postimg.cc/14bX6mNF/Index-...-to-arr2-D.jpg End Sub

The demonstration is principally an Excel spreadsheet example, and we could do it all manually, entering in the various formulas by hand. However this is often tricky, since anything involving separator characters and the such ( . , ; / \ ) are often typical to get correct syntaxly since there are often different land conventions and / or various setting involving these characters can affect the locally used syntax.
VBA is however consistent: a column separator in an array is shown as a , and a row separator as a ; so {a, b ; c, d ; e, f} ), is a 2 column x 3 row array,
{a, b
c, d
e, f}
So it is often a good idea to use short simple coding to put values and formulas in. Excel may change their form appropriately to suit the local syntax. In the 'green comments , the Debug.Print results that you get may vary for the case of the .FormulaLocal case

For the relevance to this thread we concentrate on getting the third column argument. ( Because of interception and intersection we can reduce the second row argument to just a single 1 , since Excel effectively extends that single value to the missing elements to extend it effectively to a 2x2 array with all values of the single value, 1 in this case)

Code:

Sub ThirdColumnArgument() Dim vTemp As Variant Let Range("A4:C5").FormulaArray = "=ROW(1:2)*COLUMN(A:C)" Let Range("A4:C5").FormulaArray = "=(ROW(1:2)/ROW(1:2))*COLUMN(A:C)" Let Range("A4:C5").FormulaArray = "=1*COLUMN(A:C)" Let Range("A4:C5").FormulaArray = "=COLUMN(A:C)" Let Range("A4:C5").FormulaArray = "=COLUMN(A:C)+((Row(1:2)-1)*3)" https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA https://www.youtube.com/watch?v=oVb1RfcSHLM&lc=UgwTq-jZlZLnLQ5VB8Z4AaABAg.9Hroz-OyWog9tYjSMc1qjA https://www.youtube.com/watch?v=0pbsf6sox34&lc=Ugxp9JFvvejnqA68W1t4AaABAg https://www.youtube.com/watch?v=kfQC-sQxMcw&lc=UgyCxQWypNIhG2nUn794AaABAg.9q1p6q7ah839tUQl_92mvg https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgyOh-eR43LvlIJLG5p4AaABAg.9isnKJoRfbL9itPC-4uckb https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugy1B1aQnHq2WbbucmR4AaABAg.9isY3Ezhx4j9itQLuif26T https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgxxajSt03TX1wxh3IJ4AaABAg.9irSL7x4Moh9itTRqL7dQh https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iK75iCEaGN https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iK7XF33njy https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iKCSgpAqA1 https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iKCy--3x8E https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwNaJiNATXshvJ0Zz94AaABAg.9iEktVkTAHk9iF9_pdshr6 https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iFAZq-JEZ- https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgxV2r7KQnuAyZVLHH54AaABAg.9iDVgy6wzct9iFBxma9zXI https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwnYuSngiuYaUhEMWN4AaABAg.9iDQN7TORHv9iFGQQ5z_3f https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwJ3yzdk_EE98dndmt4AaABAg.9iDLC2uEPRW9iFGvgk11nH https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgyDWAVqCa4yMot463x4AaABAg.9iH3wvUZj3n9iHnpOxOeXa https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwvLFdMEAba5rLHIz94AaABAg.9iGReNGzP4v9iHoeaCpTG8 https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iHpsWCdJ5I https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA End Sub

.FormulaArray
For this Thread post, we will just accept that the .FormulaArray is required to tell Excel that we want to put array values across a range more than one cell and that we want to see them . The full explanation of this is a very large subject discussed elsewhere involving amongst other things the whole CSE story
09-05-2023, 02:20 PM
DocAElstein

sfölb öflxmbömb

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://eileenslounge.com/viewtopic.php?p=316441#p316441
https://eileenslounge.com/viewtopic.php?p=324736#p324736
https://eileenslounge.com/viewtopic.php?p=324990#p324990
https://eileenslounge.com/viewtopic.php?f=27&t=41937&p=325485#p325485
https://eileenslounge.com/viewtopic.php?p=325609#p325609
https://eileenslounge.com/viewtopic.php?p=325610#p325610
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg. 8xzeMdC8IOGADdPM65i9PG
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg. 8xzeMdC8IOGADdPQHFk_zm
http://www.eileenslounge.com/viewtopic.php?p=324457#p324457
http://www.eileenslounge.com/viewtopic.php?p=324064#p324064
http://www.eileenslounge.com/viewtopic.php?p=323960#p323960
https://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgyZCnNfnZRfgwzDlQF4AaABAg
https://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgyZCnNfnZRfgwzDlQF4AaABAg. ADd4m2zp_xDADd6Nnotj1C
s://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgySdtXqcaA27wQLd1t4AaABAg
http://www.eileenslounge.com/viewtopic.php?p=323959#p323959
http://www.eileenslounge.com/viewtopic.php?f=30&t=41784
http://www.eileenslounge.com/viewtopic.php?p=323966#p323966
http://www.eileenslounge.com/viewtopic.php?p=323959#p323959
http://www.eileenslounge.com/viewtopic.php?p=323960#p323960
http://www.eileenslounge.com/viewtopic.php?p=323894#p323894
http://www.eileenslounge.com/viewtopic.php?p=323843#p323843
https://www.youtube.com/watch?v=fEHKPhJxgBA&lc=Ugxx8_MjhC9FDaQgcHN4AaABAg
https://www.youtube.com/watch?v=jpjYm4UvyWk&lc=Ugx_Qd4rfAN_ZYcJbo94AaABAg. ACGbG9c76OWACGbjKa7H8k
https://www.youtube.com/watch?v=jpjYm4UvyWk&lc=Ugx_Qd4rfAN_ZYcJbo94AaABAg
https://www.youtube.com/watch?v=GyPHaydeng0&lc=UgzE4a4f_e_y9Rk5OR94AaABAg
https://www.youtube.com/watch?v=I5FkNG94BcQ&lc=UgxXnkEHqulXSR5tXwh4AaABAg
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg. 8xzeMdC8IOGABa6BSa173Z
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg. 8xzeMdC8IOGABa6-64Xpgl
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg. 8xzeMdC8IOGABa5ms39yjd
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg. 8xzeMdC8IOGABa5ZXJwRCM
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg. 8xzeMdC8IOGABa4Pr15NUt
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg. 8xzeMdC8IOGABa4I83JelY
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg. 8xzeMdC8IOGADdMo2n-hyF
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

a	b	= Index( {	a	b	c	d	} , {	1	1	, {	1	2	)
c	d							1	1	}	3	4	}

(1, 1)=a	(1, 2)=b
(1, 3)=c	(1, 4)=d