Getting Excel to Display Cells Which can add up to a given number

Posted by Tim Johnson on November 25, 2001 8:38 PM

[: Is there a way in excel or any other program to search a range of cells and show possible combinations of those cells which can add up to a given number. A simple example being if say Cells A:1, A:2 and A:3 are 1,2,and 3 respectively. I am looking to see if any combination of those cells = 5. This is a very simplified version, I actually have a long spreadsheet and I am trying to add up numbers to arrive at a given total and it takes forever. If anybody knows anything please let me know.]

I am looking for the same answer that you guys are pondering. S-O-S has tried to help me so far and his macro works well with whole numbers. I am having difficulty fixing it to work with 2 decimal places ($15.23). Can one of you guys help us fix it? Below is the macro code...

Sub test_loop()
Dim a(100) As Integer
Dim Targt As Double
Targt = InputBox("Enter Target")
Sol1 = ""
n = 0
For Each cell In Selection
n = n + 1
a(n) = cell.Value
If (a(n) = Targt) Then Sol1 = Sol1 & a(n) & Chr(10)
Next

MsgBox Sol1, vbOKOnly, "Solutions with 1 Variable"

Sol2 = ""
For r = 1 To n
For s = 1 To n
If r = s Then GoTo nxt2
If a(r) + a(s) = Targt Then Sol2 = Sol2 & a(r) & "+" & a(s) & Chr(10)
nxt2:
Next
Next

MsgBox Sol2, vbOKOnly, "Solutions with 2 Variables"

Sol3 = ""
For r = 1 To n
For s = 1 To n
For t = 1 To n
If r = s Then GoTo nxt3
If r = t Then GoTo nxt3
If s = t Then GoTo nxt3
If a(r) + a(s) + a(t) = Targt Then Sol3 = Sol3 & a(r) & "+" & a(s) & "+" & a(t) & Chr(10)
nxt3:
Next
Next
Next

MsgBox Sol3, vbOKOnly, "Solutions with 3 Variables"

End Sub

Posted by Bariloche on November 25, 2001 9:19 PM

Tim,

The problem is in the dimension of the array "a". It's currently dimensioned as "integer" so that is all that's being stored. Change the dimension to "Double" and the problem with it recognizing numbers with decimal places goes away.

enjoy

Posted by Tim Johnson on November 25, 2001 9:28 PM

Thanks So Much!!! But I am not sure what you mean. Can you fix the macro that s-o-s gave me so I can cut and paste it? I am still new to macros and I don't know what you mean. THANKS AGAIN IN ADVANCE!!!!!!!!!!

Posted by Bariloche on November 25, 2001 10:04 PM

Tim,

The part of the code near the beginning that says:

Dim a(100) As Integer

Should be changed to read:

Dim a(100) As Double

good luck

ps: As it currently is written, you can only have a list of numbers that contains 99 values. If your list is bigger, then change the size of the array, like this:

Dim a(1000) As Double

There are more elegant ways of redimensioning arrays, but this will get the job done. :-)

Posted by Tim Johnson on November 25, 2001 10:14 PM

THANK YOU!!! YOU ARE THE KING!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

THANKS SO MUCH!!!! THAT SOLVED MY PROBLEM!!!!!! AND THANKS FOR GETTING BACK TO ME SO QUICKLY!!!!!!!!!!!!!!!!!!!! YOU'RE THE BEST!

Posted by Tim Johnson on November 25, 2001 10:35 PM

It works great, one more question though...

Thanks again for your help. It works great!!! The code that s-o-s gave me works for up to 3 variables and I rigged it to work for 4 variables. Is there a better and faster way to make it work for more variables? Even unlimited variables? But I wouldn't want to go through a million message boxes each time though.
THANKS AGAIN!!!!!!!!!!!!!!

Sub SumCertain()
Dim a(100) As Double
Dim Targt As Double
Targt = InputBox("Enter Target")
Sol1 = ""
n = 0
For Each cell In Selection
n = n + 1
a(n) = cell.Value
If Abs(a(n) - Targt) &LT; 0.01 Then Sol1 = Sol1 & a(n) & Chr(10)
Next

MsgBox Sol1, vbOKOnly, "Solutions with 1 Variable"

Sol2 = ""
For r = 1 To n
For s = 1 To n
If r = s Then GoTo nxt2
If a(r) + a(s) = Targt Then Sol2 = Sol2 & a(r) & "+" & a(s) & Chr(10)
nxt2:
Next
Next

MsgBox Sol2, vbOKOnly, "Solutions with 2 Variables"

MsgBox Sol3, vbOKOnly, "Solutions with 3 Variables"

Sol4 = ""
For r = 1 To n
For s = 1 To n
For t = 1 To n
For u = 1 To n
If r = s Then GoTo nxt4
If r = t Then GoTo nxt4
If r = u Then GoTo nxt4
If s = t Then GoTo nxt4
If s = u Then GoTo nxt4
If t = u Then GoTo nxt4
If a(r) + a(s) + a(t) + a(u) = Targt Then Sol4 = Sol4 & a(r) & "+" & a(s) & "+" & a(t) & "+" & a(u) & Chr(10)
nxt4:
Next
Next
Next
Next

MsgBox Sol4, vbOKOnly, "Solutions with 4 Variables"

End Sub