Solved: function to find the number of iterations/loops Do...Loop

[helix123]

Hi,

I'm trying to learn vba and have a problem which I would like to use a user defined function for in excel.

What I want to be able to do is input the value of a budget (say $30,000) and then subtract off each weekly cost (which varies week to week) until the budget runs out and then have a counter which counts the number of iterations/loops that the initial budget lasted for given that it started from a particular week.

In the first column I have which week it is i.e. week1, week2, week3 etc..

In the second column I have the total cost associated with each particular week i.e. for week1 the total cost is $502 for week2 it is $508 etc.. (see attached file)

Ideally it would be good to have a function where the first argument is the budget amount and the second argument would be the start week. For example starting the iterations on week2 the first iteration would be $30000-$508.

The end goal is to have the number of weeks a particular budget amount will last before reaching zero starting from any given week.

Hope this makes sense.

I know how to get an answer using excel formulas but it would be good to learn how to do this with vba..

Thanks in advance.

[Kenneth Hobs]

Welcome to the forum!
' Cost column assumed to be in column to right of week column.
' J2 =WeekPaidInFull(I2,B2)
Function WeekPaidInFull(rBudget, rCostWeek As Range) As String
Dim s As String, d As Double
Application.Volatile
d = rBudget - rCostWeek.Value2
Do
Set rCostWeek = rCostWeek.Offset(1)
d = d - rCostWeek.Value2
Loop Until d <= 0 Or IsEmpty(rCostWeek)
WeekPaidInFull = rCostWeek.Offset(0, -1)
End Function

[helix123]

Thank you Kenneth!

You have made my day, if you ever come to New Zealand I owe you a beer

:beerchug:


Now I just have to work how you did it. .

[helix123]

The user defined function you wrote works well.

However, is there anyway that it could be modified so that it returns the number of weeks that the budget will last?

For example at the moment with $30000 budget and starting from week 6 the function returns "Week 54" which is correct that is the week that the budget lasts to if starting costs from week6.

Is there a way though to get the formula to return a value of 48 i.e. week54-week6 = 48 weeks.

[Kenneth Hobs]

' =WeekPaidInFullc(I2,B7)
Function WeekPaidInFullc(rBudget, rCostWeek As Range) As Long
Dim s As String, d As Double, i As Long
Application.Volatile
d = rBudget - rCostWeek.Value2
i = 0
Do
Set rCostWeek = rCostWeek.Offset(1)
d = d - rCostWeek.Value2
i = i + 1
Loop Until d <= 0 Or IsEmpty(rCostWeek)
WeekPaidInFullc = i
End Function

[helix123]

Excellent!

Thank you for this.

[snb]

or


'=WeekPaidInFullc(I2,B7:B174)


Function WeekPaidInFullc(rBudget, rCostWeek As Range) As Long
For j = 1 To rCostWeek.Count
If Application.Sum(rCostWeek.Cells(1).Resize(j)) >= rBudget Then Exit For
Next
WeekPaidInFullc = j - 1
End Function

[helix123]

Cheers.