Thanks for the solution.
I appreciate the tip. I am learning VBA via the recorder and sometimes I
just dont go back and edit to a higher degrees of elegance.


"Rick Rothstein (MVP - VB)" wrote:

You do not need to select M1 in order to change make changes to it. When
ever you see code constructed like this...

Range("A1").Select
ActiveCell.<whatever

you can almost always do this instead...

Range("A1").<whatever

As to your question, these two lines can be used to replace your two line
attempt...

LastCell = Cells(Rows.Count, "M").End(xlUp).Row
Range("M1").Formula = "=IF(M" & LastCell & "=0,M" & (LastCell - 1) & _
", M" & LastCell & ")"

Rick


"AG" wrote in message
...
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.