CODEWITHSUNDEEP
excelvba
Son Gozita
Son Gozita

Reputation: 67

How to copy Range Array to specific destination in VBA?

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

Answers (2)

Paul Kelly
Paul Kelly

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

3-14159265358979323846264
3-14159265358979323846264

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

Related Questions