Excel vba assign values to array and then paste to worksheet

Question

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.

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.

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

Answer 1

To fix your code, you have to match numRows in both arrays Because you start at A2 - your row index neds to be incremented

And also - remove .Value from second array

This should work - or at least remove runtime error:

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 = Range("A" & Rows.Count).End(xlUp).Row
ReDim SumCols(numRows, numCols)
Dim tempSumCols As Variant

' Increment number of rows to match numrows starting at A2
tempSumCols = Range("A2", Cells(numRows + 1, 1))

For RowCounter = 1 To numRows
    For ColCounter = 1 To numCols
        ' Remove .Value from tempSumCols
        SumCols(RowCounter, ColCounter) = tempSumCols(RowCounter, ColCounter)
    Next ColCounter
Next RowCounter

Range("D2", Cells(numRows, "D")) = SumCols

End Sub