Reference/define last cell
 

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.

Reference/define last cell
 

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.

Reference/define last cell
 

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.

Reference/define last cell
 

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.

Reference/define last cell
 

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.