Row Count...

[fzl2007]

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.

 

[Auric__]

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.

 

[fzl2007]

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.

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

 

[Auric__]

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).Column 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

 

[Auric__]

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).Column 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