Excel VBA combination Generator

[simonbernick]

Hello, I am trying to build a macro to solve an accounting issue. Basically I have a column of numbers, some positive some negative that need to sum to 0. However, some of the signs maybe wrong on these numbers so that they do not sum to 0. I want to build a program that will create all the possible combinations of the positive and negative versions of each number so that I can find which ones sum to 0. For example: I put in 1,2,3 into cells of column A. It gives out in column B

1+2+3
1+2-3
1-2-3
-1-2-3
1-2+3
-1+2-3
-1+2+3
-1-2+3

I don't care if the macro gives out a ton of useless results, as long as the possible sum to 0 results are all there. In the case above for example only 2 cases sum to 0.

[Eri523]

For this purpose, think of the numbers' signs as boolean values in the sense "negate" and "don't negate". Then, essentially, you'll end up with plain binary counting.

Here's a thread with a relatively deep discussion of this topic: http://forums.codeguru.com/showthrea...tion-with-sets. It contains sample code, and though it's written in C++, it's simple, so you may well understand it good enough for using it as a mere example. From the two approaches laid out in post #4, I'd prefer the second one with the nested loops for VBA. The first one is niftier but somewhat harder to understand when reading the code, and I'd expect its performace gain would be relatively low in VBA, compared to C or C++.

This assessment is based on the assumption that you've got a relatively small and constant volume of numbers to process, like in your example. The larger the volume actually is, the more attractive the bit mask approach (first one) becomes, and for a variable volume it's way easier to use anyway, even for small volumes. Also, the bit mask approach will become somewhat impractical for volumes of more than 63 numbers, but doing the calculation in this brute force way would probably take ages for so many numbers anyway, so a completely different approach will be worth consideration.