Subscript out of range error on array

Question

I am trying to read a list of ids (integers) into a one dimensional array in an excel macro.

I have counted the number of IDs that will need to be inputted in a previous step therefore know what my array size needs to be. Now I want to create an array and read the values into that array. Below is the code that is giving me a "runtime error 9, subscript out of range" message on the line where I am reading the "RowID" into the array. Please let me know what I am doing incorrectly.

Notes: PD_incD is the count of ids to be read into array.

Dim AIN As Integer 'Array index
Dim PD_IncD_Array() As Variant
ReDim PD_IncD_Array(1 To PD_IncD) As Variant 'Create array for recording section IDs

AIN = 1 'Reset value
RowID = PD_1stRow 'Reset first row to begin search

Do While Not IsEmpty(Worksheets("Piping Data").Cells(RowID, 3).Value)
    If Worksheets("Piping Data").Cells(RowID, 3).Value = 1 Then
        PD_IncD_Array(AIN, 1) = RowID
        AIN = AIN + 1
    Else
        RowID = RowID + 1
    End If
Loop

MsgBox (PD_IncD_Array(AIN, 1))

Automate This · Accepted Answer · 2015-06-15 21:50:25Z

1

The error is correct but the cause is related to your logic.

You never increment RowID if the first part of your if statement is true. This leaves you with an infinite loop but since you're assigning a value to an array you get an out of bounds error first.

I'm not sure your exact logic but I'm guessing you want to increment the RowID every loop like this:

Do While Not IsEmpty(Worksheets("Piping Data").Cells(RowID, 3).Value)
    If Worksheets("Piping Data").Cells(RowID, 3).Value = 1 Then
       ' ReDim Preserve PD_IncD_Array(UBound(PD_IncD_Array) + 1)
        PD_IncD_Array(AIN, 1) = RowID
        AIN = AIN + 1
    End If

    RowID = RowID + 1
Loop

edited Jun 15, 2015 at 21:50

answered Jun 15, 2015 at 21:38

Automate This

31.5k11 gold badges63 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

joehanna · Accepted Answer · 2015-06-15 20:42:31Z

0

You are referencing the array as though it had 2 dimensions when you use (x, y) syntax. You only have an array with 1 dimension so you will get that error. Just remove the ", 1" - it has no purpose here.

answered Jun 15, 2015 at 20:42

joehanna

1,4891 gold badge11 silver badges23 bronze badges

Comments

sancho.s ReinstateMonicaCellio · Accepted Answer · 2015-06-15 20:59:31Z

0

You should be able to assign PD_IncD_Array, for AIN between 1 and PD_IncD. You are likely not satisfying this condition.

Check the values of PD_IncD, and AIN at error (you may use MsgBox or debug). Also clarify the line throwing the error.

answered Jun 15, 2015 at 20:59

sancho.s ReinstateMonicaCellio

15.8k25 gold badges110 silver badges214 bronze badges

Collectives™ on Stack Overflow

Subscript out of range error on array

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related