vba - Finding multiple combinations of sums in Excel

Vba - Finding multiple combinations of sums in Excel

Certainly! If you're looking to find multiple combinations of sums in Excel using VBA, you can create a VBA macro to achieve this. The following is a simple example of a VBA code that finds all combinations of sums for a given target value:

Option Explicit

Dim resultArr As Variant

Sub FindSumCombinations()
    Dim inputArr As Variant
    Dim targetSum As Double
    
    ' Set your input array and target sum
    inputArr = Range("A1:A10").Value ' Update with your actual range
    targetSum = 20 ' Update with your target sum
    
    ' Initialize the result array
    ReDim resultArr(1 To UBound(inputArr, 1), 1 To 2)
    
    ' Call the recursive function to find combinations
    FindCombinations inputArr, targetSum, 1, 1
    
    ' Output the result to a new sheet or display in Immediate Window
    OutputResult
End Sub

Sub FindCombinations(inputArr As Variant, targetSum As Double, currentIndex As Integer, resultIndex As Integer)
    Dim i As Integer
    
    For i = currentIndex To UBound(inputArr, 1)
        If inputArr(i, 1) = targetSum Then
            ' Found a combination
            resultArr(resultIndex, 1) = inputArr(i, 1)
            resultArr(resultIndex, 2) = "Found"
            resultIndex = resultIndex + 1
        ElseIf inputArr(i, 1) < targetSum Then
            ' Recursive call to find combinations
            resultArr(resultIndex, 1) = inputArr(i, 1)
            FindCombinations inputArr, targetSum - inputArr(i, 1), i + 1, resultIndex + 1
        End If
    Next i
End Sub

Sub OutputResult()
    ' Output the result to a new sheet or display in Immediate Window
    Dim outputSheet As Worksheet
    Set outputSheet = Sheets.Add
    
    outputSheet.Range("A1").Resize(UBound(resultArr, 1), UBound(resultArr, 2)).Value = resultArr
End Sub

Make sure to update the inputArr range and the targetSum variable according to your specific case. This code uses recursion to find all combinations that add up to the target sum and outputs the result to a new sheet.

Please note that this is a basic example, and you might need to adapt it based on your specific requirements and data structure.

Examples

  1. "VBA find combinations of sums for a specific target"

    • Description: Learn how to find all combinations of sums that equal a specific target value in VBA.
    ' Code Implementation
Sub FindCombinationsForTarget()
    Dim target As Integer
    target = 50 ' Set your target sum
    FindCombinationsRecursive 1, target, ""
End Sub

Sub FindCombinationsRecursive(startNum As Integer, remainingTarget As Integer, currentCombination As String)
    Dim i As Integer
    For i = startNum To remainingTarget
        If i = remainingTarget Then
            Debug.Print currentCombination & i
        Else
            FindCombinationsRecursive i, remainingTarget - i, currentCombination & i & "+"
        End If
    Next i
End Sub

  2. "VBA find combinations of sums for a range of values"

    • Description: Understand how to find all combinations of sums for a range of values in VBA.
    ' Code Implementation
Sub FindCombinationsForRange()
    Dim valuesRange As Range
    Set valuesRange = Range("A1:A5") ' Set your range of values
    Dim target As Integer
    target = 50 ' Set your target sum
    FindCombinationsForRangeRecursive valuesRange, target, ""
End Sub

Sub FindCombinationsForRangeRecursive(valuesRange As Range, remainingTarget As Integer, currentCombination As String)
    Dim cell As Range
    For Each cell In valuesRange
        If cell.Value = remainingTarget Then
            Debug.Print currentCombination & cell.Value
        ElseIf cell.Value < remainingTarget Then
            FindCombinationsForRangeRecursive valuesRange, remainingTarget - cell.Value, currentCombination & cell.Value & "+"
        End If
    Next cell
End Sub

  3. "VBA find all possible combinations of sums"

    • Description: Explore how to find all possible combinations of sums for a set of values in VBA.
    ' Code Implementation
Sub FindAllCombinations()
    Dim valuesArray As Variant
    valuesArray = Array(10, 20, 30, 40) ' Set your array of values
    Dim target As Integer
    target = 50 ' Set your target sum
    FindAllCombinationsRecursive valuesArray, target, ""
End Sub

Sub FindAllCombinationsRecursive(valuesArray As Variant, remainingTarget As Integer, currentCombination As String)
    Dim i As Integer
    For i = LBound(valuesArray) To UBound(valuesArray)
        If valuesArray(i) = remainingTarget Then
            Debug.Print currentCombination & valuesArray(i)
        ElseIf valuesArray(i) < remainingTarget Then
            FindAllCombinationsRecursive valuesArray, remainingTarget - valuesArray(i), currentCombination & valuesArray(i) & "+"
        End If
    Next i
End Sub

  4. "VBA find unique combinations of sums"

    • Description: Learn how to find unique combinations of sums for a set of values in VBA.
    ' Code Implementation
Sub FindUniqueCombinations()
    Dim valuesArray As Variant
    valuesArray = Array(10, 20, 30, 40) ' Set your array of values
    Dim target As Integer
    target = 50 ' Set your target sum
    Dim uniqueCombinations As Collection
    Set uniqueCombinations = New Collection
    FindUniqueCombinationsRecursive valuesArray, target, "", uniqueCombinations
End Sub

Sub FindUniqueCombinationsRecursive(valuesArray As Variant, remainingTarget As Integer, currentCombination As String, uniqueCombinations As Collection)
    Dim i As Integer
    For i = LBound(valuesArray) To UBound(valuesArray)
        If valuesArray(i) = remainingTarget Then
            Dim combinationArray As Variant
            combinationArray = Split(currentCombination & valuesArray(i), "+")
            combinationArray = RemoveDuplicatesFromArray(combinationArray)
            uniqueCombinations.Add combinationArray
        ElseIf valuesArray(i) < remainingTarget Then
            FindUniqueCombinationsRecursive valuesArray, remainingTarget - valuesArray(i), currentCombination & valuesArray(i) & "+", uniqueCombinations
        End If
    Next i
End Sub

Function RemoveDuplicatesFromArray(inputArray As Variant) As Variant
    Dim uniqueItems As Collection
    Set uniqueItems = New Collection
    Dim item As Variant
    On Error Resume Next
    For Each item In inputArray
        uniqueItems.Add item, CStr(item)
    Next item
    On Error GoTo 0
    RemoveDuplicatesFromArray = CollectionToArray(uniqueItems)
End Function

Function CollectionToArray(inputCollection As Collection) As Variant
    Dim outputArray() As Variant
    ReDim outputArray(1 To inputCollection.Count)
    Dim i As Integer
    For i = 1 To inputCollection.Count
        outputArray(i) = inputCollection(i)
    Next i
    CollectionToArray = outputArray
End Function

  5. "VBA find combinations of sums with a maximum count"

    • Description: Understand how to find combinations of sums with a maximum count of values in VBA.
    ' Code Implementation
Sub FindCombinationsWithMaxCount()
    Dim valuesArray As Variant
    valuesArray = Array(10, 20, 30, 40) ' Set your array of values
    Dim target As Integer
    target = 50 ' Set your target sum
    Dim maxCount As Integer
    maxCount = 2 ' Set the maximum count of values in a combination
    FindCombinationsWithMaxCountRecursive valuesArray, target, "", maxCount
End Sub

Sub FindCombinationsWithMaxCountRecursive(valuesArray As Variant, remainingTarget As Integer, currentCombination As String, maxCount As Integer)
    Dim i As Integer
    For i = LBound(valuesArray) To UBound(valuesArray)
        If valuesArray(i) = remainingTarget And Len(currentCombination & valuesArray(i)) / Len("+") = maxCount - 1 Then
            Debug.Print currentCombination & valuesArray(i)
        ElseIf valuesArray(i) < remainingTarget And Len(currentCombination & valuesArray(i)) / Len("+") < maxCount - 1 Then
            FindCombinationsWithMaxCountRecursive valuesArray, remainingTarget - valuesArray(i), currentCombination & valuesArray(i) & "+", maxCount
        End If
    Next i
End Sub

  6. "VBA find combinations of sums with a minimum count"

    • Description: Explore how to find combinations of sums with a minimum count of values in VBA.
    ' Code Implementation
Sub FindCombinationsWithMinCount()
    Dim valuesArray As Variant
    valuesArray = Array(10, 20, 30, 40) ' Set your array of values
    Dim target As Integer
    target = 50 ' Set your target sum
    Dim minCount As Integer
    minCount = 2 ' Set the minimum count of values in a combination
    FindCombinationsWithMinCountRecursive valuesArray, target, "", minCount
End Sub

Sub FindCombinationsWithMinCountRecursive(valuesArray As Variant, remainingTarget As Integer, currentCombination As String, minCount As Integer)
    Dim i As Integer
    For i = LBound(valuesArray) To UBound(valuesArray)
        If valuesArray(i) = remainingTarget And Len(currentCombination & valuesArray(i)) / Len("+") = minCount - 1 Then
            Debug.Print currentCombination & valuesArray(i)
        ElseIf valuesArray(i) < remainingTarget And Len(currentCombination & valuesArray(i)) / Len("+") < minCount - 1 Then
            FindCombinationsWithMinCountRecursive valuesArray, remainingTarget - valuesArray(i), currentCombination & valuesArray(i) & "+", minCount
        End If
    Next i
End Sub

  7. "VBA find combinations of sums within a specific range"

    • Description: Learn how to find combinations of sums within a specific range in VBA.
    ' Code Implementation
Sub FindCombinationsWithinRange()
    Dim valuesArray As Variant
    valuesArray = Array(10, 20, 30, 40) ' Set your array of values
    Dim target As Integer
    target = 50 ' Set your target sum
    Dim minRange As Integer
    minRange = 40 ' Set the minimum value in the range
    Dim maxRange As Integer
    maxRange = 60 ' Set the maximum value in the range
    FindCombinationsWithinRangeRecursive valuesArray, target, "", minRange, maxRange
End Sub

Sub FindCombinationsWithinRangeRecursive(valuesArray As Variant, remainingTarget As Integer, currentCombination As String, minRange As Integer, maxRange As Integer)
    Dim i As Integer
    For i = LBound(valuesArray) To UBound(valuesArray)
        If valuesArray(i) = remainingTarget And Len(currentCombination & valuesArray(i)) / Len("+") > 0 Then
            If valuesArray(i) >= minRange And valuesArray(i) <= maxRange Then
                Debug.Print currentCombination & valuesArray(i)
            End If
        ElseIf valuesArray(i) < remainingTarget Then
            FindCombinationsWithinRangeRecursive valuesArray, remainingTarget - valuesArray(i), currentCombination & valuesArray(i) & "+", minRange, maxRange
        End If
    Next i
End Sub

  8. "VBA find combinations of sums with distinct values"

    • Description: Understand how to find combinations of sums with distinct values in VBA.
    ' Code Implementation
Sub FindCombinationsWithDistinctValues()
    Dim valuesArray As Variant
    valuesArray = Array(10, 20, 30, 40) ' Set your array of values
    Dim target As Integer
    target = 50 ' Set your target sum
    Dim distinctCount As Integer
    distinctCount = 2 ' Set the count of distinct values in a combination
    FindCombinationsWithDistinctValuesRecursive valuesArray, target, "", distinctCount
End Sub

Sub FindCombinationsWithDistinctValuesRecursive(valuesArray As Variant, remainingTarget As Integer, currentCombination As String, distinctCount As Integer)
    Dim i As Integer
    For i = LBound(valuesArray) To UBound(valuesArray)
        If valuesArray(i) = remainingTarget And Len(currentCombination & valuesArray(i)) / Len("+") = distinctCount - 1 Then
            Dim combinationArray As Variant
            combinationArray = Split(currentCombination & valuesArray(i), "+")
            combinationArray = RemoveDuplicatesFromArray(combinationArray)
            If UBound(combinationArray) + 1 = distinctCount Then
                Debug.Print currentCombination & valuesArray(i)
            End If
        ElseIf valuesArray(i) < remainingTarget And Len(currentCombination & valuesArray(i)) / Len("+") < distinctCount - 1 Then
            FindCombinationsWithDistinctValuesRecursive valuesArray, remainingTarget - valuesArray(i), currentCombination & valuesArray(i) & "+", distinctCount
        End If
    Next i
End Sub

More Tags

simple-openni ngroute sanitization r-leaflet samsung-galaxy x11 locking alamofire utf8mb4 signature

More Programming Questions

More Cat Calculators

More Mixtures and solutions Calculators

More Investment Calculators

More Fitness Calculators