Code:
' https://excelfox.com/forum/showthread.php/2837-Appendix-Thread-App-Index-Rws()-Clms()-Majic-code-line-Codings-for-other-Threads-Tables-etc-)?p=19586&viewfull=1#post19586
' http://www.eileenslounge.com/viewtopic.php?f=27&t=38973&p=301714#p301714
Sub TransposeABitDifferent()
' Consider a two column, three row selection
' a b
' c d
' e f
Dim vTemp As Variant ' Use variant, and set a Shift+F9 watch on it ( To do this: Highlight it anywhere in the coding and use keys Shift+F9 )
' A single column transpose
Let vTemp = Application.Transpose(Selection.Columns(1)) ' gives - {"a", "c", "e"}
' Or we can Transpose in a different way, with index, and Stuff
Let vTemp = Application.Index(Selection, Array(1, 2, 3), Array(1, 1, 1)) ' - {"a", "c", "e"}
' What's going on: Excel / Excel VBA is doing what it often does, along a row, then down a column, sometimes referred to as array type calculations, in this case the argument arrays are followed leading to an output of a form of the 1 dimensions, ("pseudo horizontal") array , as we want. The index works three times on each pair of co ordinates, each time giving the result in the way Index would in the more conventional way for just 1 pair of row and column co ordinates
' using this way we are not restricted to a single column, we can pick any co ordinates we chose.
' The next co ordinates give us a simple single line of all our cell values
Let vTemp = Application.Index(Selection, Array(1, 1, 2, 2, 3, 3), Array(1, 2, 1, 2, 1, 2)) ' {"a", "b", "c", "d", "e", "f"}
' Or
Dim Rws() As Variant, Clms() As Variant
Let Rws() = Array(1, 1, 2, 2, 3, 3): Clms() = Array(1, 2, 1, 2, 1, 2)
Let vTemp = Application.Index(Selection, Rws(), Clms()) ' ' {"a", "b", "c", "d", "e", "f"}
' To make a more useful flexible solution, what we need to do is to get those array arguments dynamically from the Selection
' For both array aguments we need a 6 element 1 dimensional array
' ( we hit a snag generally in these things in that often Excel has those arrays but won't give us them, - typically it may only give us the first value. Noone is quite sure why. There are various tricks found empirically to make Excel give us the full array of values. Usually it involves putting what we actually want to do inside something that encourages Excel to return us all array values. (There may be some parallel to the so called C S E action in a spreadsheet to get full array results, noone is quite sure). Herfe is a trick I found, empirically to often work
' If({1}, here what you want to do ) I don't always need to do this. During the development of a solution I monitor ma results in vTemp , and if I onbl
' The start point is usually to get an array of the size we want of integers, and then fiddle with some maths to get the actual integer values we want
Let vTemp = Evaluate("=Column(A:F)") ' {1, 2, 3, 4, 5, 6}
' For a flexible solution we want the F Getting at a column letter is often a bit tricky, strangely Excel never made a function for it, whereas getting the column number is usually easy.
' In our case the column numnber is given by Selection.clumns.count
Let vTemp = Selection.Cells.Count ' 6
' there are a few ways to convert that to the appropriat Letter. An address way is convenient
Let vTemp = Split(Cells(1, 6).Address, "$")(1) ' - "F" This splits any row cell in column 6 address, in this example the cell $F$1, by a "$" resulting in an array {"", "F", "1"), we thne take the second element, which has the indice of 1 , (not 2 ,since such an array starts at the indicie of 0)
Let vTemp = Split(Cells(1, Selection.Cells.Count).Address, "$")(1) ' - "F"
Let vTemp = Evaluate("=Column(A:" & Split(Cells(1, Selection.Cells.Count).Address, "$")(1) & ")") ' {1, 2, 3, 4, 5, 6}
' ( To make the next steps easy to follow, we will stay with the "F" hard coded then substitute the bit to get it flexible later
Let vTemp = Evaluate("=Column(A:F)") ' {1, 2, 3, 4, 5, 6}
' Some maths now. There are probably a few ways. We fiddle around a bit. We try to get it using some numbers we could get dynamically, things typically of the count nature, such as row and column count, which are 3 and 2 in this example
' Rws()
Let vTemp = Evaluate("=Column(A:F)/2") ' {.5, 1, 1.5, 2, 2.5, 3}
Let vTemp = Evaluate("=Int(Column(A:F)/2)") ' 0
Let vTemp = Evaluate("=If({1},Int(Column(A:F)/2))") ' {0, 1, 1, 2, 2, 3}
Let vTemp = Evaluate("=Int((Column(A:F)+2)/2)") ' 1
Let vTemp = Evaluate("=If({1},Int((Column(A:F)+2)/2))") '
Let vTemp = Evaluate("=If({1},Int((Column(A:F)+(2-1))/2))")
Let vTemp = Evaluate("=If({1},Int((Column(A:F)+(" & Selection.Columns.Count & "-1))/" & Selection.Columns.Count & "))")
Let vTemp = Evaluate("=If({1},Int((Column(A:" & Split(Cells(1, Selection.Cells.Count).Address, "$")(1) & ")+(" & Selection.Columns.Count & "-1))/" & Selection.Columns.Count & "))")
Let Rws() = Evaluate("=If({1},Int((Column(A:" & Split(Cells(1, Selection.Cells.Count).Address, "$")(1) & ")+(" & Selection.Columns.Count & "-1))/" & Selection.Columns.Count & "))")
' Clms()
Let vTemp = Evaluate("=Mod(Column(A:F),2)") ' 1
Let vTemp = Evaluate("=If({1},Mod(Column(A:F),2))") ' {0, 1, 0, 1, 0, 1}
Let vTemp = Evaluate("=If({1},Mod((Column(A:F)-1),2)+1)") ' {1, 2, 1, 2, 1, 2}
Let vTemp = Evaluate("=If({1},Mod((Column(A:" & Split(Cells(1, Selection.Cells.Count).Address, "$")(1) & ")-1),2)+1)")
Let Clms() = Evaluate("=If({1},Mod((Column(A:" & Split(Cells(1, Selection.Cells.Count).Address, "$")(1) & ")-1),2)+1)")
Let vTemp = Application.Index(Selection, Rws(), Clms()) ' ' {"a", "b", "c", "d", "e", "f"}
' Do the Join
Dim StrOut As String
Let StrOut = Join(vTemp, ";"): Debug.Print StrOut ' a;b;c;d;e;f
End Sub
'
'
' Ref
' http://www.excelforum.com/excel-programming-vba-macros/1138428-multidimensional-array-to-single-column-range.html
' http://www.excelforum.com/excel-programming-vba-macros/1138627-dividing-the-items-of-an-array-over-multiple-columns.html
Sub SnberOne() ' http://www.eileenslounge.com/viewtopic.php?p=301714&sid=4705abb7ec796b7a3426c78642d4f638#p301714
Let Selection.Resize(1, 1).Offset(0, Selection.Columns.Count).value2 = Join(Application.Index(Selection, Evaluate("=If({1},Int((Column(A:" & Split(Cells(1, Selection.Cells.Count).Address, "$")(1) & ")+(" & Selection.Columns.Count & "-1))/" & Selection.Columns.Count & "))"), Evaluate("=If({1},Mod((Column(A:" & Split(Cells(1, Selection.Cells.Count).Address, "$")(1) & ")-1),2)+1)")), VBA.InputBox("separator", , ";")) ' a;b;c;d;e;f
End Sub
Reply With Quote
Bookmarks