OK . Think the might do for testing today.....
test
Code:Rem 3 outout Dim arrOutA() As String: Let arrOutA() = Split(strOutA, vbCr & vbLf, -1, vbBinaryCompare) ' Excel has the convention of taking a 1D array as being "horizontal" for spreadsheet purposes, so will consider it as a row of data values if applied to a worksheet range Dim arrOutB() As String: Let arrOutB() = Split(strOutB, vbCr & vbLf, -1, vbBinaryCompare) ' Let WsOut.Range("A2").Resize(UBound(arrOutA()), 1).Value = Application.Transpose(arrOutA()) Let WsOut.Range("A2").Resize(UBound(arrOutA()), 1).Value = Application.Index(arrOutA(), Evaluate("=row(1:" & UBound(arrOutA()) & ")/row(1:" & UBound(arrOutA()) & ")"), Evaluate("=row(1:" & UBound(arrOutA()) & ")")) ' Let WsOut.Range("B2").Resize(UBound(arrOutB()), 1).Value = Application.Transpose(arrOutB()) Let WsOut.Range("B2").Resize(UBound(arrOutB()), 1).Value = Application.Index(arrOutB(), Evaluate("=row(1:" & UBound(arrOutB()) & ")/row(1:" & UBound(arrOutB()) & ")"), Evaluate("=row(1:" & UBound(arrOutB()) & ")")) ' 1 D array to list in 1 column Let Range("T1").Resize(UBound(UnicBea()) + 1, 1).Value2 = Application.Index(UnicBea(), Evaluate("=row(1:" & UBound(UnicBea()) + 1 & ")/row(1:" & UBound(UnicBea()) + 1 & ")"), Evaluate("=row(1:" & UBound(UnicBea()) + 1 & ")")) End SubDik.Add Key:=Join(Application.Transpose(Range("Q1").CurrentRegion.Value2), ""), Item:="AnyThong"Code:Sub EvaluateRangeIt() ' A1:A4 to C1:F1 B1:B4 to C2:F2 Range("C1:F2").Value = Application.Index(Range("A1:B4"), Evaluate("=column(A:D)"), Evaluate("=row(1:2)")) End Sub
Alternative way to transpose 1 D array to list in 1 columnCode:Public Function CL(ByVal lclm As Long) As String ' http://www.excelforum.com/development-testing-forum/1101544-thread-post-appendix-no-reply-needed-please-do-not-delete-thanks-4.html#post4213980 Do: Let CL = Chr(65 + (((lclm - 1) Mod 26))) & CL: Let lclm = (lclm - (1)) \ 26: Loop While lclm > 0 End Function
Code:Let Range("F1").Resize(UBound(dDays()), 1).Value = Application.Index(dDays(), Evaluate("=row(1:" & UBound(dDays()) & ")/row(1:" & UBound(dDays()) & ")"), Evaluate("=row(1:" & UBound(dDays()) & ")"))
Join an array of the cells
The key to the new flexible solution is to Join the elements of an array with a comma, where the elements are the cell values, which themselves are separated already with a comma. (So then as previously, we then finally have a single string of comma separated values, which we , as previously split by a comma, to give us our single array of all data values.
Initially we can get an array of cell values from applying the .Value property to our multi cell range.
Something of this sort of form, for example, for if we had 4 cells in the second row,
_________arrCels2D1Row() = Ws1.Range("A2:D2").Value2
A small snag here is that the array returned by the .Value property, is a 1 row, 2 Dimensional array, ( a pseudo “horizontal” , “single width” array) but the VBA strings Join function only accepts a 1 dimensional array. However, it’s a strange characteristic of VBA that many things if they are asked to return something in the orientation of pseudo “horizontal” , “single width” , then they return a 1 Dimensional array: It seems that somehow the internal workings often relate a row orientation to a single dimensional array. ( This is convenient to think about, as is the idea of pseudo “horizontal” , “single width” , since in the case of a 1 dimensional array we often write it in a line like {1, 2, 3, 6, "z"} , but we should remember that strictly speaking academically orientation in arrays is subjective. )
As example this seemingly redundant code line takes the first row from our single row 2 Dimensional array: Seemingly useless, but in fact it returns the 1 Dimensional array of cell values, as we require, -
___ arrCels1D() = Application.Index(arrCels2D1Row(), 1, 0)
___ ___ - Effectively that converts a 2 Dimensional 1 row array into a 1 Dimensional array, and why VBA has that sort of strange characteristic thing is not clearly known
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
Originally Posted by DocAElstein
Bookmarks