Defining Array - Runtime error 9, Subscript out of range error

Hello everyone,

I'm new to this forum, came to here because I have a problem with some code and it giving me a headache. Most frustrating part is probably because it's about something basic as defining an array and populating it. I'm just doing something wrong but can't see it.

Below is the code with explenation

At the start of the code, I define 2 dynamical arrays (no problem here). In the first array I store client information and in the second I store some figures relevant to the client.

Please Login or Register  to view this content.

Because I don't want the array to be empty, I fill in some data in the first line of both arrays. (Still no problem)

Please Login or Register  to view this content.

Now the part that give me a headache. The program I created will loop through a long list of data. Each line in the list, I have to check if client data (mentioned in 2 different columns) is already added to the array. If it is, I store the location in the array for fast access or add the client information to the array. The code below is the part where I compare data from the list with data in the array.

Please Login or Register  to view this content.

When running the script, it stops on the line I marked in bold and gives Runtime error 9, Subscript out of range error. But I suspect the cause of this error is in the line below. I've added the MsgBox line code to see if RotationArrayCount contains wrong information what would cause the error, but it's not the case. First time it contains 1 and second time it contains 2 and that is what it should be.

I hope you guys see what I fail to see.

When using ReDim Preserve you can only re-dimension the final dimension of the array, whereas you're trying to redim the inner element.

So the amount of rows in the array has to be fixed all the time? (unless it's a one dimensional array).

Is there another way where I can add rows to an array without knowing the exact amount of rows on beforehand? Or do I have to flip the array around and start adding columns for each new customer I find.

You can redim a 2-dimensional array, you just can't do it while preserving the values.

So, your options are:

1. Switch the array around, so you're redimming the final dimension
2. Define the array to an arbitrarily large size and maintain a counter of how many 'rows' are actually used.
3. Fill the array using two loops, one to set the size (ReDim without preserve) and one to populate the data
4. Write your data to a range and then convert the range to a 2D array

Personally I'd go with option 4.

Meanwhile I already started changing my code and tried option 1. In theory it doesn't matter for me if I have to increase columns or rows. As long as I can keep the data that is already in the array. Good news for me is that now my code works.

So thanks for your help!