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
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
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
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
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。