thanks - we all appreciate feedback - especially the good ones :)


"zeroscout" wrote in message
...
Hello Patrick,
Thank you, this works very well! I have tested the function with an
offset
formula in the report and it works awesome. This will get me through the
current report.

Now I will see if I am able to engineer in a programmatic solution for the
table variable and I will post back my efforts.

Thank you again, this function is a huge help,
Brian

"Patrick Molloy" wrote:

pass into this function just the column of sales (548,407,503,617,575,444
...) and the stock amount (4573)

Function Inventory(table As Range, stock As Long)
Dim cell As Range
Dim weeks As Double
Dim total As Long
For Each cell In table.Cells
If (total + cell.Value) <= stock Then
weeks = weeks + 1
total = total + cell.Value
Else
weeks = weeks + (stock - total) / cell.Value
Exit For
End If
Next
Inventory = weeks
End Function