title: Excel vba assign values to array and then paste to worksheet
body: <p>I have a very large spreadsheet that I want to do calculations on. In order to speed this up I want to do them in vba. I have a basic knowledge of vba so to start I was trying to write code that would simply copy the cells in column A and assign them to an array and then paste all the values back into another column D. I defined numrows to get the number of rows down as this will be changing from month to month. I thought if I could get this to work I could build it up from there - but unfortunately I can't get this to work. If anyone could tell me what I'm doing wrong I'd really appreciate it. </p>
  
  <p>I have tried many variations of this - at the moment I'm getting the error Run Time Error '424' object required so I think the array is empty. </p>
  
  <pre><code>Option Explicit
  Option Compare Text
  Option Base 1
  
  
  Sub Macro1()
  
  Dim numRows As Long
  
  Dim numCols As Integer
  numCols = 1
  
  Dim RowCounter As Long
  Dim ColCounter As Integer
  Dim SumCols() As Variant
  numRows = Cells(Rows.Count, "A").End(xlUp).Row
  ReDim SumCols(numRows, numCols)
  Dim tempSumCols As Variant
  
  tempSumCols = Range("A2", Cells(numRows, 1))
  
  For RowCounter = 1 To numRows
      For ColCounter = 1 To numCols
          SumCols(RowCounter, ColCounter) = tempSumCols(RowCounter, ColCounter).Value
      Next ColCounter
  Next RowCounter
  
  Range("D2", Cells(numRows, "D")) = SumCols
  
  
  
  
  
  End Sub
  </code></pre>