Reference/define last cell

[AG]

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.

 

[StumpedAgain]

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

 

[Rick Rothstein \(MVP - VB\)]

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]

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

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

 

[AG]

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