vba code for Combinations

I should be able to get my head round this but have a complete mental block. Im trying to write an algorithim and then a vba user defined function to solve the following problem:

I have N (max 32) display panels and each panel can have any one of R (say 4) layout configurations. I want to write a code which will output an array of N columns with each row populated with the Layout number (I've used letters for clarity only):

For example a 3 panel situation would output

AAA
AAB
AAC
AAD
ABB
ABC
ABD
ACC
ACD
ADD
BBB
BBC
BBD
BCC
BCD
BDD
CCC
CCD
CDD
DDD

I know the maths to work out the number of non repetetive combinations - its the logic of generating the aray table which has defeated me.

Any help would be gratefully received.

Thanks in anticipation

Thanks Guru for the very quick response. I seem to get a Name error when entering this formula in A2??

I suspect the formula based approach may work well for small values of N but I do need a vba solution in order to develop my project further.

Thanks again

shg, Many thanks for the revision and vba code. I'll work on this but it's certainly given me the kick start I needed. Regards

mehmetcik, I was sure I had replied to your post yesterday but clearly not! Thanks very much indeed for this code. I have run it but I get a Run-Time 1004 error Application defined or Object defined error. So I changed the 32 columns to 4 and ran it again. This time the output ran to 625 lines and a sample of the first few is as below:

On running Debug there appears to be a problem at the line "Range(Cells(Row, 1), Cells(Row, 32)).Value = MyArray2". Should there be a dimension variant for MyArray2?. Also in the line above you have "Display = Display+1 but I cannot see where that variable appears elsewhere in the routine.

I would be very grateful if you could please have a look again and point me in the right direction. Your help is much appreciated

0 0 0 0
0 0 0 A
0 0 0 B
0 0 0 C
0 0 0 D
0 0 A 0
0 0 A A
0 0 A B
0 0 A C
0 0 A D
0 0 B 0
0 0 B A
0 0 B B
0 0 B C
0 0 B D
0 0 C 0
0 0 C A
0 0 C B
0 0 C C
0 0 C D
0 0 D 0
0 0 D A
0 0 D B
0 0 D C
0 0 D D

Thanks for your continuing work. I hope we are not at cross purposes here. I was anticipating the output for a 4 col ABCD arrangement to be:
AAAA
AAAB
AAAC
AAAD
AABB
AABC
AABD
AACC
AACD
AADD
ABBB
etc.
This is for all the combinations without repetition. The maths to work out what the number of non repeating combinations is (N+R-1)!/R!(N-1)! So for R=32 and N=4 there are only 6,545 combinations and for R=4 and N=4 only 35

R=32 is what I will eventually need and I was going to adapt your code into a user defined function into which I could pass variable values for R & N. Does the above make it any easier?? Cheers DJH

The logic I was trying to work out an algorithm for was that (taking the easier 4 column example), we start with AAAA and then for each subsequent row cols 1 to 3 stay on A and col 4 increments +1. When col 4 =4 row 3 of col 5 would increment by 1 (i.e. become B) and col 4 of row 5 would start at B (i.e. the same as col 3 ) etc. Sorry if that's rather long hand bit it's where I got in a mess trying to implement! DJH

My thanks once again. This certainly works and I see you are expecting the required output to be all permutations of ABCD. However, order is not important so if you take line 2 at AAAB then line 5 is AABA which is the same combination. Hence my basic algorithm that when Col(x) contains D [or 4 if numeric] then on the next row Col (x-1) = previous row Col(x)-1 and this next row Col(x) starts at Col(x-1) on the same row.

So If say Row = 5 then
Col(1) =1
Col(2) =1
Col(3) = Col(3)+1 from Row 4
Col(4) now = Col(3)

and Col(4) continues to be incremented until it is 4 again and so on

Sorry again to be taking so much of your time but it's really appreciated. DJH

Thanks shg, it certainly gives the output but the options and hence the code is perhaps a little too complex for my needs. In addition I do need each element of each combination to be in its own column. I guess I could intercept the code to do that. I'm sure mehmetcik is so close with his last version.

Once again my thanks. In the meantime I have been trying to solve the algorithm for mysef - the only way to learn!
Everything has worked as per the code below. However, there are a few things not going quite according to plan which it would be helpful if you could plesase look into. Firstly when the macro has run I seem to have "lost" all the menu ribbons. Sometimes the sheet has lost is auto calcualtion.
I'm tring to pass the numeric value from my numeric TempArray() as a character to my RealArray(x,y) but I'm getting a subscript error
Finally I thought I would be able to grab the values in cells from the worksheet and assign them to my vba variables. This works OK for anything other than a cell which contains a calculated value.
I have inserted the code into a new module - is that the correct way?
If you try and run the code you'll se what I mean. You'll need to put values in cells A2, B3 and B4 Suggest 56, 5 and 4 respectively.
I'm sure that once these few isues are ironed out I'll be able to continue unaided with my project! Thanks again. DJH

Please Login or Register  to view this content.