Marc
Marc

Reputation: 45

Runscript Error '9' Subscript out of range

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

Answers (2)

user4039065
user4039065

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

QHarr
QHarr

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

Related Questions