Searching combinations

so basically, I wish to write a programme to do the below for me.

assume there are only 4 cells with values that follow the "colon"
a1.:2
a2.:3
a3.:4
a4.:5

so what i wanna do is to see which of these figures(i.e. 2,3,4,5) can add up to 7 (or any number that i assign)

the program should run all possibilities of combinations, add the figures up to see if there are any matches, so. for instance

2+3
2+4
2+5 = 7
3+4 = 7
3+5
4+5
2+3+4
2+3+5
2+4+5
3+4+5
2+3+4+5

The problem should be able to generate a list of possibilities like above, sum them, and tell me 2&5, 3&4; (cell a1+a4; or cell a2+a3 ) add to 7.

Finally, i want the programme gives the result by highlighting the cells (or any other better way) for result.


there are a couple of issues that i have encountered.
1. after I read all those figures in the selected cells to an array and identified the possible combinations. how can I manipulate the corresponding cells for a show of result? in other words, if array[1] is the match, how can i highlight the corresponding cell-a1 for instance.
2. i wish to write a multiple-layer-for-loop for this, but not sure how to make a for-loop that can goes first from a search for combinations of 2 figures, and then for 3 figures, 4,5,6 and so on until it finds matches. and does it mean if i want to make a search for a combinations of 10 figures, i will need to make a 10-layer for-loop?
3. if I run a search for a pool of 100 cells. for a maximum of combinations of 10 numbers, the mulitple-layer-for-loop could become a enormous structure which I believe eliminating duplicated combinations due to the for-loop is necessary(e.g 2+3 = 3+2) , inefficiency is quite a issue.




really SORRY for asking such a long question, i am absolutely a newbie on vba(though with quite plenty of experience on pascal, yea, learnt in high school), but somehow i think if i can able to write up a programme like that, that would bring a lot of ease to my job, saving me a lot of time. having thought about this for a long time, but can't even figure out how to build the basic algorithms.

ths alot in advance.

Hi, Try this:-
Place your numbers in individual cells in column "A".
Run code, SELECT Range of numbers in First "InputBox", INSERT Number to be found (i.e Total of some of the combinations) in second "InputBox" ,click Ok, columns "B" and "C should return the results, Also a Msgbox .

Please Login or Register  to view this content.

Regards Mick

oh .gosh!!!!!!!!!!!!!!!!!!!! you genius mick.

First of all, I must thank you for building the whole code. This is far beyond my expectation.

Then I must say I am tremendously impressed by your super-fast output. This is way too fast. Just a couple of hours after my post, you solved my whole problem which I had been struggled for weeks.

Therefore I believe I cannot ask you any further questions, as I am not qualified for doing so, until I digested your whole algorithms.

That's the reason for why I didn't reply to this thread until now. The reason is as simple as that your algorithm is so complex, though took you like minutes to finish, took me like 3 days to understand. But still, I still don't fully understand.

After my trial and error for few days. I've made a couple of changes to the code.(and left some issues)
1. I remove the generation of column B, as i realize that would cause a much longer time for processing.
2. I set a limit to the "Lps" variable of the for-loop. Having realized this is limiting the maximum number of total figures in each combination, I think allowing the users to input the max would allow the user to limit the calculation time, also can boost the maximum capacity for the "figures pool". Under the existing code, a pool of only 16 figures can easily crash the excel. Under the new system, that should provide sufficient help if the max is around 5 figures.
3. I realized that the program become quite slow-responding when the "figures pool" contain duplicated figures(intentionally having then sometimes). And from the code, I guess, that is because your algorithm used the "Fd" variable and the if-then structure to prohibit self-adding. (cell a +cell a). Given that the check is by the content, instead of by locations. I would think if I am able to create another array (NewRay) to contain the cell location of each combination in each corresponding "Ray". The check procedure can be done by checking if the cells locations overlaps instead of using the context for checking. Not sure if that works, kindly let me know if that is feasible.
4.To give a easier view of the possible result, I modify the output code to output ONLY the possible combinations in column C.
5.To generate a better output, I brainstormed that the prior mentioned new array (in #3. above) can also serve this purpose. I intended to create buttons(or any similar ways) nearby each generated possible combinations and allow the users to click on each button to highlight the correspinding cells in the "figure pool". As each "Ray" that contains the possible combinations corresponds to its unique "NewRay". That should provide helps to manipulate the corresponding cells.

Sorry again for this sickingly-long-email. I've been reading your code for days and still, I just can't understand how can you create such a complicated algorithms in such a short time. TOTALLY IMPRESSED by your logic. And this logic is way beyond what I can possible think of. Being a VBA newbie, that's definitely a very good lesson for me. And that brings me to another level of thinking.

Just drop me a line when you have time. Thanks alot in advance.

Mick,

I've finished the modificiation, finding my logic works.
Anyway, thanks a lot for your advice. And I will acknowledge you in my final product!!!!!!!!!!!

regards,
Chris

Hi, Chris
I'm please you've got thinks working and were able to modify the code to your needs.
Much as I would like to be able to say I wrote the code in 5 Minites. The truth is I had written the combination code some while ago and just modified it for what I thought you wanted
When I write code I am very bad at providing adequate notes for future reference, this tends to cause me problems undertand my own code,and even when adding the notes I find there not as helpfull as I thouight, when I wrote them.
I also know how difficult and painstaking it is to Trawl through other peoples code, so I think you need congratulating for sorting through this code and Getting the result you wanted.
Kind Regards Mick