VBA how much permutation can be made using 3-digit with 0, 1 & 2

[motilulla]

Hello,</SPAN></SPAN>


I want to know is there any formula, which can count permutation can be made using 3-digit with 0, 1 & 2 </SPAN></SPAN>

And a VBA, which can make all possible permutation, using 3-digit with 0, 1 & 2 using 7 cells with in the row which row sum must be = 7</SPAN></SPAN>

Few examples...</SPAN></SPAN>

Book1
	E	F	G	H	I	J	K	L
1
2
3	n1	n2	n3	n4	n5	n6	n7	Sum
4	0	0	1	2	1	1	2	7
5	1	2	2	1	0	1	0	7
6	0	1	1	1	1	1	2	7
7	1	0	1	1	2	1	1	7
8	1	0	2	1	1	1	1	7
9	0	1	1	2	1	1	1	7
10	1	1	2	0	0	1	2	7
11	2	1	1	1	0	1	1	7
12	2	1	0	0	1	1	2	7
13	1	1	0	1	1	2	1	7
14	0	1	2	1	0	1	2	7
15	1	1	1	2	1	0	1	7
16	2	1	0	0	1	1	2	7
17	1	1	1	1	1	1	1	7
18	2	0	1	2	1	0	1	7
19	1	1	1	1	2	0	1	7
20	1	1	1	2	1	0	1	7
21	1	2	0	1	2	0	1	7
22	0	1	2	1	1	1	1	7
23	1	0	0	1	2	1	2	7
24	1	0	1	0	2	1	2	7
25	1	2	1	0	1	2	0	7
26	1	1	2	0	1	1	1	7
27	1	0	0	1	2	1	2	7
28	2	0	1	2	0	0	2	7
29	0	2	1	0	2	2	0	7
30	1	1	0	1	0	2	2	7
31	1	0	2	2	0	0	2	7
32	0	0	1	2	2	1	1	7
33	1	1	0	1	1	1	2	7
34	2	1	1	1	1	1	0	7
35	1	0	1	1	1	2	1	7
36	1	0	1	2	1	2	0	7
37	0	1	1	2	1	1	1	7
38	1	2	0	1	1	1	1	7
39	2	1	0	1	1	1	1	7
40	1	2	2	0	1	1	0	7
41	1	1	0	1	1	2	1	7
42	0	0	0	2	2	2	1	7
43	1	2	1	1	0	1	1	7
44	1	0	1	2	2	1	0	7
45	1	1	0	1	2	1	1	7
46	1	1	1	2	1	1	0	7
47	1	2	0	0	2	1	1	7
48	1	0	2	0	1	2	1	7
49	1	1	1	0	1	2	1	7
50	1	0	1	1	1	2	1	7
51	0	1	2	1	2	1	0	7
52	1	1	1	0	2	1	1	7
53	1	1	1	2	0	1	1	7
54	2	1	0	1	1	1	1	7
55	0	2	1	0	2	0	2	7
56	1	1	0	0	2	1	2	7
57	0	1	2	1	1	2	0	7
58	0	1	0	1	2	2	1	7
59	1	1	0	2	0	2	1	7
60	2	1	1	1	0	1	1	7
61	2	0	2	1	0	1	1	7
62	0	1	2	0	1	1	2	7
63	1	0	1	2	1	1	1	7
64	1	1	0	2	1	1	1	7
65	1	1	1	0	2	0	2	7
Sheet1

Thank you all</SPAN></SPAN>

Excel 2000</SPAN></SPAN>
Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>

 

[motilulla]

Bump

 

[offthelip]

this code will generate all possible permutations:

Sub test()
indi = 4
inarr = Range(Cells(4, 5), Cells(4, 11))
For i1 = 0 To 2
 For i2 = 0 To 2
  For i3 = 0 To 2
    For i4 = 0 To 2
     For i5 = 0 To 2
      For i6 = 0 To 2
       For i7 = 0 To 2
        tt = i1 + i2 + i3 + i4 + i5 + i6 + i7
         If tt = 7 Then
          inarr(1, 1) = i1
          inarr(1, 2) = i2
          inarr(1, 3) = i3
          inarr(1, 4) = i4
          inarr(1, 5) = i5
          inarr(1, 6) = i6
          inarr(1, 7) = i7
          Range(Cells(indi, 5), Cells(indi, 11)) = inarr
          indi = indi + 1
         End If
        Next i7
       Next i6
      Next i5
     Next i4
    Next i3
   Next i2
  Next i1
  


End Sub

 

[motilulla]

this code will generate all possible permutations:

Sub test()
indi = 4
inarr = Range(Cells(4, 5), Cells(4, 11))
For i1 = 0 To 2
 For i2 = 0 To 2
  For i3 = 0 To 2
    For i4 = 0 To 2
     For i5 = 0 To 2
      For i6 = 0 To 2
       For i7 = 0 To 2
        tt = i1 + i2 + i3 + i4 + i5 + i6 + i7
         If tt = 7 Then
          inarr(1, 1) = i1
          inarr(1, 2) = i2
          inarr(1, 3) = i3
          inarr(1, 4) = i4
          inarr(1, 5) = i5
          inarr(1, 6) = i6
          inarr(1, 7) = i7
          Range(Cells(indi, 5), Cells(indi, 11)) = inarr
          indi = indi + 1
         End If
        Next i7
       Next i6
      Next i5
     Next i4
    Next i3
   Next i2
  Next i1
  


End Sub

Wow!! offthelip, code generated 393 permutations this is what I were looking for worked perfect!! </SPAN></SPAN>

Thank you very much, I do appreciate your help and time and for giving a nice solution</SPAN></SPAN>

Good luck to you</SPAN></SPAN>

Regards,</SPAN></SPAN>
Moti </SPAN></SPAN>