this sets "lastvalue" = the last value in M if it isn't 0 or the second to
last value in M if the last value is 0.

Sub selectvalue()

Dim lastvalue As Range

Range("M1").Select
With Range("M1")
If ActiveCell.End(xlDown).Value < 0 Then
Set lastvalue = ActiveCell.End(xlDown)
Else: Set lastvalue = ActiveCell.End(xlDown).Offset(-1, 0)
End If
End With

End Sub

--
-SA


"AG" wrote:

I need to define a formula in cell M1 similar to:
If the Last Cell in column M = 0, use the value from the row above in column
M, otherwise use the value of the Last Cell in column M. For example if the
last cell in Column M was in Row 100 the code would read

Range("M1").Select
ActiveCell.FormulaR1C1 = IF(M100 = 0, M99, M100)

My problem is that the last cell in Column M changes between sheets that
would use the macro.
Is there a way to reference or define these in a formula?
Please note columns N and O also contain data so a reference to the current
region would not work.