Adding cells until they are greater than or equal to another number

[Raley]

I can't seem to figure out how to write the formula/script to add each cell in "Money Made" until it is greater than or equal to Money Owed and then have it related the years in the "How many Years to Pay off" column.
The answer I would be looking for here is 3 Years to pay off.
Thank you in advance

 

[pjmorris]

This should work, note that it will also show 4, 5 etc for all subsequent years. Should you extend the loan say in year 3 then that too will be taken into account.

Book1
	A	B	C	D
1	Year	Money Made	Money Owed	How Many Years to Pay Off
2	1	$6,150.00	($16,050.00 )
3	2	$7,150.00
4	3	$8,150.00		3
5	4	$9,150.00		4
Sheet1

Cell Formulas
Range	Formula
D2:D5	D2	=IF(SUM($B$2:$B2)+SUM($C$2:$C2)>0,[@Year],"")

HTH

 

[Saurabhj]

Hi Raley,

use below code:

Sub CalculateYears()
    Dim totalRows As Integer, totalAmount As Long, rowno As Integer
    totalRows = WorksheetFunction.CountA(ActiveSheet.Range("B:B"))
    totalAmount = 0
    
    For rowno = 2 To totalRows
        totalAmount = totalAmount + ActiveSheet.Range("B" & rowno)
        If totalAmount >= ActiveSheet.Range("C2") Then Exit For
    Next
    ActiveSheet.Range("D2") = rowno - 1
End Sub

Thanks,
Saurabh

 

[Raley]

Thank you both I am opting to go with the VB fix

 

[pjmorris]

That’s why different answers are encouraged. Thanks for the feedback.