copy formula to last row in excel using vba

[Bones55]

I would like this formula to autofill, it initially works but stops at the third line.

I think the issue is I only have headers at the moment and when stepping through the script it'll put the formula in E2, but seeing I don't have other inputs for E3 it stops at the third line.

Open to suggestions. Currently it works fine if E2 has the formula already

Sub addFormulas()

Lr = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
Range("E2").Formula = "=WORKDAY(EOMONTH(A2,-1),5,Table1[Holidays])"
Range("E2").AutoFill Range("E2:E" & Lr)

End Sub

 

[Michael M]

Try using

Lr = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
Range("E2:E" & lr).Formula = "=WORKDAY(EOMONTH(A2,-1),5,Table1[Holidays])"

 

[Bones55]

Excellent, works great thank you very much

 

[Michael M]

Glad to help