Reputation: 197
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
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