Loop through a range of cells.... 3

[luceze]

I don't know if this is even possible. I would like to add a number in a range to every other number in the range and see if the sum is equal to a predetermined number.
For example:
Range a1:a5
a1=1
a2=2
a3=3
a4=4
a5=5
I would like to add a1 to a2 then a1 to a3 then a1 to a4 then a1 to a5 then a2 to a3 then a2 to a4, etc.
Then check if their sum is equal to 5. In this case a2+a3=5
and a1+a4=5.
This is a obviously very simplified example. As you can see with a large enough range of data the number of calclations turns enormous.
My limited code writing ability has not been able to come up with a solution. If someone has any ideas I would love to hear them.

Thanks

 

[vbMax]

Try this VBA solution...
I made the following assumptions:
* The numbers to sum are in column A of Sheet1
* The numbers to sum are contiguous (no empty cells)

This module will write the results in column B. I
tested it with several hundred entries and it seems
to work fine. Let me know what you think.




Sub addTwo()

goal = InputBox("Enter sum you are seeking...&quot
goal = Val(goal)
If goal = 0 Then Exit Sub

lastRow = Application.CountA(Range("'Sheet1'!A:A&quot)
For counterA = 1 To lastRow
For counterB = counterA + 1 To lastRow
If Range("A" + LTrim(Str(counterA))).Value + _
Range("A" + LTrim(Str(counterB))).Value = goal Then
counterC = counterC + 1
Range("B" + LTrim(Str(counterC))).Value = Range("A" + _
LTrim(Str(counterA))).Value & _
" and " & Range("A" + LTrim(Str(counterB))).Value
End If
Next counterB
Next counterA

End Sub

 

[luceze]

That worked great.
Thanks for your help you have saved me hours of work.