Last Value Greater than Zero in a column
I ended up creating a User Defined Function.
Dim rngPremium As Range
Dim wb As Workbook
Dim ws As Worksheet
Dim intLastPremiumYear As Integer 'last premium year
Const cintColNumber As Integer = 7 'column number - 7 is usual
Const cintColStartRow As Integer = 3 'start row of column - 3 is usual
Const cintColLastRow As Integer = 123 'start row of column - 123 is usual
Dim intCount As Integer
Sub GetLastPremium()
intCount = 0
Set wb = ThisWorkbook
Set ws = wb.Worksheets("GUI")
Set rngPremium = ws.Range(Cells(cintColNumber, cintColStartRow),
Cells(cintColNumber, cintColLastRow))
For i = cintColLastRow To cintColStartRow Step -1
intCount = intCount + 1
If Cells(i, cintColNumber) 0 Then
intLastPremiumYear = 121 - intCount + 1
ws.Range("LastPremiumYear") = intLastPremiumYear
Exit Sub
End If
Next i
Set rngPremium = Nothing
Set ws = Nothing
Set wb = Nothing
End Sub
"RagDyer" wrote:
Why not just add Isnumber to eliminate a text return:
=INDEX(A1:A25,MAX((A1:A250)*(ISNUMBER(A1:A25))*(R OW(A1:A25))))
?
--
Regards,
RD
---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Peo Sjoblom" wrote in message
...
A couple of ways
=LOOKUP(2,1/(A1:A100),A1:A10)
entered normally or
=INDEX(A1:A10,MAX((A1:A100)*(ROW(A1:A10))))
entered with ctrl + shift & enter
note that if there is a text string in the range at the right spot it will
be returned since text is greater than number according to Excel
--
Regards,
Peo Sjoblom
"Dkline" wrote in message
...
In a column of 121 numbers which can be positive or negative or zero, is
there a function to find the last positive value in the column?
Or do I need to build a UDF?
|