I have a 2,000 row column (A) that contains either positive or negative
numbers.
In an adjacent column (B) I need a user defined function in each row which
counts how many rows preceding the active row in Column A contain unbroken
strings of either positive (or negative) numbers.
So for example the function in cell B2000 would determine that A2000 was,
say, positive, and then return a value that counted how many consecutive
cells preceding A2000 were also positive, before changing to negative.
I am sure I could, eventually, come up with some VBA code that looped until
while counting, but would find a Function much more useful.
As always, all suggestions and assistance will be much appreciated.
MichaelC

Michael,

Try this:

Public Function NthSignConsecutive(rngInput As Range) As Long

Dim lOffset As Long
Dim bPositive As Boolean
Dim lRow As Long
Dim lCol As Long

'note according to your specs this actually returns one less than the
'total number of consecutive sign numbers since it doesn't count the
'row in which it was entered

'and no error checking or type checking at present

lOffset = -1

bPositive = (rngInput.Value 0)
lRow = rngInput.Row + lOffset
lCol = rngInput.Column

With rngInput.Parent

Do While lRow 0

If (.Cells(lRow, lCol).Value 0) = bPositive Then

NthSignConsecutive = NthSignConsecutive + 1
lRow = lRow - 1

Else

Exit Do

End If

Loop

End With

End Function

Robin Hammond
www.enhanceddatasystems.com

"MichaelC" wrote in message
...
I have a 2,000 row column (A) that contains either positive or negative
numbers.
In an adjacent column (B) I need a user defined function in each row which
counts how many rows preceding the active row in Column A contain unbroken
strings of either positive (or negative) numbers.
So for example the function in cell B2000 would determine that A2000 was,
say, positive, and then return a value that counted how many consecutive
cells preceding A2000 were also positive, before changing to negative.
I am sure I could, eventually, come up with some VBA code that looped
until
while counting, but would find a Function much more useful.
As always, all suggestions and assistance will be much appreciated.
MichaelC

Thank you very much for your detailed response, Robin. The function does
appear in my list. When I enter the function into a cell in the column
adacent to the first column, I am asked for the range, and I enter the column
1 range.
But the answer comes out as #VALUE.
I am not proficient enough to figure out the solution.
I would much appreciate any suggestion to fix this.

"Robin Hammond" wrote:

Michael,

Try this:

Public Function NthSignConsecutive(rngInput As Range) As Long

Dim lOffset As Long
Dim bPositive As Boolean
Dim lRow As Long
Dim lCol As Long

'note according to your specs this actually returns one less than the
'total number of consecutive sign numbers since it doesn't count the
'row in which it was entered

'and no error checking or type checking at present

lOffset = -1

bPositive = (rngInput.Value 0)
lRow = rngInput.Row + lOffset
lCol = rngInput.Column

With rngInput.Parent

Do While lRow 0

If (.Cells(lRow, lCol).Value 0) = bPositive Then

NthSignConsecutive = NthSignConsecutive + 1
lRow = lRow - 1

Else

Exit Do

End If

Loop

End With

End Function

Robin Hammond
www.enhanceddatasystems.com

"MichaelC" wrote in message
...
I have a 2,000 row column (A) that contains either positive or negative
numbers.
In an adjacent column (B) I need a user defined function in each row which
counts how many rows preceding the active row in Column A contain unbroken
strings of either positive (or negative) numbers.
So for example the function in cell B2000 would determine that A2000 was,
say, positive, and then return a value that counted how many consecutive
cells preceding A2000 were also positive, before changing to negative.
I am sure I could, eventually, come up with some VBA code that looped
until
while counting, but would find a Function much more useful.
As always, all suggestions and assistance will be much appreciated.
MichaelC

Put this in cell b1 and copy it all the way down the length of your data.

=NthSignConsecutive(A1)

Robin Hammond
www.enhanceddatasystems.com

"MichaelC" wrote in message
...
Thank you very much for your detailed response, Robin. The function does
appear in my list. When I enter the function into a cell in the column
adacent to the first column, I am asked for the range, and I enter the
column
1 range.
But the answer comes out as #VALUE.
I am not proficient enough to figure out the solution.
I would much appreciate any suggestion to fix this.

"Robin Hammond" wrote:

Michael,

Try this:

Public Function NthSignConsecutive(rngInput As Range) As Long

Dim lOffset As Long
Dim bPositive As Boolean
Dim lRow As Long
Dim lCol As Long

'note according to your specs this actually returns one less than the
'total number of consecutive sign numbers since it doesn't count the
'row in which it was entered

'and no error checking or type checking at present

lOffset = -1

bPositive = (rngInput.Value 0)
lRow = rngInput.Row + lOffset
lCol = rngInput.Column

With rngInput.Parent

Do While lRow 0

If (.Cells(lRow, lCol).Value 0) = bPositive Then

NthSignConsecutive = NthSignConsecutive + 1
lRow = lRow - 1

Else

Exit Do

End If

Loop

End With

End Function

Robin Hammond
www.enhanceddatasystems.com

"MichaelC" wrote in message
...
I have a 2,000 row column (A) that contains either positive or negative
numbers.
In an adjacent column (B) I need a user defined function in each row
which
counts how many rows preceding the active row in Column A contain
unbroken
strings of either positive (or negative) numbers.
So for example the function in cell B2000 would determine that A2000
was,
say, positive, and then return a value that counted how many
consecutive
cells preceding A2000 were also positive, before changing to negative.
I am sure I could, eventually, come up with some VBA code that looped
until
while counting, but would find a Function much more useful.
As always, all suggestions and assistance will be much appreciated.
MichaelC

Thank you so much Robin - that works like magic. Just visited your website
and am impressed. Now I understand how you appreared to intuit what it was I
was trying to accomplish: to identify the lowest points of an Awesome
Oscillator histogram over a ten year period for a very clunky currency
trading model I am working on.


"Robin Hammond" wrote:

Put this in cell b1 and copy it all the way down the length of your data.

=NthSignConsecutive(A1)

Robin Hammond
www.enhanceddatasystems.com

"MichaelC" wrote in message
...
Thank you very much for your detailed response, Robin. The function does
appear in my list. When I enter the function into a cell in the column
adacent to the first column, I am asked for the range, and I enter the
column
1 range.
But the answer comes out as #VALUE.
I am not proficient enough to figure out the solution.
I would much appreciate any suggestion to fix this.

"Robin Hammond" wrote:

Michael,

Try this:

Public Function NthSignConsecutive(rngInput As Range) As Long

Dim lOffset As Long
Dim bPositive As Boolean
Dim lRow As Long
Dim lCol As Long

'note according to your specs this actually returns one less than the
'total number of consecutive sign numbers since it doesn't count the
'row in which it was entered

'and no error checking or type checking at present

lOffset = -1

bPositive = (rngInput.Value 0)
lRow = rngInput.Row + lOffset
lCol = rngInput.Column

With rngInput.Parent

Do While lRow 0

If (.Cells(lRow, lCol).Value 0) = bPositive Then

NthSignConsecutive = NthSignConsecutive + 1
lRow = lRow - 1

Else

Exit Do

End If

Loop

End With

End Function

Robin Hammond
www.enhanceddatasystems.com

"MichaelC" wrote in message
...
I have a 2,000 row column (A) that contains either positive or negative
numbers.
In an adjacent column (B) I need a user defined function in each row
which
counts how many rows preceding the active row in Column A contain
unbroken
strings of either positive (or negative) numbers.
So for example the function in cell B2000 would determine that A2000
was,
say, positive, and then return a value that counted how many
consecutive
cells preceding A2000 were also positive, before changing to negative.
I am sure I could, eventually, come up with some VBA code that looped
until
while counting, but would find a Function much more useful.
As always, all suggestions and assistance will be much appreciated.
MichaelC