I want to count the rows bottom up for a column value. As soon as the
sign is changed or the number is the value of a zero, counter finishes
counting. Ex,
-2.1
0.3
-1.1
44
13.1
12.2
counter x = 3

31.2
44.8
9.4
10.4
-2.2
-11.1
counter x = 2

5.3
9
0
3.2
counter x = 1

Thanks.

fzl2007 wrote:

I want to count the rows bottom up for a column value. As soon as the
sign is changed or the number is the value of a zero, counter finishes
counting. Ex,

Something like this, perhaps?

Function FindSignChangeOrZero() As Long
'Replace ActiveCell with whatever you need...
col = ActiveCell.Column
If ActiveCell.SpecialCells(xlCellTypeLastCell).Value < 0 Then
s = Sgn(Cells.SpecialCells(xlCellTypeLastCell).Value)
c = 1
For L0 = Cells.SpecialCells(xlCellTypeLastCell).Row - 1 To 1 Step -1
tmp = Cells(L0, col).Value
If (Sgn(tmp) < s) Or (0 = tmp) Then Exit For
c = c + 1
Next
FindSignChangeOrZero = c
End If
End Function

If the bottom value is 0, this will return 0.

--
I will be the power surge -- Shock to the system
Electrified, amplified -- Shock to the system

On Feb 21, 11:22*am, "Auric__" wrote:
fzl2007 wrote:
I want to count the rows bottom up for a column value. As soon as the
sign is changed or the number is the value of a zero, counter finishes
counting. Ex,

Something like this, perhaps?

* FunctionFindSignChangeOrZero() As Long
* * 'Replace ActiveCell with whatever you need...
* * col = ActiveCell.Column
* * If ActiveCell.SpecialCells(xlCellTypeLastCell).Value < 0 Then
* * * s = Sgn(Cells.SpecialCells(xlCellTypeLastCell).Value)
* * * c = 1
* * * For L0 = Cells.SpecialCells(xlCellTypeLastCell).Row - 1 To 1 Step -1
* * * * tmp = Cells(L0, col).Value
* * * * If (Sgn(tmp) < s) Or (0 = tmp) Then Exit For
* * * * c = c + 1
* * * Next
* * *FindSignChangeOrZero= c
* * End If
* End Function

If the bottom value is 0, this will return 0.

--
I will be the power surge -- Shock to the system
Electrified, amplified -- Shock to the system

How do I apply this above code so that it will look up every other
column starting Column B on "Sheet1" and place the result on F6
"Sheet2", Column D on "Sheet1" and place result on F7 "Sheet2"; Column
F on "Sheet1" and place result on F8 on "Sheet2" and on ... until
column is empty?

Basically, The data is on "Sheet1" and results will show on "Sheet2".

Thanks again,
Faye

fzl2007 wrote:

On Feb 21, 11:22*am, "Auric__" wrote:
fzl2007 wrote:
I want to count the rows bottom up for a column value. As soon as the
sign is changed or the number is the value of a zero, counter finishes
counting. Ex,

Something like this, perhaps?
[snip]
If the bottom value is 0, this will return 0.

How do I apply this above code so that it will look up every other
column starting Column B on "Sheet1" and place the result on F6
"Sheet2", Column D on "Sheet1" and place result on F7 "Sheet2"; Column
F on "Sheet1" and place result on F8 on "Sheet2" and on ... until
column is empty?

Basically, The data is on "Sheet1" and results will show on "Sheet2".

Replace my previous function with this:

Sub FindSignChangeOrZero()
For c = 2 To Sheet1.Cells.SpecialCells(xlCellTypeLastCell).Colu mn Step 2
s = Sgn(Sheet1.Cells(1, c).End(xlDown).Value)
counter = 1
For L0 = Sheet1.Cells(1, c).End(xlDown).Row - 1 To 1 Step -1
tmp = Sheet1.Cells(L0, c).Value
If (Sgn(tmp) < s) Or (0 = tmp) Then Exit For
counter = counter + 1
Next
Sheet2.Cells((c / 2) + 5, 6).Value = counter
Next
End Sub

--
The best path is the hardest earned.

Auric__ wrote:

fzl2007 wrote:

On Feb 21, 11:22*am, "Auric__" wrote:
fzl2007 wrote:
I want to count the rows bottom up for a column value. As soon as the
sign is changed or the number is the value of a zero, counter finishes
counting. Ex,

Something like this, perhaps?
[snip]
If the bottom value is 0, this will return 0.

How do I apply this above code so that it will look up every other
column starting Column B on "Sheet1" and place the result on F6
"Sheet2", Column D on "Sheet1" and place result on F7 "Sheet2"; Column
F on "Sheet1" and place result on F8 on "Sheet2" and on ... until
column is empty?

Basically, The data is on "Sheet1" and results will show on "Sheet2".

Replace my previous function with this:

If you want to have the sub do as the function did regarding the bottom cell
= 0, use this instead:

Sub FindSignChangeOrZero()
For c = 2 To Sheet1.Cells.SpecialCells(xlCellTypeLastCell).Colu mn Step 2
s = Sgn(Sheet1.Cells(1, c).End(xlDown).Value)
If s < 0 Then
counter = 1
For L0 = Sheet1.Cells(1, c).End(xlDown).Row - 1 To 1 Step -1
tmp = Sheet1.Cells(L0, c).Value
If (Sgn(tmp) < s) Or (0 = tmp) Then Exit For
counter = counter + 1
Next
Sheet2.Cells((c / 2) + 5, 6).Value = counter
Else
Sheet2.Cells((c / 2) + 5, 6).Value = 0
End If
Next
End Sub

--
Okay, team, dogpile on the girl with the jetpack.