Macro issues - trying to get an array to a column
Ah-ha. Sorry, I must have put something in the wrong place. I tried again,
and it does indeed seem to be working.
Thank you! You made a rather rotten few hours sitting here at the computer
a little less painful. :) All my best.
- V
"Jacob Skaria" wrote:
May be I have not explained well enough.
You need to keep the FOR loop... I commented it just to let you know the new
codes added....
If this post helps click Yes
---------------
Jacob Skaria
"valkyrie" wrote:
Thank you, but that didn't seem to change anything, oddly enough.
Shouldn't there be away to change the FOR loop to read rows instead of
columns? I experimented with changing the FOR loop to index by iRow instead
of iCol, but I couldn't ever get it to work.
Thanks
"Jacob Skaria" wrote:
Try the below and feedback...Additional codes added to top and bottom of the
For loop..
Dim rTemp As Range
Set rTemp = rOut
'For iCol = 1 To nCol
'rOut.Value = WorksheetFunction.Index(v, 0, iCol)
'Set rOut = rOut.Offset(nRow)
'Next iCol
Range(Cells(rTemp.Row, rTemp.Column), _
Cells(rOut.Row, rOut.Column)).Sort _
Key1:=Cells(rTemp.Row, rTemp.Column), _
Order1:=xlAscending, Orientation:=xlTopToBottom
If this post helps click Yes
---------------
Jacob Skaria
"valkyrie" wrote:
I found a great macro I want to use to convert an array to a column. This
one takes an array like so:
1 2 3
4 5 6
and makes it
1
4
2
5
3
6
But I need
1
2
3
4
5
6
Can anyone help me edit it? I can't get it to work for the life of me.
Thanks!
Sub Matrix2Column()
Dim v As Variant
Dim nCol As Long
Dim nRow As Long
Dim rOut As Range
Dim iCol As Long
On Error Resume Next
v = Application.InputBox("Select range to copy", Type:=8).Value
If IsEmpty(v) Then Exit Sub
nRow = UBound(v, 1)
nCol = UBound(v, 2)
Set rOut = Application.InputBox("Select destination",
Type:=8).Resize(nRow, 1)
If rOut Is Nothing Then Exit Sub
For iCol = 1 To nCol
rOut.Value = WorksheetFunction.Index(v, 0, iCol)
Set rOut = rOut.Offset(nRow)
Next iCol
End Sub
|