Simple loop & array question.

[randomdude]

So, all I want to do is a Do Until the end of an array loop. Here's my current code:

Please Login or Register  to view this content.

I get the subscript out of range error.

[TMS]

What are you trying to do?

Suggest you post a sample workbook and describe the process. Might be other options.

Regards, TMS

[randomdude]

Alright, there are some teams in the 6th column, starting from the 8th row, and I want to add each one to the allTeams array, but only if the team isn't already in the array, therefore the same team won't be added 2 or more times into the allTeams array.

Here's more of the code (changed i-7 to j to keep it more easy to read):

Please Login or Register  to view this content.

[TMS]

Three options:

1. A Collection
2. Create a Dictionary
3. Use an Advanced Filter to extract the unique entries and sort them, then drop the sorted range into an array.


Regards, TMS

[apo]

As TMS suggests.. try a Dictionary..

It puts all the values in your 6th column into a Dictionary and then assigns the Uniques values (Keys) to the allTeams array.

Please Login or Register  to view this content.

[randomdude]

Thank you, both of you, it works now.

[protonLeah]

You declared allTeams as a dynamic array:
Dim allTeams() As String

It has no no elements at this point so
Do Until allTeams(j) <> ""

throws an error.

Since j = 1 at this point,
ReDim Preserve allTeams(j)

will dimension the array with TWO elements, 0 & 1