Indefinite number of loops

[catalinand]

Dear all,

Pls help me with the following problem:

I have a variable number of vertical ranges (vertical string arrays): from column A to, lets say, D.
The number of arrays can vary. For example could be from column A to H instead of D.
Each array has a variable number of string elements (up to 1000)
Here is an example below.

A B C D

1 str1 str4 str10 str12

2 str2 str5 str11 str13

3 str3 str6 str14

4 str7

5 str8

6 str9


I do know how to determine how many vertical ranges / arrays I have and how many elements has each range / array at runtime.

In the end I need to concatenate the elements as follows

str1 & str4 & str10 & str12
str1 & str4 & str10 & str13
str1 & str4 & str10 & str14
str1 & str4 & str11 & str12
str1 & str4 & str11 & str13
str1 & str4 & str11 & str14
str1 & str5 & str10 & str12
str1 & str5 & str10 & str13
.
.
.
str3 & str9 & str11 & str14


This could be achieved with a DEFINITE or a FIX number of FOR or DO loops, something like below:

FOR i=1 to num_elements(columnA)
FOR j=1 to num_elements(columnB)
FOR k=1 to num_elements(columnC)
FOR l=1 to num_elements(columnD)
....
next
next
next
next

My problem arises because I do not have a fix number of arrays (columns). I only know how many arrays I have at runtime... And at that time I cannot insert another FOR loop if needed.

One solution I think would be to write the code with maximum FOR or DO loops possible supported by Excel, but it does't seem to be very professional...

I am sorry for my very long explanation but I tried to make myself understood.

So, any useful ideas would be very appreciated.

Thanks and regards,
Catalin

[catalinand]

Once again the example in HTML..

[AB33]

I do not know what are you trying to do, but if you wish to use arrays, load the range in to array first and loop through the array and then write back the result in to worksheet again. It is much faster than looping through ranges. For e.g you could use the following.
x = sheets(1).Range("A1:X" & Cells(Rows.Count, 2).End(xlUp).Row).Value.
You have now loaded your range in to an array.
X has now lower and upper bounds. It does not matter which row or column you are, you go loop through the array X

[catalinand]

Thanks for your time and answer.
I know what you mean and I thought about it myself, but this does not solve my problem.

What I am trying to achieve is to convert those arrays having different number of elements as shown in my second message into a single array with concatenated elements as in my first message.

As I said, I could have done it using large "finite" number of FOR or DO loops, but I want to know if there is a way of doing it if we have an "unknown" number of loops. The number of loops will be available only at runtime, after seeing how many arrays I have. I have to repeat that the number of array is also "unknown" until the runtime.

Does everything I said make sense ?

Best regards,
Catalin

[AB33]

Have you thought of using Redim and Preserve with your arrays?

[event21]

Hi -

Can you post your data?

Regards,
Event

[Norie]

Do Loops terminate based on criteria not after a certain no of iterations.

Are there any criteria you could use to determine when to stop the loop?

For example, do you want to loop until the first empty row.

As for the concatenation, you don't need a loop for that.

Instead you can use VBA's Join function.