title: How to print a 1D array of class module type to a range in Excel VBA?
body: <p>I have a class module called LotData that defines 12 public variables.  I then create an array called Buys() of type LotData and fill it with numBuys items. (0 to numBuys -1)</p>
  
  <p>I'm having trouble printing Buys() to a range.  I've seen a few examples here that make it seem easy, but I keep running into errors.</p>
  
  <p>Update:  This is the whole subroutine.  If there's a better way to do this, I'm all ears.  Maybe after I sort <em>transactions,</em> I should just copy the Buy rows to a <em>temp</em> sheet, rearrange the columns, and then copy that to my final destination?</p>
  
  <pre><code>Option Private Module
  
  Sub HandlePurchases()
  
  Sheets("master_holdings").Select
  
  Dim lastRowM As Long
  lastRowM = Range("A1").End(xlDown).Row
  lastRowM = IIf(lastRowM = 1048576, 1, lastRowM)
  
  Sheets("transactions").Select
  
  Dim lastRow As Long
  lastRow = Range("A1").End(xlDown).Row
  lastRow = IIf(lastRow = 1048576, 1, lastRow)
  
  Range("A1:Z" + CStr(lastRow)).Sort Key1:=Range("A1"), Order1:=xlAscending, Key2:=Range("F1"), Order2:=xlAscending, Key3:=Range("H1"), Order3:=xlAscending, Header:=xlYes
  
  Dim firstBuy As Long
  Dim lastBuy As Long
  Dim Buys() As LotData
  Dim numBuys As Long
  Dim i As Integer
  
  firstBuy = Columns("A").Find("Buy", , xlValues, , xlRows, xlNext).Row
  lastBuy = Columns("A").Find("Buy", , xlValues, , xlRows, xlPrevious).Row
  
  numBuys = lastBuy - firstBuy + 1
  
  ReDim Preserve Buys(numBuys - 1)
  
  i = 0
  
  For Row = firstBuy To lastBuy
      Set Buys(i) = New LotData
      Buys(i).Ticker = Cells(Row, 5)
      Buys(i).CUSIP = Cells(Row, 14)
      Buys(i).AcctNum = Cells(Row, 3)
      Buys(i).AcctName = Cells(Row, 4)
      Buys(i).Asset = Cells(Row, 6)
      Buys(i).OpenDate = Cells(Row, 2)
      Buys(i).StartDate = Cells(Row, 2)
      Buys(i).StartUnits = Cells(Row, 8)
      Buys(i).StartFMV = Cells(Row, 10)
      Buys(i).StartCB = Cells(Row, 10)
      Buys(i).CurrentFMV = Cells(Row, 10)
      Buys(i).CurrentUnits = Cells(Row, 8)
  
      i = i + 1
  Next
  
  Sheets("test").Activate
  
  Dim myRange As Range
  Set myRange = Sheets("test").Range("A1:L" &amp; numBuys)
  
  'Attempt 1
      'myRange = Buys
  'Run-time error 1004: Application-defined or object defined error
  
  'Attempt 2
      'Worksheets("test").Range(Range("A1"), Range("A1").Offset(UBound(Buys, 1), UBound(Buys, 2))).Value = Buys
  'UBound(Buys, 2) causes causes Run-time error 9: Subscript out of range
  
  'Attempt 3
      'Worksheets("test").Range("A1").Resize(UBound(Buys, 1) + 1, UBound(Buys, 2) + 1).Value = Buys
  'Same result as Attempt 2
  
  'Attempt 4
      'myRange.Resize(UBound(Buys, 1), UBound(Buys, 2)).Value = Buys
  'Same as Attempts 2 and 3
  
  'Attempt 5
  myRange.Resize(Ubound(Buys) + 1, 1).value = WorksheetFunction.Transpose(Buys)
  'Methond Transpose of object WorksheetFunction failed
  
  End Sub
  </code></pre>