Macro issues - trying to get an array to a column
Sorry, I posted the wrong macro; this is the finalized version I meant to
post...
Sub MatrixToColumn()
Dim X As Long, M As Range, Start As Range
With Application
On Error GoTo Whoops
Set M = .InputBox("Select range to copy", Type:=8)
Set Start = .InputBox("Select destination start cell", Type:=8)(1)
For X = 1 To M.Rows.Count
Start.Offset((X - 1) * M.Columns.Count).Resize(M.Columns.Count) = _
.WorksheetFunction.Transpose(M.Offset(X - 1).Rows(1))
Next
End With
Whoops:
End Sub
--
Rick (MVP - Excel)
"Rick Rothstein" wrote in message
...
Give this macro a try...
Sub MatrixToColumn()
Dim X As Long, M As Range, Start As Range
With Application
Set M = .InputBox("Select range to copy", Type:=8)
If M Is Nothing Then Exit Sub
Set Start = .InputBox("Select destination start cell", Type:=8)(1)
If Start Is Nothing Then Exit Sub
For X = 1 To M.Rows.Count
Start.Offset((X - 1) * M.Columns.Count).Resize(M.Columns.Count) = _
.WorksheetFunction.Transpose(M.Offset(X - 1).Rows(1))
Next
End With
End Sub
--
Rick (MVP - Excel)
"valkyrie" wrote in message
...
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
|