Results 1 to 3 of 3

Thread: Combination of list of values to a target range

  1. #1

    Thread Starter
    New Member
    Join Date
    Sep 2017
    Posts
    3

    Combination of list of values to a target range

    Hello,

    need help to improve the VB code ( Excel macro ) to find out combination of values which will sum up to the given target range..

    For instance, the given list of values are 1,3,2,4,2,2.9,2.5,1.5,3.8 target value 4.8 to 5.2..then the macro need to give the combinations of values whose sum will be 4.5 to 5.2..

    Also please note that the combination may be 2 values / 3 values / 4 values/ 5values..

    I have a code from internet which gives the combination for single target value and not for a range..

    The code is below,

    Sub startSearch()
    'The selection should be a single contiguous range in a single column. _
    The first cell indicates the number of solutions wanted. Specify zero for all. _
    The 2nd cell is the target value. _
    The rest of the cells are the values available for matching. _
    The output is in the column adjacent to the one containing the input data.

    If Not TypeOf Selection Is Range Then GoTo ErrXIT
    If Selection.Areas.Count > 1 Or Selection.Columns.Count > 1 Then GoTo ErrXIT
    If Selection.Rows.Count < 3 Then GoTo ErrXIT

    Dim TargetVal, Rslt(), InArr(), StartTime As Date, MaxSoln As Integer, _
    HaveRandomNegatives As Boolean
    StartTime = Now()
    MaxSoln = Selection.Cells(1).Value
    TargetVal = Selection.Cells(2).Value
    InArr = Application.WorksheetFunction.Transpose( _
    Selection.Offset(2, 0).Resize(Selection.Rows.Count - 2).Value)
    HaveRandomNegatives = checkRandomNegatives(InArr)
    If Not HaveRandomNegatives Then
    ElseIf MsgBox("At least 1 negative number is present between positive numbers" _
    & vbNewLine _
    & "It may take a lot longer to search for matches." & vbNewLine _
    & "OK to continue else Cancel", vbOKCancel) = vbCancel Then
    Exit Sub
    End If
    ReDim Rslt(0)
    recursiveMatch MaxSoln, TargetVal, InArr, HaveRandomNegatives, _
    LBound(InArr), 0, 0.00000001, _
    Rslt, "", ", "
    Rslt(UBound(Rslt)) = Format(Now, "hh:mm:ss")
    ReDim Preserve Rslt(UBound(Rslt) + 1)
    Rslt(UBound(Rslt)) = Format(StartTime, "hh:mm:ss")
    Selection.Offset(0, 1).Resize(ArrLen(Rslt), 1).Value = _
    Application.WorksheetFunction.Transpose(Rslt)
    Exit Sub
    ErrXIT:
    MsgBox "Please select cells in a single column before using this macro" & vbNewLine _
    & "The selection should be a single contiguous range in a single column." & vbNewLine _
    & "The first cell indicates the number of solutions wanted. Specify zero for all." & vbNewLine _
    & "The 2nd cell is the target value." & vbNewLine _
    & "The rest of the cells are the values available for matching." & vbNewLine _
    & "The output is in the column adjacent to the one containing the input data."
    End Sub

    --------------------------------

  2. #2
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Amsterdam...
    Posts
    5,343

    Re: Combination of list of values to a target range

    Couldnt you do this with a column that checks if the value is range of values, and then sumif or subtotal (or a function something like that) to give you the totals. no need for vba...

    unless you are practicing...
    in which case, write something yourself, based on your internet findings...
    also use the code tags... the # button above the editor, around the posted code.

    for coding loop -pseudo code:
    - hold a total set to 0
    - loop rows
    - -- if value is >= min and <=max add to total
    - return total to cell/message box

    the above assumes a continuous range or sheet of values. If you need randomly selected ranges of cells... well thats a bit more difficult.

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

  3. #3

    Thread Starter
    New Member
    Join Date
    Sep 2017
    Posts
    3

    Re: Combination of list of values to a target range

    Thanks a lot Ecniv..will try and then comeback..

Posting Permissions

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



Click Here to Expand Forum to Full Width