CODEWITHSUNDEEP
excelvbaappend
Vichtor
Vichtor

Reputation: 197

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

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

Upvotes: 4

Views: 6032

Answers (1)

Matt Cremeens
Matt Cremeens

Reputation: 5151

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.

Upvotes: 5

Related Questions