Writing an array to a range. Only getting first value of array

Question

I am trying to write an array to a range and I have tried several ways but no matter what, I always get only the FIRST value of the array over and over again.

Here is the code:

Option Explicit

Sub test()

    ActiveWorkbook.Worksheets("Sheet1").Cells.Clear

    Dim arrayData() As Variant
    arrayData = Array("A", "B", "C", "D", "E")

    Dim rngTarget As Range
    Set rngTarget = ActiveWorkbook.Worksheets("Sheet1").Range("A1")

    'this doesn't work
    rngTarget.Resize(UBound(arrayData, 1), 1).Value = arrayData

    Dim rngTarget2 As Range
    Set rngTarget2 = ActiveWorkbook.Worksheets("Sheet1").Range(Cells(1, 5), Cells(UBound(arrayData, 1), 5))
    'this doesn't work either
    rngTarget2.Value = arrayData

End Sub

What I expect to see is:

(Col A)     (Col E)
A           A
B           B
C           C
D           D
E           E

What I actually see is:

(Col A)     (Col E)
A           A
A           A
A           A
A           A
A           A

What am I doing wrong here?

I tried to follow Chip Pearson's suggestions, as found HERE

But no luck...

Okay, so adding in the second part of this problem:

I have a 1D array with 8,061 elements that I am passing to the following function as such:

Call writeArrayData7(strTabName, arrayBucketData, 7)

Sub writeArrayData7(strSheetName As String, arrayData As Variant, intColToStart As Integer)

    Dim lngNextRow As Long
    lngNextRow = 1 ' hard-coded b/c in this instance we are just using row 1

    ' Select range for data
    Dim rngData As Range
    Set rngData = Sheets(strSheetName).Range(Cells(lngNextRow, intColToStart), Cells(lngNextRow - 1 + UBound(arrayData, 1), intColToStart))

    ' Save data to range
    Dim arrayDataTransposed As Variant
    arrayDataTransposed = Application.Transpose(arrayData)
    rngData = arrayDataTransposed

End Sub

So when I run this, the transpose function is properly converting into an:

Array(1 to 8061, 1 to 1)

The range appears to be a single column with 8,061 cells in Column G.

But I get the following error:

Run-time error '1004':
Application-defined or object-defined error

The error is thrown on the following line:

rngData = arrayDataTransposed

--- UPDATE ---

So one thing I left out of my sample code (b/c I honestly didn't think it mattered) was that the contents of my array are actually formulas. Here is the line that I'm using in the actual live code:

arrayData(i) = "=IFERROR(VLOOKUP($D" + CStr(i) + "," + strSheetName + "!$D:$F,3,FALSE),"")"

Well, what I found (with Excel Hero's help) was that the above statement didn't have the double sets of quotes required for a string, so I had to change to this instead:

arrayBucketData(i) = "=IFERROR(VLOOKUP($D" + CStr(i) + "," + strSheetName + "!$D:$F,3,FALSE),"""")"

I can chalk that up to late-night bonehead coding.

However, one other thing I learned is that when I went back to run the full code is that it took FOREVER to paste the array to the range. This would ordinarily be a very simple task and happen quickly, so I was really confused.

After much debugging, I found that the issue came down to the fact that I was turning off all the alerts/calculations/etc and when I pasted in these formulas, the strSheetName sheet was not there yet b/c I'm developing this code separate from the main file. Apparently, it throws up a dialog box when you paste the code in, but if you have all that stuff shut off, you can't see it but it REALLY slows everything down. It takes about 6mins to paste the range if those tabs are not there, and if they exist it takes seconds (maybe less). At any rate, to refine the code a bit further, I simply added a function that checks for the required sheet and if it doesn't exist, it adds the tab as a placeholder so the entire process doesn't slow to a crawl.

Thanks to everyone for their help! I hope this helps someone else down the road.

Answer 1

Do this:

arrayData = Array("A", "B", "C", "D", "E")

[a1].Resize(UBound(arrayData) + 1) = Application.Transpose(arrayData)

The important bit is the Transpose() function.

But it is better to work with 2D arrays from the get go if you plan on writing them to the worksheet. As long as you define them as rows in the first rank and columns in the second, then no transposition is required.

Answer 2

This:

Sub test()

    ActiveWorkbook.Worksheets("Sheet1").Cells.Clear

    Dim arrayData(1 To 5, 1 To 1) As Variant
    arrayData(1, 1) = "A"
    arrayData(2, 1) = "B"
    arrayData(3, 1) = "C"
    arrayData(4, 1) = "D"
    arrayData(5, 1) = "E"

    Dim rngTarget As Range
    Set rngTarget = ActiveWorkbook.Worksheets("Sheet1").Range("A1:A5")
    rngTarget = arrayData

End Sub

will produce:

Answer 3

If I may expand the accepted answer, I propose:

[a1].Resize(UBound(arrayData) - LBound(arrayData) + 1) = Application.Transpose(arrayData)

That would be the safe way. This will work no matter if you declare your array variable as:

Dim arrayData(0 to 2) 

or

Dim arrayData(1 to 3) 

The accepted answer works only in the second case.

The proposed way might be useful if the size of array is unknown and you declare your arrayData as:

Dim arrayData()