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

What line are you getting the error on? We shouldn't have to guess. — John Coleman
– John Coleman, Commented Jun 25, 2016 at 16:19
Apologies, this is my first time posting a question, I will ensure that I include where the error is in future questions. — A.K.C
– A.K.C, Commented Jun 26, 2016 at 11:11

dbmitch · Accepted Answer · 2016-06-25 16:29:58Z

2

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

answered Jun 25, 2016 at 16:29

dbmitch

5,3964 gold badges27 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

A.K.C Over a year ago

Thanks for this, it removed the error but there was still a problem with my code, so I went with Thomas Inzina code which was way more efficient than mine

user6432984 · Accepted Answer · 2016-06-25 16:22:07Z

0

The simplest way to fill an array from a range:

declare the array as a variant
create a range of the correct shape
array = range().value

You can now iterate over the array using

For i = lBound(array) to uBound(array)

or

For each e in array

Sub Macro1()

    Dim SumCols As Variant
    Dim e As Variant

    SumCols = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp)).Value

    For Each e In SumCols

    Next

    Range("D2").Resize(UBound(SumCols, 1), UBound(SumCols, 2)) = SumCols

End Sub

answered Jun 25, 2016 at 16:22

user6432984

1 Comment

A.K.C Over a year ago

Thanks a million, this worked and is really efficient.

Collectives™ on Stack Overflow

Excel vba assign values to array and then paste to worksheet

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related