Macro to create combinations of a string

[dogankurban]

Hello;

I am trying to create a macro to do below, but got stuck at the beginning, can anyone point me at the right direction please?

I have a string like this: 111111111

What I am trying to do is to get the excel to write down all the combinations of that string on different columns.

The reason I need to use a seperator like "_" is because _111 and 111_ would mean different things in my case.

i.e COLUMN1: Combination with 8 would be below; COMBIN(9;8) = 9 new strings

_11111111
1_1111111
11_111111 and so on...

COLUMN2: Combination with 7 would be below; COMBIN(9;7) = 36 new strings
__1111111
1__111111
1__111111
1_1_11111 etc...

I cannot figure out how that loop should work

[millz]

Something like this?

Please Login or Register  to view this content.

[dogankurban]

Millz, this is very good thanks for your help!
Apologies I could not make myself clear the first time but, the only additional thing is, I need the macro to get the combinations from user as well. For example;

String: 12345 Combine high: 4 Combine Low: 2

In this case, Column:A would have COMBIN(5;4)=5 rows, Column:B COMBIN(5:3)=10 rows, Column:C COMBIN(5:2)=10 rows ; Column:D COMBIN(5;1) = 5 rows.

A B C D
1 _12345 1__45 1___5 1____
2 1_2345 12__5 12___ _2___

The macro currently works perfectly for getting the string from me, but is it possible for it to also ask for the combination numbers also? Although I have been working on your direction for the last couple of hours, could not figure it out.

[millz]

This took a lot of brain juice out of me:

Try with string: 12345
Number of combinations: 4

Please Login or Register  to view this content.