CODEWITHSUNDEEP
vbaexcel
Gary M
Gary M

Reputation: 1

Subscript out of range error on array

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

Upvotes: 0

Views: 411

Answers (3)

Automate This
Automate This

Reputation: 31364

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

Upvotes: 1

sancho.s ReinstateMonicaCellio
sancho.s ReinstateMonicaCellio

Reputation: 15561

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.

Upvotes: 0

joehanna
joehanna

Reputation: 1489

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.

Upvotes: 0

Related Questions