Loop in VBA choosing different sets of data until it sums to X

Question

I have a series of data in Excel (280,000 lines) with random values. Each line represents the total ticket of consumptions made in a supermarket branch.

I need to select tickets from those 280,000 lines that add up to USD 1,500,000.

I thought first minimizing errors by using binaries on Solver but the variable limit was too low.

I am thinking of trying with do while / do until. How do I structure this?

Answer 1

Create a Collection called Tickets. Create a variable to hold the sum, called Total. Then you can loop down the sheet, adding tickets to the total and saving the rows in the collection until you reach 1,500,000.

Sub Example()
    Const STARTING_ROW As Long = 2 'Change this
    Const VALUE_COLUMN As Long = 5 'Change this
    
    Dim DataSheet As Worksheet
    Set DataSheet = ActiveSheet 'Change this
    
    Dim CurrentRow As Range
    Set CurrentRow = DataSheet.Rows(STARTING_ROW)
    
    Dim Tickets As New Collection, Total As Double
    While Total < 1500000
        Tickets.Add CurrentRow
        With CurrentRow.Columns(VALUE_COLUMN)
            If IsNumeric(.Value) Then Total = Total + .Value
        End With

        'Exit condition - End of Sheet
        If CurrentRow.Row = CurrentRow.Parent.Rows.Count Then 
            Exit Sub
        Else
            Set CurrentRow = CurrentRow.Offset(1)
        End If
    Wend
End Sub

At the end of this While Loop, you will have all of the ranges inside the Tickets collection, whose values sum up to at least 1,500,000.