Assign values to array based on cells in sheet

Question

I am trying to assign values from column A to an array.

I want the size of the array to be dynamic. I want to loop through each cell and assign every cell value to my array.

Sub exercise3()
    Dim asnwer
    Dim output
    Dim lastrow
    Dim test
    Dim i
    Dim Data() As Variant 'Creating an dynamic array
    Dim endnumber

    lastrow = cells(Rows.Count, 1).End(xlUp).Row 'Find last row in column A

    MsgBox (lastrow)

    For i = 1 To lastrow
        Data(i) = cells(i, "A").Value 'I want to assign each cell value to my array
    Next i

    MsgBox (Data(6))

    Set output = cells(4, 4)
    answer = WorksheetFunction.Average(Data)

    output.Value = answer

End Sub

I get an error in my loop

"subscript out of range".

I have 14 rows in column A. If I declare the array with 14 positions/variables like this: Dim data (14), it is working. However I want it to be dynamic, so I can add/remove rows.

Scott Craner · Accepted Answer · 2019-10-21 18:41:07Z

1

no loop needed just assign the whole to the array at once

Data = ActiveSheet.Range("A1:A" & lastrow).Value

Then the only thing you need to remember is that it is a 2 dimensional array with 1 column so:

msgbox Data(6,1)

Sub exercise3()
    Dim asnwer As Double
    Dim output As Range
    Dim lastrow As Long
    Dim Data() As Variant 'Creating an dynamic array


    lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row 'Find last row in column A

    MsgBox lastrow

    Data = ActiveSheet.Range("A1:A" & lastrow).Value

    MsgBox Data(6, 1)

    Set output = ActiveSheet.Cells(4, 4)
    answer = WorksheetFunction.Average(Data)

    output.Value = answer


End Sub

answered Oct 21, 2019 at 18:41

Scott Craner

154k10 gold badges52 silver badges88 bronze badges

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

3 Comments

Noah Over a year ago

Hi scott, First of all, thank you so much for your answer! I was actually able to solve it this way. However, here we are not using an array, but a range - right? I cannot understand, why I am not able to loop through cells and put these values in my array? :)

Scott Craner Over a year ago

No, you are mass assigning the values from the range into an array and as such you are then using the array not the range. You can change the values in the array and it will not cause the range values to change it is a copy of values in the range. As to why, an array must be assigned the number of items when loading one by one before trying to load it. You will need to ReDim Data(1 to lastrow) as variant after getting the value for lastrow, but looping will be slower on large data sets. The quickest and easiest is to load in mass.

Noah Over a year ago

Scott Craner! Thank you so much! :) This make so much sense. I also tried the "loop" solution by using Redim data (1 to lastrow) and it is working :)

Collectives™ on Stack Overflow

Assign values to array based on cells in sheet

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related