title: Writing an array to a range. Only getting first value of array
body: <p>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.</p>
  
  <p>Here is the code:</p>
  
  <pre><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
  </code></pre>
  
  <p>What I expect to see is:</p>
  
  <pre><code>(Col A)     (Col E)
  A           A
  B           B
  C           C
  D           D
  E           E
  </code></pre>
  
  <p>What I actually see is:</p>
  
  <pre><code>(Col A)     (Col E)
  A           A
  A           A
  A           A
  A           A
  A           A
  </code></pre>
  
  <p>What am I doing wrong here?</p>
  
  <p>I tried to follow Chip Pearson's suggestions, as found <a href="http://www.cpearson.com/excel/ArraysAndRanges.aspx" rel="noreferrer">HERE</a></p>
  
  <p>But no luck...</p>
  
  <p>Okay, so adding in the second part of this problem:</p>
  
  <p>I have a 1D array with 8,061 elements that I am passing to the following function as such:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>So when I run this, the transpose function is properly converting into an:</p>
  
  <pre><code>Array(1 to 8061, 1 to 1)
  </code></pre>
  
  <p>The range appears to be a single column with 8,061 cells in Column G.</p>
  
  <p>But I get the following error:</p>
  
  <pre><code>Run-time error '1004':
  Application-defined or object-defined error
  </code></pre>
  
  <p>The error is thrown on the following line:</p>
  
  <pre><code>rngData = arrayDataTransposed
  </code></pre>
  
  <p><strong>--- UPDATE ---</strong></p>
  
  <p>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:</p>
  
  <pre><code>arrayData(i) = "=IFERROR(VLOOKUP($D" + CStr(i) + "," + strSheetName + "!$D:$F,3,FALSE),"")"
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>arrayBucketData(i) = "=IFERROR(VLOOKUP($D" + CStr(i) + "," + strSheetName + "!$D:$F,3,FALSE),"""")"
  </code></pre>
  
  <p>I can chalk that up to late-night bonehead coding.</p>
  
  <p>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.</p>
  
  <p>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 <code>strSheetName</code> 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.</p>
  
  <p>Thanks to everyone for their help!  I hope this helps someone else down the road.</p>