fzl2007 said:
The formula will 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. [....]
If there is no sign change or value of zero, then
count the total rows (with value).
What if the last value of a column is zero?
In that case, I assume you want the index from the bottom of the first
non-zero value.
fzl2007 said:
the numbers start in row 2 (of all columns). And they
are contiguous through the last row; no gaps in the data.
Ideally. I have data on "Sheet1" and want results on
"Sheet2". I want to 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?
Good description.
The first issue is: what the VBA function interface should be? There are
trade-offs.
-----
The "obvious" interface is (entered into F6 and copied down):
=lastSignChange(OFFSET(Sheet1!$B:$B,0,2*ROWS($F$6:F6)-2))
The problem with that is: OFFSET is a volatile function. Consequently,
__all__ of the formulas are recalculated any time __any__ cell in __any__
worksheet is modified. That can cause excessive delays when editing, saving
the file and (I believe) opening the file, if there are a lot of such
formulas.
-----
An "obvious" alternative is to let the VBA function compute the column
offset, to wit:
=lastSignChange(Sheet1!$B:$B,ROWS($F$6:F6))
The problem with that is: the formula is recalculated only when column B is
modified, not when any other columns (D, F, etc) are modified. Remedy: be
sure to press ctrl+alt+F9 after modifying any of the "interesting" columns,
namely B, D, F etc.
Is that acceptable?
If so, you might as well use a VBA macro instead of a VBA function. See
below. The macro can be assigned to a "button".
-----
A "compromise" alternative is:
=lastSignChange(Sheet1!$B:$Z,ROWS($F$6:F6))
This assumes that column Z is the last possible column. Adjust that for
your situation.
__All__ of the formulas are recalculated whenever __any__ cell in columns B
through Z is modified. But at least that is only when any cell in those
columns is modified, not when other cells are modified, as is the case with
volatile functions like OFFSET.
Is that acceptable?
------
The following is a macro implementation. It is relatively to easy to adapt
to a VBA function, if you prefer, subject to answers to the questions above.
Option Explicit
Sub doit()
Dim nCol As Long, r As Long, c As Long, n As Long
Dim k As Long, s As Long
Dim dataSh As Worksheet
Dim resRng As Range, firstData As Range
Dim oldCalc, v
' customize
' dataSh = source of data starting Cells(firstRow,firstCol)
' resRng = first cell of result
Const firstCol As Long = 2 ' 2 = column B
Const firstRow As Long = 2
Set dataSh = Sheets("sheet1")
Set resRng = Sheets("sheet2").Range("f6")
With Application
oldCalc = .Calculation
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
End With
' process firstCol through last column of dataSh
nCol = dataSh.UsedRange.SpecialCells(xlCellTypeLastCell).Column
If nCol < firstCol Then nCol = firstCol
ReDim res(1 To nCol / 2, 1 To 1) As Long
r = 0
For c = firstCol To nCol Step 2
Set firstData = dataSh.Cells(firstRow, c)
If IsEmpty(firstData) Then Exit For
n = 1
If Not IsEmpty(firstData.Offset(1)) Then
v = Range(firstData, firstData.End(xlDown))
k = UBound(v, 1)
s = Sgn(v(k, 1))
For k = k - 1 To 1 Step -1
If Sgn(v(k, 1)) <> s Then Exit For
n = n + 1
Next
End If
r = r + 1
res(r, 1) = n
Next
' write results to resRng
' clear previous results below resRng
If r > 0 Then
' normal case: non-empty data in dataSh
resRng.Resize(r) = res
Set resRng = resRng.Offset(r)
If Not IsEmpty(resRng) Then
Range(resRng, resRng.Offset(-1).End(xlDown)).ClearContents
End If
ElseIf Not IsEmpty(resRng) Then
' special case: empty data in dataSh
If IsEmpty(resRng.Offset(1)) Then
resRng.ClearContents
Else
Range(resRng, resRng.End(xlDown)).ClearContents
End If
End If
With Application
.EnableEvents = True
.Calculation = oldCalc
.ScreenUpdating = True
End With
End Sub
