Hi,

I'm trying to transfer selected data from 1 array to another, but I keep getting an error called "Subscript out of range". Below is my code. vaTest is the original array. vaSelect is the another array that should contain data from vaTest if the value is greater than 10. Does anyone have any ideas on how to get rid of the error?


Option Base 1
Sub test()

Dim vaTest As Variant
Dim vaSelect()

vaTest = Range("A3:A10").Value
j = 1
For i = 1 To UBound(vaTest, 1)
    If vaTest(i, 1) > 10 Then
        vaSelect(j, 1) = vaTest(i, 1)
        j = j + 1
    End If
Next i
End Sub

because the dimensions are not set in the declarations you are experiencing this error. try...

Dim vaSelect(10, 1)

cheers

Matthew

hi,

u should try this:

Option Base 1
Sub test()

Dim vaTest As Variant
Dim vaSelect()
sheets(x).select '''x represents the sheet where you need to work on
'i think you cant use the Range("A3:A10").Value without first selecting the sheet of the ranges
vaTest = Range("A3:A10").Value
j = 1
For i = 1 To UBound(vaTest, 1)
    If vaTest(i, 1) > 10 Then
        vaSelect(j, 1) = vaTest(i, 1)
        j = j + 1
    End If
Next i
End Sub

If you know the array size you are going to need, Matthew’s answer will solve your problem. (You would still need to incorporate Matthew’s answer into maxpotters’ answer to avoid the error you’re getting, because you still would have an array with no dimensions or elements...)

(It looks as if you never use more than 1 dimension of vaSelect, so I am going to address this presuming a 1-dimension array is suitable.) If you do not know in advance what the size of the array needs to be, you can declare it as you have been doing, then immediately resize it to have 1 element Then, in your loop, repeatedly resize the array to accomodate what you are about to add to it, using the keyword ‘Preserve.’

When you resize an array, a different contiguous block of memory is allocated for the array. That is the end of the story if you do not specify ‘Preserve.’ But if you do specify ‘Preserve,’ then VB (and VBA) follows that step by copying all of the former data into the new block of memory before relinquishing its hold on the old memory. If a couple of numbers get skipped because of the > 10 test, when the array is redimmed, the elements in between that last one that was filled in and the current one getting filled in will be initialized to the default value of the data type of the array. Since you have not specified a data type, the array is an array of Variants, so the interposing elements will be equal to Variant(Empty). If you specify the array as a numeric type they will equal zero.

Thanks Guys -- I now have a better understanding of "Redim Preserve". My code now works.

I'm trying to increase my knowledge of arrays and so I'm experimenting with code. I know that its possible to have multi-dimensional arrays so I've altered my original code so that it now has two dimensions.

Basically what I'm trying to do is: I have a spreadsheet with 10 rows and 2 columns. I then want to transfer some of this data into an array where the contents in column A is greater than 10. As I don't know how many items will be greater than 10, I've used a dynamic array. I thought it would be a simple process of altering my original code by putting in a reference to the column number. But I get the "Subscript out of range" error when I try to Redim the array. I've read that the "Redim Preserve" doesn't allow you to change the number of columns, and so I'm confused as to how to "Redim Preserve" an array to increase the number of rows, without specifying the column index.

Hopefully someone can clarify this for me. Below is my altered code:

Option Base 1
Sub test()

Dim vaTest As Variant
Dim vaSelect()

Worksheets("Sheet1").Select
vaTest = Worksheets("Sheet1").Range("A1:B10")

j = 1
For i = 1 To UBound(vaTest, 1)
    If vaTest(i, 1) > 10 Then

        ReDim Preserve vaSelect(j, 2)

        vaSelect(j, 1) = vaTest(i, 1)
        vaSelect(j, 2) = vaTest(i, 2)
        j = j + 1
    End If
Next i
End Sub

You can only increase the size of an array using the Preserve keyword if you are re-sizing the last dimension of that array.

If you have VB Help installed, place your cursor on the Preserve keyword in the VB editor and press the F1 key. You'll find there a decent explanation of how this works.