# 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