Excel formula - urgent help needed!

Andrew Poulsom said:

Here is a macro that will put in column C the combinations of values in column A that equal the value in cell B1 (source unknown):

Code:

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
 
Sub AddsUp()
'   *** 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 = 10       ' 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
    Add1 1, 0, "", 0
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
            OneA = Cells(ThisRow, 1).Address(RowAbsolute:=False, ColumnAbsolute:=False)
            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) 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

Click to expand...