Hi!
Is this what you want?
comb-perm2.xls |
---|
|
---|
| A | B | C | D | E | F |
---|
1 | list | DesiredSum | 10 | | possiblecombination | Sum |
---|
2 | 1 | Summary | | 4+6= | 10 |
---|
3 | 2 | numberofresults | 5 | | 2+3+5= | 10 |
---|
4 | 3 | GREATERTHAN10% | 0 | | 1+4+5= | 10 |
---|
5 | 4 | LESSTHAN10% | 0 | | 1+3+6= | 10 |
---|
6 | 5 | EQUALTO | 5 | | 1+2+3+4= | 10 |
---|
7 | 6 | TOTAL | 5 | | | |
---|
8 | | | | | | |
---|
9 | | | | | | |
---|
10 | | | | | | |
---|
11 | | | | | | |
---|
12 | | | | | | |
---|
13 | | | | | | |
---|
14 | | | | | | |
---|
|
---|
The code!<font face=Courier New><SPAN style="color:#00007F">Option</SPAN><SPAN style="color:#00007F">Explicit</SPAN><SPAN style="color:#00007F">Dim</SPAN> n, k, q<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Double</SPAN><SPAN style="color:#00007F">Dim</SPAN> Comblist()<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">String</SPAN><SPAN style="color:#00007F">Dim</SPAN> b()<SPAN style="color:#00007F">Dim</SPAN> Ans()<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Variant</SPAN><SPAN style="color:#00007F">Private</SPAN><SPAN style="color:#00007F">Sub</SPAN> Comb(j, m<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Integer</SPAN>)<SPAN style="color:#00007F">Dim</SPAN> tmp, X
<SPAN style="color:#00007F">If</SPAN> j > n<SPAN style="color:#00007F">Then</SPAN>
tmp = ""
<SPAN style="color:#00007F">For</SPAN> X = 1<SPAN style="color:#00007F">To</SPAN> n
tmp = tmp & b(X)
<SPAN style="color:#00007F">Next</SPAN> X
q = q + 1
<SPAN style="color:#00007F">ReDim</SPAN><SPAN style="color:#00007F">Preserve</SPAN> Comblist(UBound(Comblist) + 1)<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">String</SPAN>
Comblist(UBound(Comblist)) = tmp
<SPAN style="color:#00007F">Else</SPAN>
<SPAN style="color:#00007F">If</SPAN> k - m< n - j + 1<SPAN style="color:#00007F">Then</SPAN>
b(j) = 0
Comb j + 1, m
<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">If</SPAN> m< k<SPAN style="color:#00007F">Then</SPAN>
b(j) = 1
Comb j + 1, m + 1
<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN><SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Sub</SPAN><SPAN style="color:#00007F">Sub</SPAN> Sumxxx()<SPAN style="color:#00007F">Dim</SPAN> St, i, j, tmp, Tmp2, AnsCount, L, Res<SPAN style="color:#00007F">Dim</SPAN> listx()
Application.ScreenUpdating =<SPAN style="color:#00007F">False</SPAN>
Application.Calculation = xlCalculationManual
n = Range("a65536").End(xlUp).Row - 1
Res = Range("c1").Value
q = 0<SPAN style="color:#00007F">ReDim</SPAN> Comblist(0)<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">String</SPAN><SPAN style="color:#00007F">ReDim</SPAN> b
<SPAN style="color:#00007F">ReDim</SPAN> listx
<SPAN style="color:#00007F">ReDim</SPAN> Ans(0)<SPAN style="color:#00007F">For</SPAN> k = 1<SPAN style="color:#00007F">To</SPAN> n
Comb 1, 0<SPAN style="color:#00007F">Next</SPAN> k<SPAN style="color:#00007F">For</SPAN> i = 1<SPAN style="color:#00007F">To</SPAN> n
listx(i) = Range("a" & 1 + i).Value<SPAN style="color:#00007F">Next</SPAN> i
AnsCount = 0<SPAN style="color:#00007F">For</SPAN> i =<SPAN style="color:#00007F">LBound</SPAN>(Comblist)<SPAN style="color:#00007F">To</SPAN><SPAN style="color:#00007F">UBound</SPAN>(Comblist)
St = ""
tmp = 0
<SPAN style="color:#00007F">For</SPAN> j = 1<SPAN style="color:#00007F">To</SPAN> Len(Comblist(i))
tmp = tmp + Mid(Comblist(i), j, 1) * listx(j)
<SPAN style="color:#00007F">If</SPAN> Mid(Comblist(i), j, 1) = 1<SPAN style="color:#00007F">Then</SPAN>
St = St & "+" & listx(j)
<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">Next</SPAN> j
<SPAN style="color:#00007F">If</SPAN> tmp = Res<SPAN style="color:#00007F">Then</SPAN><SPAN style="color:#007F00">'Or (tmp >= 0.9 * Res And tmp<= 1.1 * Res) Then</SPAN>
<SPAN style="color:#00007F">ReDim</SPAN><SPAN style="color:#00007F">Preserve</SPAN> Ans(UBound(Ans) + 1)
AnsCount = AnsCount + 1
Ans(AnsCount) = St
<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN><SPAN style="color:#00007F">Next</SPAN> i
Range("e2:f65536").Value = ""<SPAN style="color:#00007F">For</SPAN> i =<SPAN style="color:#00007F">LBound</SPAN>(Ans) + 1<SPAN style="color:#00007F">To</SPAN><SPAN style="color:#00007F">UBound</SPAN>(Ans)
Range("e" & i + 1).Value = "'" & Mid(Ans(i), 2, Len(Ans(i))) & "="
Range("F" & i + 1).Value = "=" & Ans(i)<SPAN style="color:#00007F">Next</SPAN> i
Application.ScreenUpdating =<SPAN style="color:#00007F">True</SPAN>
Application.Calculation = xlCalculationAutomatic<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Sub</SPAN></FONT>
Note:
The above solution is not limited to 6 possible combination. this is a general solution for permuation problems. you may take up to 20 individual number in the list but would take sometimes to return.Above 20 may take you several minutes or give you an error of "stack overflow"
because of recursive nature of the function above.