Using a for loop to append to array in Excel VBA Basic

Question

How do I loop through a sheet using IF statements, and for each TRUE, append it to an array?

Basic example, if Cells(y, 1).Value is greater than 0, then append 1 to an array and do so through the given range creating an array with multiple values of 1 (given multiple Cells(y, 1).Value(s) is greater than 0).

This is how I've created loops before.

For y = 2 To LastRow

    On Error Resume Next
    If Cells(y, 1).Value > 0 Then   
        Cells(y, 2).Value = 1     ' Instead of populating Cells(y,2) with "1" IF true, I want to append the value to an array
    ElseIf Cells(y, 1).Value = 0 > 0 Then 
        Cells(y, 2).Value = 2
    Else
        Cells(y, 2).Value = 0
    End If

Next y

Matt Cremeens · Accepted Answer · 2019-10-24 21:06:00Z

5

You have to first dimension an array

Dim myArray() as Integer

And inside your loop, keep track of the number of elements the array will have

Dim myCount as Integer

Then inside your loop you must increment this counter and redimension the array so you can add to it

If Cells(y, 1).Value > 0 Then   
    myCount=myCount+1
    Redim Preserve myArray(1 to myCount)
    myArray(myCount)=1

The Preserve reserved word is important as it prevents the contents of your array from being reinitialized as you add items to it.

edited Oct 24, 2019 at 21:06

answered Oct 24, 2019 at 20:50

Matt Cremeens

5,2118 gold badges42 silver badges71 bronze badges

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

4 Comments

Vichtor Over a year ago

Thanks Matt for the quick, clear and helpful reply!

Matt Cremeens Over a year ago

My pleasure. Glad I could help.

Justyna MK Over a year ago

@Vichtor remember to mark Matt's answer as the correct one. See link for more info.

T.M. Over a year ago

Some hints to your answer: a) "..inside your loop you must increment this counter and redimension the array" - Just dimension the array to the count of rows/items (minus 1 as it's 0-based) and eventually do the Redim Preserve once outside the loop. b) BTW Assigning a range's value to a temporary variant array and looping through it would be less time consuming than to loop through a range. c) Why do you assign myArray(myCount)= to 1 instead of the range value? d) Why do you fill item values of a zero-bound array starting from 1? - @MattCremeens

Collectives™ on Stack Overflow

Using a for loop to append to array in Excel VBA Basic

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related