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.

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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

post #4 worked. I will take a look at post 6 too. thanks again.