Reputation: 45
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.
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
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
Upvotes: 1
Views: 104
Reputation:
Let's say ListRange.Cells.Count is 9. Arrays by default are zero-based, not one-based.
ReDim myArray(ListRange.Cells.Count)
redims to 0 to 9, a total of 10 array elements.
The following code populates myArray(0) to myArray(8).
For Each cell In ListRange.Cells
myArray(x) = cell.Value
x = x + 1
Next cell
myArray(9) is empty.
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.
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
.
Upvotes: 2
Reputation: 84465
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
ReDim myArray(ListRange.Cells.Count-1)
Also, use explicit sheet references not Activesheet and correct the syntax for
ActiveSheet("Sheet4")
Perhaps,
Worksheets("Sheet4")
Upvotes: 1