Reputation: 67
Please help I've search everywhere but it seems all methods failed. This is my Code
Dim MyArray(1 To 20) As Range
Dim A0 As Integer
Cells(1, 1).Select
For A0 = 1 To 20
Set MyArray(A0) = Range(ActiveCell, ActiveCell.Offset(1, 0))
ActiveCell.Offset(0, 1).Select
Next
Range("A15:T15") = Application.Transpose(MyArray)
Range("A15:T16") = Application.Transpose(MyArray)
Range("A15:T15") = WorksheetFunction.Transpose(MyArray)
Range("A15:T16") = WorksheetFunction.Transpose(MyArray)
I've tried every last 4 procedure/s. But failed. This is just summary to make it east to read. Thanks
Upvotes: 1
Views: 844
Reputation: 985
You can move data from "Range to Array" and "Array to Range" using only one line for each operation.
Sub CopyUsingArray()
' Create array
Dim Marks() As Variant
' Read 20 values into array from sheet1
Marks = Range("A1:T1").Value
' Write the 20 values to row 15
Range("A15:T15").Value = Marks
End Sub
Of course, if you are simply copying from on range to another you don't need to use an array. You can use the Range.Copy method like so
Sub CopyRange()
Range("A1:T1").Copy Destination:=Range("A15")
End Sub
If you need to Transpose the data you can do it this way
Sub CopyRange_Transpose()
Range("A1:T1").Copy
Range("A15").PasteSpecial Transpose:=True
End Sub
Upvotes: 1
Reputation: 3290
Going a bit further than my comment ... try this as an example ...
Sub a()
Dim x(2) As Integer
x(0) = 1
x(1) = 2
Range("A10:B10").Value = x
End Sub
This correctly outputs the array into cells A10
and B10
In the case of a 2D array, you can use this example ...
Sub a()
Dim x(2,2) As Integer
x(0,0) = 1
x(0,1) = 2
x(1,0) = 3
x(1,1) = 4
Range("A10:B11").Value = x
End Sub
You still don't need transpose though!
Upvotes: 2