时间戳

首页 > 解决方案 > VBA：如何使用 for 循环将数组添加到锯齿状数组

arrays - VBA：如何使用 for 循环将数组添加到锯齿状数组

问题描述

想象一下下面的电子表格，但您不确定会有多少行：

样本电子表格

现在您需要创建一个包含其他数组（称为ArticleArray）的数组，以便它的输出如下所示：

ArticleArray = {{"Fake Outlet 1","9/1/2020","Fake headline 1"},{"Fake Outlet 2","9/2/2020","Fake headline 2"},{"Fake Outlet 3","9/3/2020","Fake headline 3"}}

您可以在下面看到我已经编写的代码示例：

Sub Pull_News()

    Dim i As Long

    'Finding the last row of the news table and adding borders
    Dim lRow As Long
    lRow = Sheets("News Archive").Cells(Rows.Count, 1).End(xlUp).Row
    
    'Creating a jagged array that will storeeach article array
    Dim ArticleArray() As Variant

    'Lopping through each article and creating an array
    For i = 2 To lRow - 1
        
        'Creating a temporary news array
        Dim Article() As Variant
        
        'Filling Temporary Array
        Article = Sheets("News Archive").Range("A" & i & ":" & "C" & i).Value
        
        'Adding temporary array to the jagged array
            
        'change / adjust the size of array
        ReDim Preserve ArticleArray(1 To UBound(ArticleArray) + 1) As Variant
        
        ' add value on the end of the array
        ArticleArray(UBound(ArticleArray)) = Article
        
    Next i

End Sub

我在以下行收到以下错误消息：

ReDim Preserve ArticleArray(1 To UBound(ArticleArray) + 1) As Variant

Run-time error 9: Subscript out of range

标签： arraysexcelvbavariant

解决方案

  1. Article不是 aRange也没有 a .Value。
  2. ReDim Preserve价格昂贵。最好在循环外调整一次数组的大小：
Dim ArticleArray() As Variant
ReDim ArticleArray(0 To lrow -2) 'size the array outside the loop

Dim j As Long

'Looping through each article and creating an array
For i = 2 To lRow
        
    'Creating a temporary news array
    Dim Article() As Variant
        
    'Filling Temporary Array
    Article = Sheets("News Archive").Range("A" & i & ":C" & i).Value
        
    'Adding temporary array to the jagged array
    ArticleArray(j) = Article
    j = j + 1
        
Next i

推荐阅读