Runtime error '9'. Subscripts out of range - Dynamic array

**akabraha** · 08-23-2007, 10:54 AM

Hello,

I have defined a dynamic array called Steps(). I am trying to store the value of a cell in it. But I am getting the error: Runtime error '9'. Subscripts out of range. Not sure what's wrong?


X = Range(ActiveCell.Address, ActiveCell.End(xlDown)).Count

Dim Steps() As Integer

For j = 1 To X Step 1

    Steps(j - 1) = ActiveCell.Offset(0, 7 + j).Value
    MsgBox Steps(j - 1)

Next j

Thanks in Advance.. Anil

**akabraha** · 08-23-2007, 11:14 AM

Thanks for looking into it - I figured it out. Had to redim with preserve..


X = Range(ActiveCell.Address, ActiveCell.End(xlDown)).Count
Dim Steps() As Integer
For j = 1 To X Step 1
    ReDim Preserve Steps(0 + j)
    Steps(j - 1) = ActiveCell.Offset(0, 7 + j).Value
    MsgBox Steps(j - 1)

Next j

Thanks, Anil

**mudraker** · 08-23-2007, 05:38 PM

You could also use

Dim Steps(1 to  X) as String
For j = 1 To X Step 1
    Steps(j) = ActiveCell.Offset(0, 7 + j).Value
    MsgBox Steps(j)
Next j

Note:- the array starts at 1

Here are 2 others ways to fill an array from a sheet without looping through the cells

Dim Steps As Variant
Dim X As Long
X = Range(ActiveCell.Address, ActiveCell.End(xlDown)).Row
Steps = Range(ActiveCell.Address, Cells(X, ActiveCell.Column)).Value

Dim Steps As Variant
Dim Rng As Range

Set Rng = Range(ActiveCell.Address, ActiveCell.End(xlDown))

Steps = Range(ActiveCell.Address, Rng.Address).Value
Set Rng = Nothing

Runtime error '9'. Subscripts out of range - Dynamic array

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Runtime error '9'. Subscripts out of range - Dynamic array

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions