@pgcthat was very helpful. It is seeming quite understandable now ( like most things do with hindsight) ( at least the getting of the indicies...

Great, pgc####) , but it would have taken me ages to get the point without your help.Thanks very much.

So:

_1 ) ( Minor typo I think at the start, you meant from Top left, going left to right, then top to bottom, like the Cells(x) convention from the ask2tsp stuff, Post #3... normal Excel convention I think for this stuff ? )

_2 ) Columns Indicie calculations, clms() Array

_2a ) I stupidly missed the __S__ in columnSand got confused with

My______Clms()=Evaluate("column(C:N)") = Array(3, 4, 5 ....... 14)_____’Gives Array column No.s 3:14

and

Your____ COLUMNS(Sheet1!$C$2:$N$225)_____’Gives column Count in range

=Columns(Sheet1!$C$2:$N$225)

=Columns(Sheet1!$C$2:$N$2)

=Columns(Sheet1!$C$2:$N$34871)

=Columns(Sheet1!$C:$N)

=Columns(Sheet1!$C:$N)

=Columns(Sheet1!$C:$N)

=Columns(C:N)

=12

My fading memory of maths ( especially in my forgotten native English) don’t help either. MOD is “bit left over” as you say. We are “MODin” every number in are range along the “ask2tsp stuff, Post #3” VBA Cells(x) convention, and

_2 b) we do a bit of subtle number juggling to get what shg once explained to me

VBA Column Letter from Column Number. Explained.

as a “not quite right” number system that does not have a 0 in it a “bijective numeration“ thing where no 0 is in ( In our case 1,2,3,4,5,6,7,8,9,10,11,12,1,2,3....

we finally have a Column Array formula like this

=mod(ROW(2:2688)-2,12)+1

Which works in a spreadsheet cell ( at least gives us the first value in the Array, 1, ) but it errors in the VBA Evaluate(“________”) ...... so.......

_2c) So for that problem, then there is the If(Row ___ coercing stuff necessary ( what you and Rory and XLORX discussed to death in that Epic Evaluate Range and VlookUp Thread...) which is required to get the Evaluate(“________”) to give us an Array.

We end up with this Simplified formula

clms() = Evaluate("=if(row(2:2689),mod(ROW(2:2688)-2,12)+1)")

which returns us a 1 Column 2 Dimensional Array which repeats the sequence 1,2,3,4,5,6,7,8,9,10,11,12, 1,2,3,4,5,6,7,8,9,10,11,12, 1,2,3,4,5,6,7,...... etc

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

_ 3) Row indicies

__A similar argument, with your explained INT for the rows gives us this simplified version for the Rows indices

rws() = Evaluate("=if(row(2:2689),INT((ROW(2:2689)-2)/12)+1)")

which returns another 1 Column 2 Dimensional Array which gives this

1,1,1,1,1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,2,2,2,2,3,3,3,3,3,3,3,3,3,3,3,3,4,4........

In words, 12 x 1’s then 12 x 2’s then 12 x 3’s ... etc.

_ 4) Finally Application.Index with these Rows and Columns ( VBA Arrays. )

I see some logic here, ... but must be away now for a bit...I would like to come back in a day of two ( if i can ) when the thing has gone through my head a bit... It may help me to solve another big mystery in this Thre........ Ahh – can’t mention that or I won’t be “allowed” back..........

_ ... I mean finding out exactly how this works....

_ ......”................ use of Application.Index with Look Up Rows and Columns Arguments as VBA Arrays. …..“...####I think You may be helping me understand this one now.. a bit...finally..

Thanks very much pgc, I think you have really got some things going up in my head and helping it to function a bit just now.....

Alan

Code:Sub pgcDBMOneLinerSHimplifGfied() Dim rws() As Variant Dim clms() As Variant Sheets("Sheet2").Columns(4).ClearContents 'Clear column rws() = Evaluate("=if(row(2:2689),INT((ROW(2:2689)-2)/12)+1)") clms() = Evaluate("=if(row(2:2689),mod(ROW(2:2688)-2,12)+1)") Sheets("Sheet2").Range("D2:D2689").Value = Application.Index(Range("Sheet1!$C$2:$N$225"), rws(), clms()) End Sub

## Like this thread? Share it with others