OK, I'm going to talk a bit about how I see what you are trying to do.
You have a cell with the actual inventory in it and below that cell
(maybe right up against it, maybe with some rows with blanks or text or
whatever) you have a series of rows with actual weekly sales of the
item. All these numbers (inventory and past sales) are in the same
column. Let's plan on there being some rows between the inventory value
and the first of the weekly sales because that's the more general case
and if you can get this to work with those rows, you can get it to work
without those 'extra' rows.
This means that your function will have to have the location of the
cell with the inventory in it and the cell at the top of the set of
weekly sales, so the function will have to be passed these two
locations, as ranges.
So far the function looks like
Public Function WOI(rInv As Range, rWks As Range) As Double
End Function
where WOI stands for Weeks Of Inventory, rInv is the cell (say J3) that
holds the inventory and rWkS is the cell at the top of the Weekly Sales
(say J7) and the value that needs to be returned is a single or double -
I'll go with a double.
You were onto something with the idea of using Offsets, but Offsets
need to be from Ranges, not variables like your invST (an integer, I
believe).
If we're going to be looping, we are probably going to need a counter
or index variable. If we are going to be keeping a running total of
weekly sales, we probably need something to store that in and we may
need something to hold the value of the inventory. Let's Dim a few
things in the function and do some initializations
Public Function WOI(rInv As Range, rWks As Range) As Double
Dim dSum As Double
Dim dOldSum As Double
Dim dInv As Double
Dim lL As Long
lL = 0
dInv = rInv.Value
dSum = rWks.Offset(lL, 0).Value
End Function
You'll notice that dInv now holds the value of the cell where the
inventory is and dSum holds the first of the weekly sales, the value of
the cell at the top of the weekly sales. Yes, the value of dSum could
have been determined by
dSum = rWks.Value
but the way it was defined above is a foreshadowing of how we will be
getting weekly sales values once inside the while/wend loop - so let's
add that loop now:
Public Function WOI(rInv As Range, rWks As Range) As Double
Dim dSum As Double
Dim dOldSum As Double
Dim dInv As Double
Dim lL As Long
lL = 0
dInv = rInv.Value
dSum = rWks.Offset(lL, 0).Value
'You will stay in this loop until dSum includes one week too many,
so
' save the old sum in dOldSum
While dInv dSum
dOldSum = dSum
lL = lL + 1
dSum = dSum + rWks.Offset(lL, 0).Value
Wend
End Function
Let's talk through what's going on in the loop. While the sum of all
weeks sales is less than the inventory, the code inside the loop will
execute. First, the previous value of dSum is cached in dOldSum, then
lL is incremented and finally, the next weekly sales value is added to
the previous dSum. As the comment says, this loop will run until it has
added in the statement
dSum = dSum + rWks.Offset(lL, 0).Value
one too many of the weekly sales values. Therefore, we need to use
dOldSum (the last sum of the weekly sales which was less than the
inventory) to calculate what fraction of that last-step-too-far weekly
sales value is needed to bring dOldSum just up to dInv. And here's that
in the function:
Public Function WOI(rInv As Range, rWks As Range) As Double
Dim dSum As Double
Dim dOldSum As Double
Dim dInv As Double
Dim lL As Long
lL = 0
dInv = rInv.Value
dSum = rWks.Offset(lL, 0).Value
'You will stay in this loop until dSum includes one week too many,
so
' save the old sum in dOldSum
While dInv dSum
dOldSum = dSum
lL = lL + 1
dSum = dSum + rWks.Offset(lL, 0).Value
Wend
WOI = CDbl(lL) + (dInv - dOldSum) / rWks.Offset(lL, 0).Value
End Function
Now there are a few things that need to be tested (I did do a simple
test, just to check my logic) and potential problems to be thought
through:
1) What does the code do if the first weekly sales happens to be more
than the inventory - does the value get calculated correctly?
2) What happens if the loop reaches the bottom of the weekly sales and
dSum is still less than dInv? This one is going to take more thought,
especially if it's possible that there were no sales during a week and
the person who enters the sales decides it's OK to just leave the cell
blank for that week. Since you know more about your specific
application than I do, the ball is in your court for this, but one
possible hint is to check if the value of the weekly inventory is "".
3) Look up Application.Volatile in the help files and decide how you
want the function to recalculate and add, if you decide it's appropriate
a line
Application.Volatile = True after the Dim statements
Finally, you will note that in your code, you looked at the loop
criterion differently than I did - you were, in effect, checking if dSum
was less than dInv. Doing it that way would affect the code in the
function above - just for grins, go ahead and modify it to use that
inequality test, and think through the ramifications - you may find
things get simpler if you do.
To use this UDF, go to a cell of your choice, type in
=woi(
then click on the cell with the inventory in it, say J3, they type a
comma
=woi(J3,
then click on the cell at the top of the weekly sales values, say J7,
then close the parentheses
=woi(J3,J7)
and you should get your answer.
Hope this has helped... :Bgr
--- Automerged consecutive post before response ---
:Bgr
Or, you could just use Patrick Molloy's solution - he got in, worked it
out and posted it while I was still typing mine...
--
jamescox
------------------------------------------------------------------------
jamescox's Profile:
http://www.thecodecage.com/forumz/member.php?userid=449
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=110734