-
Oct 4th, 2017, 12:42 AM
#1
Thread Starter
New Member
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
--------------------------------
-
Oct 6th, 2017, 12:16 PM
#2
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.
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...
-
Oct 7th, 2017, 10:50 AM
#3
Thread Starter
New Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|