VBA Adding values from a column until it reaches a certain number

Question

I have a column with values from "R85" to "R193". In the previous column I have names for those values from "Q85" to "Q193". I want it to add one by one untill it reaches a specified number in "I2" The ideea is that I want it to add the numbers untill it reaches a number close to that I2 value (<=) , let's say 420, and give me the output of the value in another cell. The more complicated part is that I need the names from Q85 and the last value it calculated on the R column. If Anyone could help I would be so grateful ! I searched everywhere and couldn't find anything that I could adapt for this ideea. Thank you!

Answer 1

Consider the following User Defined Function:

Public Function SumUntil(rng As Range, lim As Variant) As Variant
    Dim r As Range, tot As Variant
    tot = 0
    
    For Each r In rng
        tot = tot + r.Value
        If tot > lim Then
            sumuntil = tot - r.Value
            Exit Function
        End If
    Next r
    
    sumuntil = tot
End Function

In the above example, the function returns the sum of the first four items in column A