Row Count...

  • Thread starter Thread starter fzl2007
  • Start date Start date
F

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.
 
fzl2007 said:
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,
Click to expand...

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.
 
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.
Click to expand...

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 said:
fzl2007 said:
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,
Click to expand...

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

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".
Click to expand...

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__ said:
fzl2007 said:
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.
Click to expand...

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".
Click to expand...

Replace my previous function with this:
Click to expand...

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
 
Back
Top