Using Last Row with a Formula

[Guzzlr]

Hello All...
Having a little trouble here
I have the following codes:

Dim Lst As Long

Lst = Range("A:A").Cells.Find("*", _
SearchOrder:=xlByRows, searchdirection:=xlPrevious).Row

And

Range("M2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-8]=""No Tag Found-"","""",RC[-8])"
Selection.AutoFill Destination:=Range("M2" & Lst), Type:=xlFillDefault
Range("M2" & Lst).Select
Range("A1").Select

and it's not working...

If I use:

    Range("M2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-8]=""No Tag Found-"","""",RC[-8])"
    Selection.AutoFill Destination:=Range("M2:M20"), Type:=xlFillDefault
    Range("M2:M20").Select
    Range("A1").Select

It does work, however I wish the formula to go to the last row and not only to Row 20...
What am I doing wrong?
Thanks

PS what I'm trying to accomplish, is in column M, to only display what is in column E that only has a mixture of Text and Numbers like: LB667

 

[BigBeachBananas]

I believe you're trying to auto-fill your column M2 to the last row of column A. If so try:

    Range("M2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-8]=""No Tag Found-"","""",RC[-8])"
    Selection.AutoFill Destination:=Range("M2:M" & Range("A" & Rows.count).End(xlUp).Row)
    Range(Selection, Selection.End(xlDown)).Select
    Range("A1").Select

 

[Guzzlr]

Thank you...it is working!

 

[Peter_SSs]

Instead of putting one formula in and then filling down, you could just put them all in at once with a single line:

Range("M2:M" & Range("A" & Rows.Count).End(xlUp).Row).FormulaR1C1 = "=IF(RC[-8]=""No Tag Found-"","""",RC[-8])"