# Thread: Checking for Sum of Values (Excel 2000)

1. ## Checking for Sum of Values (Excel 2000)

I have a list of 1000 records. I would like to run a formula to tell me which 2 values will add up to give me 758. Any formula that will do the trick?

Thanks

2. ## Re: Checking for Sum of Values (Excel 2000)

VBA code is probably more efficient here, but perhaps one of the array formula gurus will come up with something.
Problems with a formula:
- There are 1000*999/2 = 499500 pairs of values to be checked.
- There can be more than one solution.

3. ## Re: Checking for Sum of Values (Excel 2000)

Here is a VBA solution that will list all "matching" addresses to the right of the list:

<pre>Sub FindCombinations()
Dim oCell As Range
Dim oFoundcell As Range
Dim oFirstFound As Range
Dim lVal As Long
Dim lSum As Long
Dim iFoundCount As Integer
Dim lCalcmode As Long
lSum = Application.InputBox("Please enter the sum", "Find combinations", , , , , , 1)
Application.ScreenUpdating = False
lCalcmode = Application.Calculation
Application.Calculation = xlCalculationManual
For Each oCell In Selection
Set oFirstFound = Nothing
Set oFoundcell = Nothing
lVal = lSum - oCell.Value
iFoundCount = 0
On Error Resume Next
Set oFirstFound = Selection.Find(What:=lVal, LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If Not oFirstFound Is Nothing Then
Set oFoundcell = oFirstFound
Do
On Error GoTo 0
If Not (oFoundcell Is Nothing) Then
iFoundCount = iFoundCount + 1
oCell.Offset(, iFoundCount).Value = oFoundcell.Address
End If
Set oFoundcell = Selection.FindNext(after:=oFoundcell)
Loop Until oFoundcell.Address = oFirstFound.Address Or (oFoundcell Is Nothing)
End If
Next
Application.ScreenUpdating = True
Application.Calculation = lCalcmode
End Sub</pre>

4. ## Re: Checking for Sum of Values (Excel 2000)

Thanks for VBA Code. Very clever. I would have gone the long way around at match each cell with the other 999 values.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•