CODEWITHSUNDEEP
arraysexcelvbapastetranspose
JFerro
JFerro

Reputation: 3433

Excel pasting array in cell (after resizing cell to array dimensions)

I want to paste a two-dimension array from one cell on, extending the range with RESIZE() to the exact size of the array.

I get a run error type 13 type mismatch

ReDim conceptArray(1 To 5, NumberOfConcepts + 1)


Dim firstcellofTbl As range
    Set firstcellofTbl = qbaTbl.ListColumns("name").range.iTem(1).Offset(1, 0)
' this is just a test:
'firstcelloftbl is a single cell
firstcellofTbl.Select


firstcellofTbl.Resize(UBound(conceptArray, 2), UBound(conceptArray, 1)) = Application.Transpose(conceptArray)

whatever the size of the array is some things I give for granted. a) firstcellofTbl is just one cell. And exists since the selection works.

the resize size has the same dimensions as the array because it is just resize to the dimensions of the array

c) the resize interchanged both dimensions, therefore the application.transpose

this is the bit of code I added to make tests:

 MsgBox UBound(conceptArray, 1)
 MsgBox UBound(conceptArray, 2)
 Dim newrange As range
    Set newrange = firstcellofTbl.Resize(UBound(conceptArray, 2), UBound(conceptArray, 1))
MsgBox newrange.address

being: UBound(conceptArray, 1)=5 UBound(conceptArray, 2)=439

newrange.address =$a$3:$E$441

which seems to match since the size of the new range is the same as the transpose array

What I am possibly doing wrong? Thanks a lot

Note: using this way of pasting data into a listobject (or a sheet) is millions times faster than populating with for-loop, thats the reason for using it.

Upvotes: 0

Views: 132

Answers (1)

JFerro
JFerro

Reputation: 3433

The problem was that the array was dimensioned as variant.

original:

dim conceptarray() as variant

solution:

dim conceptarray() as string

Upvotes: 0

Related Questions