excel - 循环写入数组

Question

首先感谢您的帮助

我编写了一个代码，它遍历特定​​列，然后开始获取该列中的所有值 <=1 并将其分配给特定数组。我想自动化这个过程。请看一下代码

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

我最后遇到的问题，如何在 for 循环中分配给不同的数组

标签： excelvba

Answer 1

您需要将数组放入该数组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

所以每个 off 都Arry(i)包含一个不同大小的数组。

所以……</p>

• Arry(0)有一个数组0 To 1
• Arry(1)有一个数组0 To 2
• Arry(2)有一个数组0 To 1
• Arry(3)有一个数组0 To 3
• Arry(4)有一个数组0 To 5

例如Debug.Print Arry(4)(3)将输出separated哪个是项目 3 inArry(4)