VBA - Pasting 2d Array to worksheet. Why is it producing a blank column with everything shifted 1 column across?

PTP86

Board Regular
Joined
Nov 7, 2009
Messages
86
I'm trying to paste the dynamic array OutputArray to the worksheet starting in cell Q8 which I've given the name UniqueEntitiesOutput

VBA Code:
ReDim OutputArray(1 To UniqueOutputs, 3)
The code then populates OutputArray and I now want to paste it to the worksheet

Here I've used select just so that you can see on the screenshot that I am telling it to use the intended range
VBA Code:
Range("UniqueEntitiesOutput").Resize(UBound(OutputArray, 1), 3).Select
Selection = OutputArray

What's the result? (see 2nd screenshot)
It's outputting a column of blanks in Q (whereas it should be outputting 1st column of the array)
It's outputting the 1st column of the array in R (whereas it should be outputting 2nd column of the array)
It's outputting the 2nd column of the array in S (whereas it should be outputting 3rd column of the array)
It's not doing anything with the 3rd column of the array

Yet if I look at the Immediates window, the array is holding data the way I was expecting it to
?OutputArray(1,1)
Aviva plc
?OutputArray(1,2)
AV.
?OutputArray(1,3)
1



So why isn't it pasting the 3 columns of the array starting in column Q?


Thanks
 

Attachments

  • Picture1.jpg
    Picture1.jpg
    88.8 KB · Views: 3
  • Picture2.jpg
    Picture2.jpg
    84.7 KB · Views: 3
Last edited:

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
The code then populates OutputArray and I now want to paste it to the worksheet
You can put the code to populates the OutputArray , or put all your code.
 

PTP86

Board Regular
Joined
Nov 7, 2009
Messages
86
You can put the code to populates the OutputArray , or put all your code.

From the comment I've made about the immediates window, you can see that the array has populated as intended, and you can see what it looks like.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
Is it a problem for you to put all your code here?
 

PTP86

Board Regular
Joined
Nov 7, 2009
Messages
86
SOLVED :): Knew it would be an obvious error 😤
As expected, it's a fault in one of the bits of code that I did provide

ReDim OutputArray(1 To UniqueOutputs, 1 to 3)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,767
Messages
5,574,126
Members
412,573
Latest member
Chriszion
Top