Attempting to redefine an Array and getting Out of Range (error 9)

[DonEB]

Just when I think I understand how this works, the Out Of Range (error 9) pops up and I can't figure out why.

In a Subroutine called ReadChart I define and populate the following array. It also passes that array information onto another subroutine called PreComboSort:

Dim inarrayp As Variant                     'Array containing priority players only
inarrayp = Sheets("Sheet2").Range("AF5:AF24").Value2

PreComboSort inarray, inarrayp

As a result of the above, inarayp was defined as being one demensional with 20 rows. However, the values found in Sheets("Sheet2").Range("AF5:AF24") can range anywhere from 4, 8, 12, 16 or 20 numbers. In my example here, there were only 8 values in these cells and they were 1, 2, 3, 4, 5, 6, 7 and 11. The rest of the cells are presumed to be blank.

Upon passing this array to the PreComboSort subroutine, I was hoping to redefine the array so when I go to perform tasks such as "for each member", I will only be looking at rows where there are actual values to look at. So, I attempt to do just that with the following with the PrecomboSort subroutine:

Sub PreComboSort(ByRef inarray As Variant, inarrayp As Variant)
    Dim LastRowa As Long
    LastRowa = Sheets("Sheet2").Range("AF5").End(xlDown).Row

ReDim Preserve inarrayp(1 To LastRowa) As Variant

And the last line is where I get my error. I'm guessing there is something simple I'm forgetting but I'm having difficulty understanding what it might be. Any help would be appreciated.

Thanks,
Don

 

[Domenic]

When the values for a range of cells are transferred to a Variant variable, it creates a 2-dimensional array, not a 1-dimensional array. So inarrayp actually holds a 20-Row by 1-Column, 2-dimensional array. And so you can access an element within the array as inarrayp(1,1).

And when it comes to resizing an array with ReDim, you can only resize the last dimension of the array. So, in this case, you can only resize the second dimension. However, as a workaround, you can assign inarrayp a 1-dimensional array as follows...

inarrayp = Application.Transpose(Sheets("Sheet1").Range("AF5:AF24").Value2)

Hope this helps!

 

[bebo021999]

It seems like you're encountering a run-time error 9, which typically occurs when you're trying to access an array element that is out of its defined bounds. In your case, you're trying to redefine the inarrayp array using the ReDim Preserve statement, but the error is happening because the new size you're trying to assign to inarrayp is causing it to go out of bounds.

The issue appears to be with how you're determining the last row in your array. You're using the End(xlDown) method, which is meant for finding the last used cell in a column, not for determining the last row in your inarrayp array.

To dynamically resize the inarrayp array based on the number of values in your range, you can use the UBound function to find the number of values in your inarrayp array.
initial line:

 LastRowa = Sheets("Sheet2").Range("AF5").End(xlDown).Row

should be

LastRowa = UBound(inarrayp, 1)

 

[DonEB]

When the values for a range of cells are transferred to a Variant variable, it creates a 2-dimensional array, not a 1-dimensional array. So inarrayp actually holds a 20-Row by 1-Column, 2-dimensional array. And so you can access an element within the array as inarrayp(1,1).

And when it comes to resizing an array with ReDim, you can only resize the last dimension of the array. So, in this case, you can only resize the second dimension. However, as a workaround, you can assign inarrayp a 1-dimensional array as follows...

inarrayp = Application.Transpose(Sheets("Sheet1").Range("AF5:AF24").Value2)

Hope this helps!

This works perfectly. Thanks!

My only follow up... for educational purposes, you mentioned that "when the values for a range of cells are transferred to a Variant variable"... you are referring to inarrayp... correct? If so, being that the values within this range will ONLY be numeric, would there have been a better choice to use to define that variable and possibly avoid the use of the "Transpose"? I ask simply to try and find ways to make the code more efficient.

Thanks again!!

 

[Domenic]

This works perfectly. Thanks!

You're very welcome!

My only follow up... for educational purposes, you mentioned that "when the values for a range of cells are transferred to a Variant variable"... you are referring to inarrayp... correct?

Yes, that's correct.

If so, being that the values within this range will ONLY be numeric, would there have been a better choice to use to define that variable and possibly avoid the use of the "Transpose"?

I've re-read your original post and looked at your original code that creates the 2-dimensional array inarrayp. It seems to me that you can avoid having to both transpose and resize your array by looping through each element in the array and checking for an empty element like this...

Dim itm As Variant
For Each itm In inarrayp
    If Not IsEmpty(itm) Then
        'do something
    End If
Next itm

Or, better still, if the empty cells occur only after the last used cell, you can transfer those values to inarrayp like this...

With Sheets("Sheet2")
    inarrayp = .Range("AF5:AF" & .Cells(.Rows.Count, "AF").End(xlUp).Row).Value2
End With

Hope this helps!