Write to arrays in a loop

Question

firstly thank you for your help

I wrote a code that go through a specific columns then start to get all the values <=1 in that column and assign it to a specific array. I want to automate this process. Please take a look to the code

Sub zones()

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

Dim temp As Variant
Dim Arry0 As Variant
Dim Arry1 As Variant
Dim Arry2 As Variant
Dim Arry3 As Variant
Dim Arry4 As Variant
Dim Arry5 As Variant
Dim Arry6 As Variant
Dim Arry7 As Variant
Dim Arry8 As Variant
Dim Arry9 As Variant


Top10zones(0) = 309101502
Top10zones(1) = 309101802
Top10zones(2) = 106900101
Top10zones(3) = 9082004
Top10zones(4) = 407100901
Top10zones(5) = 2056355
Top10zones(6) = 5075001
Top10zones(7) = 10092021
Top10zones(8) = 5075005
Top10zones(9) = 205701516

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

For i = 0 To 9
Range("A1").Select
    Do Until IsEmpty(ActiveCell)
         If ActiveCell.Value = Top10zones(i) Then
            found = True
            Exit Do
         End If

         ActiveCell.Offset(0, 1).Select
      Loop
   ' Check for found.
      If found = True Then
      ActiveCell.Offset(1, 0).Select
      For j = 2 To NumRows
      If ActiveCell.Value <= 1 Then
      temp = temp & "," & Cells(j, 1)
      End If
      ActiveCell.Offset(1, 0).Select
      Next j
      Arryi = Split(Mid(temp, 2), ",")

      End If




End Sub

The problem that I have at the end, how can assign to different arrays in a for loop

You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). • Instead of defining `Arry0` … `Arry9` you must declar it as an array `Dim Arry(0 To 9) As Variant` so you can use it in a loop like `Arry(i)`. Actually exactly like you did with `Top10zones`. • But make sure you get rid of all these `Select` statements (see link). — Pᴇʜ, Jan 27 '20 at 07:00
Thank you for your Suggestion. But I want to create 10 different arrays (unknown size) — abdalrhman Najib, Jan 27 '20 at 07:14
You can, that's just an array in an array. See my example below. — Pᴇʜ, Jan 27 '20 at 07:32

score 1 · Accepted Answer · answered Jan 27 '20 at 07:31

You need to put arrays into that array Arry.

Option Explicit

Public Sub Example()

    Dim SplitText(0 To 4) As String
    SplitText(0) = "This is"
    SplitText(1) = "a test string"
    SplitText(2) = "array where"
    SplitText(3) = "the items have different"
    SplitText(4) = "numbers of words separated by spaces"

    Dim (0 To 4) As Variant

    Dim i As Long
    For i = 0 To 4
        Arry(i) = Split(SplitText(i), " ")
    Next i
End Sub

So each off Arry(i) contains an array of a different size.

So …

Arry(0) has an array of 0 To 1
Arry(1) has an array of 0 To 2
Arry(2) has an array of 0 To 1
Arry(3) has an array of 0 To 3
Arry(4) has an array of 0 To 5

And Debug.Print Arry(4)(3) for example will output separated which is the item 3 in Arry(4)

Write to arrays in a loop

1 Answers1