Assign values to array in for loop VBA

Question

Can any one kindly check the code, I explained the code in comments.

I am getting run error 13

My concern in the assign method that I am using. I want to assign the cell value in the array starting form position 0

Thank you very mush

Sub zones()

Dim Top10zones(0 To 9) As Long
Dim found As Boolean

Top10zones(0) = 309101502

NumRows = Range("A1", Range("A1").End(xlDown)).Rows.count

Dim Arry As Variant

Dim count As Integer
count = 0

' find the 10zones in row 1.
Range("A1").Select
    Do Until IsEmpty(ActiveCell)
         If ActiveCell.Value = Top10zones(0) Then
            found = True
            Exit Do
         End If

         ActiveCell.Offset(0, 1).Select
      Loop
   ' if found move to row 2 in the same cloumn.
      If found = True Then
      ActiveCell.Offset(1, 0).Select

      For j = 2 To NumRows
      If ActiveCell.Value <= 1 Then
         ' if the value <= 1 assign cells(j,1) to the array.
        Arry(count) = Cells(j, 1).Value
        count = count + 1
      End If
      ActiveCell.Offset(1, 0).Select

      ' repeat the process.
      Next j


      End If

End Sub

Side note: see [this question](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — BigBen, Jan 27 '20 at 13:05
`Arry` isn't magically an array, so `Arry(count) = Cells(j, 1).Value` is problematic. — BigBen, Jan 27 '20 at 13:06
a) Use a worksheet qualifier when using `Cells` method. b) I don't see you dimension your array anywhere? — Dean, Jan 27 '20 at 13:08
@BigBen `Arry(count) = Cells(j, 1).Value` what is the way to fix it. Thank you all — abdalrhman Najib, Jan 28 '20 at 05:33

jclasley · Answer 1 · 2020-01-27T15:20:00.160

In VBA, arrays are initialized with a fixed length. For instance, an array of length 1 will only have a Array(0) index and none others. If you want to increase the size of the array (without destroying the current contents of the array), you need to use ReDim Preserve Arry(new_length). So if you ReDim Preserve Arry(10) your array is now ready for 10 elements, and if you had any elements already in the array they are preserved.

There are two ways to initialize an array.

1)

Dim myArry(0 to 1) as String

This is an array with two empty elements ready to accept a string.

2)

Dim myArry as Variant
myArry = Array("", "")

This is an array with two "empty" element ready to accept a string.

To change the length:

For i = 1 to 10
    ReDim Preserve myArry(i)
    myArry(i) = 'whatever
Next i

@sjr good point about 1, it was early in the morning for me. To your second point, I just wanted to illustrate that it's an empty Variant that then becomes an array. — jclasley, Jan 27 '20 at 15:18

Assign values to array in for loop VBA

1 Answers1