FillDown method of range class failed

[reubanrao93]

Hi Guys,

I'm trying to run this code but currently am stuck with "Run-Time-error '1004" FillDown method of range class failed" during debug. Is there something I'm missing here?

Sub PastFormula()
Dim LRw As Long
Sheets("Sheet1").Range("S:S").Select
ActiveCell.Formula = "=MID(L2,(LEN(L2)-(LEN(LEFT(L2,LEN(L2)-SEARCH(""ENO="",L2)))-4)),((LEN(LEFT(L2,LEN(L2)-SEARCH(""ENO="",L2)))-4)-(LEN(LEFT(L2,LEN(L2)-SEARCH("",SUB"",L2))))))"
LRw = Range("L" & Rows.Count).End(xlUp).Row

Range(ActiveCell, Cells(LRw, ActiveCell.Column)).FillDown

End Sub

 

[Peter_SSs]

When you get the error, click Debug and hover over the variable name LRw and see what the pop-up says

 

[Tegg]

Hi

This code won't run if column L is empty
You also needed to add '.address. in Range()
Hopefully, this code should help

Sub PastFormula()
Dim LRw As Long
Sheets("Sheet1").Range("S:S").Select
ActiveCell.Formula = "=MID(L2,(LEN(L2)-(LEN(LEFT(L2,LEN(L2)-SEARCH(""ENO="",L2)))-4)),((LEN(LEFT(L2,LEN(L2)-SEARCH(""ENO="",L2)))-4)-(LEN(LEFT(L2,LEN(L2)-SEARCH("",SUB"",L2))))))"
LRw = Range("L" & Rows.Count).End(xlUp).Row

Range(ActiveCell.Address, Cells(LRw, ActiveCell.Column)).FillDown

End Sub

 

[Fluff]

You also needed to add '.address. in Range()

Actually, you don't need to do that.