Macro to find the first blank cell in a column and add formula till lastused row

Apologies as I am pretty sure this was asked before , but I couldn't find the correct thread. I am new to VBA so could be that I am not searching properly.

I have a worksheet that uses column A to DD. I have a task(outside of excel) that find the last used row in this worksheet and paste data daily, except for the last 2 columns. Last two rows have formula which is noted below. I am looking for a macro that can autofill the blank cells in each column with below formula. I prefer the macro to paste the formula only from blank cell down, rather than from cell CZ2 and CY2 onwards.

Column CZ formula is INDEX(Categories,MATCH(TRUE,ISNUMBER(SEARCH(Keywords,I2)),0))
Column CY formula is =IF(LEFT(S2,3)="rem","Ignorefromanalysis",W2)

There are no blank cells in Column A so i can use column A to find the total range for pasting formula Down.

Below is an example ) that could work, but it copies the formula from cell 2 -down, rather than from the first empty cell-down.
Cells(5,1).Select
Selection.End(xlDown).Select
lastRow = ActiveCell.Row
Range(Cells(2,3),Cells(lastRow,3)) = .....my formula

Thanks for the help.

Sub FormulaToRowLast()
Dim lr As Long
lr = Cells(Rows.Count, "CY").End(xlUp).Row
    Range("CY" & lr & ":CZ" & Cells(Rows.Count, "A").End(xlUp).Row).FillDown
End Sub

Check all references and change if/where required.

thank you for your reply. It worked like a charm.

one more question, How would I change the range , if I have to fill down the formulas in column CP, CY and DD. thanks

Try this.

It assumes that Columns CP (94), CY (102) and DD (108) all have the same last row.
It uses Column A as a benchmark for maximun rows.

Sub FillDown_To_Last_Row()
Dim lr1 As Long, lr2 As Long, colArr, i As Long

lr1 = Cells(Rows.Count, 94).End(xlUp).Row
lr2 = Cells(Rows.Count, 1).End(xlUp).Row
colArr = Array(94, 103, 108)    '<---- numericals for Columns CP, CY and DD

Application.ScreenUpdating = False
    For i = LBound(colArr) To UBound(colArr)
        Range(Cells(lr1, colArr(i)), Cells(lr2, colArr(i))).FillDown
    Next i
Application.ScreenUpdating = True
End Sub

Thank You!

The code in Post #4 is easy to expand if needed and so is this one.
Just a slightly different way of attacking the problem.
Have fun.

Sub FillDown_To_Last_Row_2()
Dim lr1 As Long, lr2 As Long
lr1 = Cells(Rows.Count, 94).End(xlUp).Row
lr2 = Cells(Rows.Count, 1).End(xlUp).Row

Application.ScreenUpdating = False
    Application.Union(Range(Cells(lr1, 94), Cells(lr2, 94)), Range(Cells(lr1, 103), Cells(lr2, 103)), Range(Cells(lr1, 108), Cells(lr2, 105))).FillDown
Application.ScreenUpdating = True

End Sub

Good luck and thanks for letting us know that it all worked to your needs.