Runscript Error'9'下标超出范围

I've been trying to wrap my head around using loops and arrays and have put together the below example which takes worksheet names from a table on a worksheet and stores them in array from which another loop runs to add a value in cell A1 in those named spreadsheets based on a value in cell D1 found on the activesheet.

我一直在尝试使用循环和数组包围我的头并将下面的示例放在一起,该示例从工作表上的表中获取工作表名称并将它们存储在数组中,从该数组中运行另一个循环以在单元格A1中添加值基于活动表上的单元格D1中的值命名的电子表格。

I keep getting a runtime error but I cannot identify what the value the code is looking that keeps tripping up.

我不断收到运行时错误,但我无法确定代码看起来持续绊倒的价值。

The error seems to be located on this line:

该错误似乎位于此行:

Sheets(myArray(x)).Range("A1").Value = EntryValue

表格(myArray(x))。范围(“A1”)。值= EntryValue

Any help on what I've not done correctly is greatly appreciated.

我非常感谢您对我未正确完成的任何帮助。

Thanks.

Here's the code:

这是代码:

Sub WorksheetListLoop()

    Dim myArray() As Variant
    Dim EntryValue As String
    Dim ListRange As Range
    Dim cell As Range
    Dim x As Long

    'Set the values to go into range
    Set ListRange = ActiveSheet.ListObjects("tblArrayList").DataBodyRange

    'Resize array prior to loading data
    ReDim myArray(ListRange.Cells.Count)

    'Loop through each cell in range and store sheetname in array
    For Each cell In ListRange.Cells
        myArray(x) = cell.Value
        x = x + 1
    Next cell

    'Use the value in this cell to put into the sheets in the array
    EntryValue = ActiveSheet.Range("D1").Value

    'Loop through list and add value to cell
    For x = LBound(myArray) To UBound(myArray)
        Sheets(myArray(x)).Range("A1").Value = EntryValue
    Next x

End Sub

2 个解决方案

#1

Your array is 0 based but you are doing 0 to .Cell.Count so have an empty position causing the error. Do to .Cells.Count -1

你的数组是基于0的,但你正在执行0到.Cell.Count,所以有一个空位置导致错误。做.Cells.Count -1

ReDim myArray(ListRange.Cells.Count-1)

Also, use explicit sheet references not Activesheet and correct the syntax for

此外,使用显式工作表引用而不是Activesheet并更正其语法

ActiveSheet("Sheet4")

Perhaps,

Worksheets("Sheet4")

#2

Let's say ListRange.Cells.Count is 9. Arrays by default are zero-based, not one-based.

假设ListRange.Cells.Count为9.默认情况下,数组是从零开始的,而不是从一开始的。

ReDim myArray(ListRange.Cells.Count) redims to 0 to 9, a total of 10 array elements.

ReDim myArray(ListRange.Cells.Count)redims为0到9,总共10个数组元素。

The following code populates myArray(0) to myArray(8).

以下代码将myArray(0)填充到myArray(8)。

For Each cell In ListRange.Cells
    myArray(x) = cell.Value
    x = x + 1
Next cell

myArray(9) is empty.

myArray(9)是空的。

This code loops through every element including the one that is empty.

此代码循环遍历每个元素,包括空元素。

For x = LBound(myArray) To UBound(myArray)
    Sheets(myArray(x)).Range("A1").Value = EntryValue
Next x

On the last iteration when x equals UBound(myArray), you are trying to reference an empty array element.

在x等于UBound(myArray)的最后一次迭代中,您尝试引用一个空数组元素。

Easiest solution: put Option Base 1 at the top of the module sheet with Option Explicit and move x = x + 1 above myArray(x) = cell.Value.

最简单的解决方案:使用Option Explicit将Option Base 1放在模块表的顶部,并在myArray(x)= cell.Value上移动x = x + 1。

#1