Adding to a new variable array from an existing array

[Aussiexile]

Hi Folks,

I'm having a bit of dilemma, I am trying to populate an array based on parts of a string.

I have an array that contains data like this:

Action1-Item1
Action1-Item2
Action1-Item3-ID
Action1-Item4
Action1-Item5
Action2-Item1-ID
Action2-Item-ID2

What i am trying to do is loop through the original array, strip out the ID's and add them to a new array without adding duplicates.

The kicker is the original array is subject to change and the amount with ID's is very likely to change so the second array needs to be variable. now what i have currently is:

Please Login or Register  to view this content.

ListArray is a global array

Now my issue is that when i run the code i get an error that says:
Compile Error
Array already Dimensioned.

Any help would be greatly appreciated.

[apo]

Hi..

IDArray is a OneDimensional Array.

You can only Redim Preserve the last Dimension in a Multidimensional Array.

[apo]

Try this.. if it is not exactly what you want.. hopefully you can adapt it..

It will loop through your ListArray array.. splitting each element. if the Upper bound of the array = 2 it will then add that ID value into the Dictionary.

Simple case of transposing the unique Keys of the Dictionary to the sheet at the end to get your new single dimension array (Z) shown.

Please Login or Register  to view this content.

[romperstomper]

Hi..

IDArray is a OneDimensional Array.

You can only Redim Preserve the last Dimension in a Multidimensional Array.

Nope - you can do it with a 1D array but it must be declared as a dynamic array. Here it has been declared with fixed dimensions so it can't be resized at all, with or without preserve.

[apo]

Hi Rory..

Here it has been declared with fixed dimensions so it can't be resized at all, with or without preserve.

I get that part now that you have pointed it out.. thanks

But..

you can do it with a 1D array but it must be declared as a dynamic array

Can you elaborate on this part please.. I usually only 'play' with 2 dimensional arrays.. and was of the thinking that only the second dimension could be Preserved.. I am keen to learn how to do what you are saying..

[romperstomper]

If you use Preserve you can only resize the last dimension. For a 1D array, the only dimension is also the last dimension, so:

Please Login or Register  to view this content.

for example.

[apo]

Got it .. thanks

[Aussiexile]

Hi Folks,

Thanks for all the replies, I did not know that about dimensioning of arrays!!

Is there an easier way to to temporarily store data for comparison before writing to the worksheet?

I have never used the Dictionary before. Can anyone direct me to a good eBook source for VBA coding with a slant towards excel?

Thanks once again!!

[apo]

Did you try the attached Workbook i posted..? it should pretty much do what you asked for..

Is there an easier way to to temporarily store data for comparison before writing to the worksheet?

For what you want to do.. loop thru array.. strip out ID's and build a new array without dupes.. I can't think of anything that is direct as what i posted..

Note: I only transposed the Z array to sheet to show the values.. you can whatever you want other than that with the resulting Z array..

As for books.. not sure.. reading posts on Excel Help forums and googling is how i learned..

[Aussiexile]

Thanks Apo,

Worked a treat!!