Hi yauchildchew
Welcome to the board
If you don't mind using a more general solution I posted some time ago a solution for the 4 usual combinatorics problems:
- Comb=True, Repet=False - Combinations without repetition
- Comb=True, Repet=True - Combinations with repetition
- Comb=False, Repet=False - Permutations without repetition
- Comb=False, Repet=True - Permutations with repetition
Your example: Combinations with repetition of 3 elements taken 4 at a time.
- B1=4 , 4 elements taken at a time
- B2=True - Combinations
- B3=True - with repetition
- B5:B8, The set of elements is in B5, down
Just set the values in column B and run the code.
<table style="background: none repeat scroll 0% 0% rgb(255, 255, 255); border-collapse: collapse; border-width: 2px; border-color: rgb(204, 204, 204); font-family: Arial,Arial; font-size: 10pt;" border="1" cellpadding="1"><tbody><tr><th style="border-width: 1px; border-color: rgb(136, 136, 136); background: none repeat scroll 0% 0% rgb(153, 204, 255);"> </th><th style="border-width: 1px; border-color: rgb(136, 136, 136); background: none repeat scroll 0% 0% rgb(153, 204, 255); text-align: center;">A</th><th style="border-width: 1px; border-color: rgb(136, 136, 136); background: none repeat scroll 0% 0% rgb(153, 204, 255); text-align: center;" width="30">B</th><th style="border-width: 1px; border-color: rgb(136, 136, 136); background: none repeat scroll 0% 0% rgb(153, 204, 255); text-align: center;" width="30">C</th><th style="border-width: 1px; border-color: rgb(136, 136, 136); background: none repeat scroll 0% 0% rgb(153, 204, 255); text-align: center;" width="30">D</th><th style="border-width: 1px; border-color: rgb(136, 136, 136); background: none repeat scroll 0% 0% rgb(153, 204, 255); text-align: center;" width="30">E</th><th style="border-width: 1px; border-color: rgb(136, 136, 136); background: none repeat scroll 0% 0% rgb(153, 204, 255); text-align: center;" width="30">F</th><th style="border-width: 1px; border-color: rgb(136, 136, 136); background: none repeat scroll 0% 0% rgb(153, 204, 255); text-align: center;" width="30">G</th><th style="border-width: 1px; border-color: rgb(136, 136, 136); background: none repeat scroll 0% 0% rgb(153, 204, 255); text-align: center;" width="30">H</th></tr><tr><td style="border-width: 1px; border-color: rgb(0, 0, 0); padding: 0.4em 0.5em 0.25em; background: none repeat scroll 0% 0% rgb(153, 204, 255); text-align: center;">
1</td><td style="padding: 0.4em 0.5em 0.25em; text-align: left; border-width: 1px; border-color: rgb(136, 136, 136);">p</td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);">4</td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">A</td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">A</td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">A</td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">A</td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td></tr><tr><td style="border-width: 1px; border-color: rgb(0, 0, 0); padding: 0.4em 0.5em 0.25em; background: none repeat scroll 0% 0% rgb(153, 204, 255); text-align: center;">
2</td><td style="padding: 0.4em 0.5em 0.25em; text-align: left; border-width: 1px; border-color: rgb(136, 136, 136);">Comb</td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">TRUE</td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">A</td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">A</td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">A</td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">B</td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td></tr><tr><td style="border-width: 1px; border-color: rgb(0, 0, 0); padding: 0.4em 0.5em 0.25em; background: none repeat scroll 0% 0% rgb(153, 204, 255); text-align: center;">
3</td><td style="padding: 0.4em 0.5em 0.25em; text-align: left; border-width: 1px; border-color: rgb(136, 136, 136);">Repet</td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">TRUE</td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">A</td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">A</td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">A</td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">C</td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td></tr><tr><td style="border-width: 1px; border-color: rgb(0, 0, 0); padding: 0.4em 0.5em 0.25em; background: none repeat scroll 0% 0% rgb(153, 204, 255); text-align: center;">
4</td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">A</td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">A</td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">B</td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">B</td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td></tr><tr><td style="border-width: 1px; border-color: rgb(0, 0, 0); padding: 0.4em 0.5em 0.25em; background: none repeat scroll 0% 0% rgb(153, 204, 255); text-align: center;">
5</td><td style="padding: 0.4em 0.5em 0.25em; text-align: left; border-width: 1px; border-color: rgb(136, 136, 136);">Set</td><td style="padding: 0.4em 0.5em 0.25em; text-align: left; border-width: 1px; border-color: rgb(136, 136, 136);">A</td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">A</td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">A</td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">B</td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">C</td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td></tr><tr><td style="border-width: 1px; border-color: rgb(0, 0, 0); padding: 0.4em 0.5em 0.25em; background: none repeat scroll 0% 0% rgb(153, 204, 255); text-align: center;">
6</td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td><td style="padding: 0.4em 0.5em 0.25em; text-align: left; border-width: 1px; border-color: rgb(136, 136, 136);">B</td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">A</td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">A</td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">C</td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">C</td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td></tr><tr><td style="border-width: 1px; border-color: rgb(0, 0, 0); padding: 0.4em 0.5em 0.25em; background: none repeat scroll 0% 0% rgb(153, 204, 255); text-align: center;">
7</td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td><td style="padding: 0.4em 0.5em 0.25em; text-align: left; border-width: 1px; border-color: rgb(136, 136, 136);">C</td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">A</td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">B</td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">B</td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">B</td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td></tr><tr><td style="border-width: 1px; border-color: rgb(0, 0, 0); padding: 0.4em 0.5em 0.25em; background: none repeat scroll 0% 0% rgb(153, 204, 255); text-align: center;">
8</td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">A</td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">B</td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">B</td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">C</td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td></tr><tr><td style="border-width: 1px; border-color: rgb(0, 0, 0); padding: 0.4em 0.5em 0.25em; background: none repeat scroll 0% 0% rgb(153, 204, 255); text-align: center;">
9</td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">A</td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">B</td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">C</td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">C</td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td></tr><tr><td style="border-width: 1px; border-color: rgb(0, 0, 0); padding: 0.4em 0.5em 0.25em; background: none repeat scroll 0% 0% rgb(153, 204, 255); text-align: center;">
10</td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">A</td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">C</td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">C</td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">C</td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td></tr><tr><td style="border-width: 1px; border-color: rgb(0, 0, 0); padding: 0.4em 0.5em 0.25em; background: none repeat scroll 0% 0% rgb(153, 204, 255); text-align: center;">
11</td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">B</td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">B</td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">B</td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">B</td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td></tr><tr><td style="border-width: 1px; border-color: rgb(0, 0, 0); padding: 0.4em 0.5em 0.25em; background: none repeat scroll 0% 0% rgb(153, 204, 255); text-align: center;">
12</td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">B</td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">B</td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">B</td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">C</td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td></tr><tr><td style="border-width: 1px; border-color: rgb(0, 0, 0); padding: 0.4em 0.5em 0.25em; background: none repeat scroll 0% 0% rgb(153, 204, 255); text-align: center;">
13</td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">B</td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">B</td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">C</td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">C</td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td></tr><tr><td style="border-width: 1px; border-color: rgb(0, 0, 0); padding: 0.4em 0.5em 0.25em; background: none repeat scroll 0% 0% rgb(153, 204, 255); text-align: center;">
14</td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">B</td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">C</td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">C</td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">C</td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td></tr><tr><td style="border-width: 1px; border-color: rgb(0, 0, 0); padding: 0.4em 0.5em 0.25em; background: none repeat scroll 0% 0% rgb(153, 204, 255); text-align: center;">
15</td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">C</td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">C</td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">C</td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);">C</td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td></tr><tr><td style="border-width: 1px; border-color: rgb(0, 0, 0); padding: 0.4em 0.5em 0.25em; background: none repeat scroll 0% 0% rgb(153, 204, 255); text-align: center;">
16</td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);"> </td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);"> </td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);"> </td><td style="padding: 0.4em 0.5em 0.25em; text-align: center; border-width: 1px; border-color: rgb(136, 136, 136);"> </td><td style="padding: 0.4em 0.5em 0.25em; text-align: right; border-width: 1px; border-color: rgb(136, 136, 136);"> </td></tr><tr><td colspan="9" style="background: none repeat scroll 0% 0% rgb(153, 204, 255); padding-left: 1em;"> [Book1]Sheet1</td></tr></tbody></table>
Code:
Option Explicit
' PGC Set 2007
' Calculates and writes the Combinations / Permutations with/without repetition
' vElements - Array with the set elements (1 to n)
' p - number of elements in 1 combination/permutation
' bComb - True: Combinations, False: Permutations
' bRepet - True: with repetition, False: without repetition
' vResult - Array to hold 1 permutation/combination (1 to p)
' lRow - row number. the next combination/permutation is written in lRow+1
' vResultAll - Array to hold all the permutations/combinations (1 to Total, 1 to p)
' iElement - order of the element to process in case of combination
' iIndex - position of the next element in the combination/permutation
' Sub CombPerm() deals with the input / output
' Sub CombPermNP() generates the combinations / permutations
Sub CombPerm()
Dim rRng As Range, p As Integer
Dim vElements As Variant, vResult As Variant, vResultAll As Variant, lTotal As Long
Dim lRow As Long, bComb As Boolean, bRepet As Boolean
' Get the inputs and clear the result range (you may adjust for other locations)
Set rRng = Range("B5", Range("B5").End(xlDown)) ' The set of numbers
p = Range("B1").Value ' How many are picked
bComb = Range("B2")
bRepet = Range("B3")
Columns("D").Resize(, p + 1).Clear
' Error
If (Not bRepet) And (rRng.Count < p) Then
MsgBox "With no repetition the number of elements of the set must be bigger or equal to p"
Exit Sub
End If
' Set up the arrays for the set elements and the result
vElements = Application.Index(Application.Transpose(rRng), 1, 0)
With Application.WorksheetFunction
If bComb = True Then
lTotal = .Combin(rRng.Count + IIf(bRepet, p - 1, 0), p)
Else
If bRepet = False Then lTotal = .Permut(rRng.Count, p) Else lTotal = rRng.Count ^ p
End If
End With
ReDim vResult(1 To p)
ReDim vResultAll(1 To lTotal, 1 To p)
' Calculate the Combinations / Permutations
Call CombPermNP(vElements, p, bComb, bRepet, vResult, lRow, vResultAll, 1, 1)
Range("D1").Resize(lTotal, p).Value = vResultAll 'you may adjust for other location
End Sub
Sub CombPermNP(ByVal vElements As Variant, ByVal p As Integer, ByVal bComb As Boolean, ByVal bRepet As Boolean, _
ByVal vResult As Variant, ByRef lRow As Long, ByRef vResultAll As Variant, ByVal iElement As Integer, ByVal iIndex As Integer)
Dim i As Integer, j As Integer, bSkip As Boolean
For i = IIf(bComb, iElement, 1) To UBound(vElements)
bSkip = False
' in case of permutation without repetition makes sure the element is not yet used
If (Not bComb) And Not bRepet Then
For j = 1 To p
If vElements(i) = vResult(j) And Not IsEmpty(vResult(j)) Then
bSkip = True
Exit For
End If
Next
End If
If Not bSkip Then
vResult(iIndex) = vElements(i)
If iIndex = p Then
lRow = lRow + 1
For j = 1 To p
vResultAll(lRow, j) = vResult(j)
Next j
Else
Call CombPermNP(vElements, p, bComb, bRepet, vResult, lRow, vResultAll, i + IIf(bComb And bRepet, 0, 1), iIndex + 1)
End If
End If
Next i
End Sub
Remark: As I said this is a solution for the 4 usual cases of combinatorics problems. You may prefer a simpler solution for your specific case of Combinations with Repetition.