On Fri, 01 Aug 2003 13:06:30 -0700, Alan Beban wrote:

In Sanders' Sub Transpose below, the 4th line from the end has a
typo--"MyArrayMyArrayTemp" should be "MyArrayTemp". In addition,
transferring the arrays to the worksheet element by element is quite
slow; they can be transferred directly, at least in xl2000 and later.
The following Sub TransposeIt (to avoid a procedure with the same name
as a built-in Excel function) illustrates this. It also redeclares i
and j as Long to accommodate larger arrays. I left most of the original
lines of code, commented out, so the changes can be more easily
identified. Watch for word wrap.

Sub TransposeIt()

Dim MyArray(), i As Long, j As Long, _
MyArrayTemp()
ReDim MyArray(1 To 80, 1 To 80)
ReDim MyArrayTemp(1 To 80, 1 To 80)

For i = LBound(MyArray, 1) To UBound(MyArray, 1)
For j = LBound(MyArray, 2) To UBound(MyArray, 2)
MyArray(i, j) = CInt(Rnd() * 1000)
Next j
Next i

For i = LBound(MyArray, 1) To UBound(MyArray, 1)
For j = LBound(MyArray, 2) To UBound(MyArray, 2)
MyArrayTemp(j, i) = MyArray(i, j)
Next j
Next i

'Range("A1").Activate
Set rng = Range("A1")
Range(rng(1, 1), rng(UBound(MyArray), _
UBound(MyArray, 2))).Value = MyArray
'For i = LBound(MyArray, 1) To UBound(MyArray, 1)
' For j = LBound(MyArray, 2) To UBound(MyArray, 2)
' ActiveCell.Offset(i - 1, j - 1).Value = MyArray(i, j)
' Next j
'Next i

Set rng = rng(UBound(MyArrayTemp) + 2, 1)
Range(rng(1, 1), rng(UBound(MyArrayTemp), _
UBound(MyArrayTemp, 2))).Value = MyArrayTemp
'For i = LBound(MyArray, 1) To UBound(MyArray, 1)
' For j = LBound(MyArrayTemp, 2) To UBound(MyArrayTemp, 2)
' ActiveCell.Offset(i - 1, j - 1).Value = MyArrayTemp(i, j)
' Next j
'Next i

End Sub

Alan Beban

Sander Lablans wrote:
On Fri, 1 Aug 2003 12:58:41 +0200, Ulrik Petersen wrote:


Hi.
Does anyone know how to transpose for instance a 2x2 matrix in VBA, Excel
2000?


Try this (it's a small procedure, which fills an array, transposes the
array and places both 'matrices' into an excel-sheet):

Sub Transpose()

Dim MyArray(1 To 10, 1 To 10), i As Integer, j As Integer, _
MyArrayTemp(1 To 10, 1 To 10)

For i = LBound(MyArray, 1) To UBound(MyArray, 1)
For j = LBound(MyArray, 2) To UBound(MyArray, 2)
MyArray(i, j) = CInt(Rnd() * 1000)
Next j
Next i

For i = LBound(MyArray, 1) To UBound(MyArray, 1)
For j = LBound(MyArray, 2) To UBound(MyArray, 2)
MyArrayTemp(j, i) = MyArray(i, j)
Next j
Next i

Range("A1").Activate

For i = LBound(MyArray, 1) To UBound(MyArray, 1)
For j = LBound(MyArray, 2) To UBound(MyArray, 2)
ActiveCell.Offset(i-1, j-1).Value = MyArray(i, j)
Next j
Next i

Range("A15").Activate

For i = LBound(MyArray, 1) To UBound(MyArray, 1)
For j = LBound(MyArrayTemp, 2) To UBound(MyArrayMyArrayTemp, 2)
ActiveCell.Offset(i-1, j-1).Value = MyArrayTemp(i, j)
Next j
Next i

End Sub

Just noted the typo myself. Of course very silly...

I haven't got experience with filling area's directly from VB-code into a
worksheet (guess I never needed them), so the corrections you perform are
quite educative. Thx.

Just a quick question, did you deliberately NOT assign a datatype to the
variable 'arr' in your ArrayTranspose-function, further in this thread?

SL