paul_0722
07-27-2008, 09:19 PM
I just can't seem to get my head around ReDim Preserve. Other languages have a nice array.count function that you just call and then re-dimension your array to whatever it returns - I guess that's just not the case with Excel VBA unless you write a special function to do so (and I don't want to) so my thinking is I'll redimension and preserve data within the loop I'm using to create a second array... am I on the right track here?
My immediate dilemma - I have two 2 multi dimension arrays and I need to pull certain records from Array 1 when the value equals 'x' and place them in Array 2. I have no way of knowing the what first index dimension value will be needed for Array 2. I keep getting a 'subscript out of range' error if I use ReDim, but it works fine if I dimension the array with a good guess of how many are needed. I'm obviously using ReDim wrong... where should it go within the loop?
The attached code throws the error, but if you uncomment the Dim
ar02() code at the top and use ar02(15,5) and then comment out the ReDim Preserve within the loop - it will work and return worksheet "Sheet1" as expected - albeit with a few too many rows...
Any help appreciated!
My immediate dilemma - I have two 2 multi dimension arrays and I need to pull certain records from Array 1 when the value equals 'x' and place them in Array 2. I have no way of knowing the what first index dimension value will be needed for Array 2. I keep getting a 'subscript out of range' error if I use ReDim, but it works fine if I dimension the array with a good guess of how many are needed. I'm obviously using ReDim wrong... where should it go within the loop?
The attached code throws the error, but if you uncomment the Dim
ar02() code at the top and use ar02(15,5) and then comment out the ReDim Preserve within the loop - it will work and return worksheet "Sheet1" as expected - albeit with a few too many rows...
Any help appreciated!