Creating and Transposing Array in VBA

Question

I'm hoping to load values in a range to an array and transpose that array to another location (different workbook)

I am using the below forum post to get an idea of how to do it:

http://www.mrexcel.com/forum/excel-questions/629320-application-transpose-visual-basic-applications-array.html

Below is the code I am working with now, and I'm getting the 1004 object defined error. Can anyone spot what I am doing wrong?

I did find that the code works if I do not Set tRangeArray and instead do Sheets("sheet1").Range("C12:C19).Value = Application.Transpose(MyArray), but I'm not sure why that's different from my code.

Sub copy_data()
Dim cRange As Range, aRange As Range, tRange1 As Range, wbk1 As Workbook, wbk2 As
Workbook
Dim MyArray() As Variant, tRangeArray As Range

Set wbk1 = ThisWorkbook

MyArray = Range("E12:L12")
Set tRangeArray = wbk1.Sheets("sheet1").Range("C12:C19")

Sheets("sheet1").Range(tRangeArray).Value = Application.Transpose(MyArray)

tRangeArray.Value = Application.Transpose(MyArray)

Dmitry Pavliv
– Dmitry Pavliv

2014-06-27 16:13:13 +00:00
Commented Jun 27, 2014 at 16:13 — Dmitry Pavliv
– Dmitry Pavliv, Commented Jun 27, 2014 at 16:13

Dmitry Pavliv · Accepted Answer · 2014-06-27 19:26:00Z

8

As I mentioned in comments, just use:

tRangeArray.Value = Application.Transpose(MyArray)

Sheets("sheet1").Range(tRangeArray).Value not working, because Range accepts either single parameter - string with range address (not range itself): Range(addr), either two parameters - top left and bottom right cells: Range(cell_1,cell_2)

answered Jun 27, 2014 at 19:26

Dmitry Pavliv

35.9k13 gold badges84 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

robertocm · Accepted Answer · 2016-07-07 17:25:13Z

2

Similar, but using Resize and Ubound:

Dim myarray As Variant
myarray = Array(1, 2, 3, 4, 5)
Sheets("sheet1").Range("A1").Resize(UBound(myarray), 1).Value = Application.Transpose(myarray)

edited Jul 7, 2016 at 17:25

answered Jul 7, 2016 at 17:20

robertocm

1327 bronze badges

Collectives™ on Stack Overflow

Creating and Transposing Array in VBA

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related