matrix columns to rows

[J_J]

Hi,
Working with WinXP+Office2K...
The below code gives a "function not defined" error.
In fact my intention is not to only put A1:A10 data from Sheet2 to A1:J1 row
in Sheet1, but to copy the all 3 columns (A,B,C) to rows (1,2,3) in a 3x3
matrix form (from Sheet2 to Sheet1).
Can experts recommend alterations to the code?

'------------------
Sub read()
Dim Arr1 As Range
Dim Arr2() As Variant
Arr2 = Worksheets("sheet2").Range("A1:A10").Value
Set Arr1 = Worksheets("sheet1").Range("A1:J1")
End Sub
'----------------------
Sub transfer()
Dim i As Integer
For i = 1 To 10
Arr1(i, 1).Value = Arr2(1, i)
Next i
End Sub

Thanks
J_J

 

[Tom Ogilvy]

Sub ReadandTransfer()
Worksheets("sheet2").Range("A1:C10").Copy
Worksheets("Sheet1").Range("A1").PasteSpecial xlPasteAll, Transpose:=True
End Sub


If you only want values, then change xlPasteAll to xlValues

 

[jindon]

Hi

If you want to transpose 3x3 from then vertically lined-up date then

Code:
--------------------

Sub test()
Dim arr1, arr2, ws1 As Worksheet, ws2 As Worksheet, i As Integer
Dim x, y

Set ws1 = Sheets("sheet1"): Set ws2 = Sheets("sheet2")
arr1 = ws1.Range("a1:a9").Value
ReDim arr2(1 To 3, 1 To 3)

For i = LBound(arr1) To UBound(arr1)
x = Application.RoundUp(i / 3, 0)
y = i Mod 3: If y = 0 Then y = 3
arr2(x, y) = arr1(i, 1)
Next

ws2.Range("a1").Resize(UBound(arr2, 1), UBound(arr2, 2)).Value = arr2
End Sub

 

[J_J]

Thank you Tom,
that did it!.
J_J

Sub ReadandTransfer()
Worksheets("sheet2").Range("A1:C10").Copy
Worksheets("Sheet1").Range("A1").PasteSpecial xlPasteAll, Transpose:=True
End Sub


If you only want values, then change xlPasteAll to xlValues

 

[J_J]

Thank you jindon,
Although Tom's solution is a cure for the problem, I'll also give a try to
your code later on...
Regards
J_J