thanks for reading and helping out.

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 to select tickets from those 280,000 lines that add up to USD 1,500,000.

Anyone knows how I can create that? I thought first minimizing errors by using binaries on Solver but of course the variable limit was too low.

I am thinking of trying with do while / do until, but can't figure out how to put it into code. Does anybody have a clue how to structure this?

Any suggestions are welcome.

Thanks!

CodePudding user response：

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.