Hi, BenefitRich!
About your comment.
It's just a matter of practice, only requiring a regular dose of write, review, rewrite, update, erase, write again... and when you realize that are spending more time in other than writing, you begin to learn how to write... and that's the only secret.
About the code.
It doesn't require such a deep analysis, let me help you.
a)
[pre]
Code:
' constants
Const kiFirstColumn = 5
Const ksStart = "S"
Const ksEnd = "E"
Const kdDate0 = #1/1/1900#
Const ksError = "Error"
I hate hardcoding fixed values within the code, so it's more clear assigning a meaningful name.
b)
[pre][code]' declarations
Dim iColumn As Integer, iOrder As Integer, nValue As Single, nValueAnt As Single
Dim dDate As Date
The same for variables not defined or defined without type, so it aids to not to misuse or assume incorrect data.
c)
[pre][code]' start
iColumn = kiFirstColumn
nValueAnt = 0
dDate = kdDate0
iOrder = 0
[/pre]
iColumn: pointer to navigate thru the date & stock values
iOrder: occurrence number of negative sequence
nValue: stock for iColumn
nValueAnt: idem for previous (key for the process!!!)
dDate: date found
d)
' process
Do
' stock
nValue = Cells(plRow, iColumn).Value
' sequence
Select Case nValue
' from pos to neg
Case Is < 0
If nValueAnt >= 0 Then
If psStartEnd = ksStart Then
iOrder = iOrder + 1
If iOrder = piOrder Then dDate = Cells(1, iColumn).Value
End If
End If
' from neg to pos
Case Is >= 0
If nValueAnt < 0 Then
If psStartEnd = ksEnd Then
iOrder = iOrder + 1
If iOrder = piOrder Then dDate = Cells(1, iColumn - 1).Value
End If
End If
End Select
' cycle
nValueAnt = nValue
iColumn = iColumn + 1
Loop Until Cells(plRow, iColumn - 1).Offset(-1, 0).Value = "" Or _
iOrder = piOrder And dDate <> kdDate0[/code][/pre]
Here is the core.
For each stock value at current column, it only checks two cases:
1) change from positive to negative (used for the date start parameter)
2) change from negative to positive (used for the date end parameter)
If there's a hit, it increments iCounter.
Then it saves the actual stock value as previous in nValueAnt.
And loops moving one column right (here I had a problem with border conditions -as usual, not just me!-, so it checks not until last date column but first empty one, because of 2).
e)
' end
If dDate = kdDate0 Then
vNegativeDate = ksError
Else
vNegativeDate = dDate
End If[/code][/pre]
The easy part, returning the desired value. I used a variant type for the function, because you wanted to return a date or an error. And with this simple If, I avoid writing a more complicate formula in the worksheet, and multiple times, indeed:
Instead of "=vNegativeDate(2,$A17,"S")" it would have been necessary "=IF(vNegativeDate(2,$A17,"S")=DATE(1900,1,1),"Error",vNegativeDate(2,$A17,"S")".
And that's all. The only secret/smartness are those two little pieces of code within each case sentence.
Hope it helped you.
Regards!
))