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
Reply With Quote
Bookmarks