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.

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.

Thanks for the info.
BTW, I like the username!


"StumpedAgain" wrote:

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.

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.

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.