# Combination of negative and positive numbers

#### jamiensurrey

##### New Member
Is there a way of editing this macro so it can take a list of numbers and see if it matches another (target) in any combination including negative numbers as some amounts might net off others to still match the target value?

i.e

any combination of these numbers

-1,791.81
23,371.48
11,685.72
2,508.53
-3,583.62
7,167.24
5,375.43
1,480.19
2,866.90
2,165.75
3,583.62
2,508.53
1,558.10

39,139.39

This is the code I am using

Option Explicit

'These private variables are used by the AddsUp macro
Private Target As Double ' The target total we're aiming for
Private EndRow As Integer ' The last row of the value list
Private Limit As Integer ' sum no more than this many cells
Private OutRow As Integer ' The row for the next output line

' *** Results in column C - change to suit ***
Columns(3).Clear
' *** Required answer - change reference to suit ***
Target = Range("B1").Value
' *** The last row in the list of values - change Range reference to suit ***
EndRow = Range("A1").End(xlDown).Row
' You can change the next two values
Limit = 20 ' Max number of cells to be summed
OutRow = 1 ' The row for the next output line
' You can change the first argument in the function call that follows.
' Doing so will change the starting row. Do not change the other
' three arguments
End Sub

Private Sub Add1(ByVal BegRow As Integer, ByVal SumSoFar As Double, _
ByVal OutSoFar As String, ByVal Num As Integer)
'This subroutine is called once by the AddsUp macro, to get the process
'started. It then calls itself recursively as many times as needed.
'
'BegRow - the first row that will be tested
'SumSoFar - the sum of all cells under consideration
'OutSoFar - the addresses of all cells under consideration
'Num - the number of cells under consideration
Dim ThisRow As Long
Dim OneA As String
If (BegRow <= EndRow) And (SumSoFar < Target) And (Num < Limit) Then
For ThisRow = BegRow To EndRow
If OutSoFar <> "" Then
OneA = " + " & OneA
End If
' If the current cell's value plus the sum so far equals
' the target, then we have found an answer. Display it
' in the current output row, and set OutRow to the next row
If (Round(SumSoFar + Cells(ThisRow, 1).Value, 2) < Target + 1) And (Round(SumSoFar + Cells(ThisRow, 1).Value, 2) > Target - 1) And (Num > 0) Then
Cells(OutRow, 3).Value = OutSoFar & OneA
OutRow = OutRow + 1
Else
' If the current cell's value plus the sum so far does not
' equal the target value, call this function again, starting
' in the row after ThisRow
Add1 ThisRow + 1, Round(SumSoFar + Cells(ThisRow, 1).Value, 2), _
OutSoFar & OneA, Num + 1
End If
Next ThisRow
End If
End Sub

Thanks!

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Hi jamiensurrey,

I suggest you have your code copy the two ranges you want to compare to new locations, then sort both of them using the Sort method, then you can compare them cell-for-cell since the two ranges will now contain identical values in their corresponding cells if they originally contained the same values. Then your code can clear these ranges when done with the comparison.

I hope this helps.

Damon

Replies
14
Views
603
Replies
14
Views
785
Replies
22
Views
2K
Replies
15
Views
431
Replies
1
Views
87

1,196,467
Messages
6,015,405
Members
441,892
Latest member
gpen

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back