Permutation and combinations VB code to be modified

[asad]

Hello Gurus,

I found this thread http://www.mrexcel.com/forum/showthread.php?t=172630 with a code in post #3 that almost gave me the answer for what I was lookking for. I don't know how to modify this code, so here I am asking your help.
This code gives all the possible combinations from a set of numbers. What want is for this to show only those combinations that totals to a desired result e.g the set of numbers is 1,2,3,4,5,6,7,8,9 and I want all combinations of three numbers that totals to 6. The answer is only 1,2 and 3. This is the ONLY combination with 3 numbers and every number is used opnly once.
A number in the set cannot be used twice which already is a part of the code. It selects one number only once but it doesn't give the total of all combinations.
Is there a way to get it to do what I want or is it not possible?

 

[bobsan42]

hi, i looked thru th e code and i came up with this:
replace this lines - SavePermutation SetMembers()
with this:
If Application.WorksheetFunction.Sum(SetMembers) = 6 Then SavePermutation SetMembers()
change the 6 to a cell value or something (for example cell B2) :
If Application.WorksheetFunction.Sum(SetMembers) = Range("B2").Value Then SavePermutation SetMembers()

 

[asad]

It is not working for me

May be because when the other part of code works, it puts the combinations like this

1,2,3

and I think the sum function that you have included in new code does not sum them up to 6. So I end up getting no combinations at all.

How can I get the numbers to show up individually in different cells so that your code can sum them up?

 

[asad]

 

[tusharm]

See
Find a set of amounts that match a target value
http://www.tushar-mehta.com/excel/templates/match_values/index.html

Hello Gurus,

I found this thread http://www.mrexcel.com/forum/showthread.php?t=172630 with a code in post #3 that almost gave me the answer for what I was lookking for. I don't know how to modify this code, so here I am asking your help.
This code gives all the possible combinations from a set of numbers. What want is for this to show only those combinations that totals to a desired result e.g the set of numbers is 1,2,3,4,5,6,7,8,9 and I want all combinations of three numbers that totals to 6. The answer is only 1,2 and 3. This is the ONLY combination with 3 numbers and every number is used opnly once.
A number in the set cannot be used twice which already is a part of the code. It selects one number only once but it doesn't give the total of all combinations.
Is there a way to get it to do what I want or is it not possible?

 

[asad]

Thanks a lot Tushar. It is a perfect solution for what I was doing.

 

[tusharm]

You are welcome.

Thanks a lot Tushar. It is a perfect solution for what I was doing.