CountA in VBA using activecell.row to set the range

[bell_pa]

Please help!

I am trying to enter the worksheet function CountA in a whole column to count how many blank cells exist in each of the rows. I have a for each loop which will work down the column but I can't figure out how to get the formula (or result) into each of the cells. I have nicked bits of code from all over the internet to try this so its quite possible that I am miles away from where I should be now

For MyRow = 1 To Range("a655360").End(xlUp).Row
MyRange = Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row, 22))

ActiveCell.Value = WorksheetFunction.CountA(Range(MyRange))
ActiveCell.Offset(1, 0).Select

Next MyRow

Please forgive my ignorance with this! And thanks in advance for any help!

 

[Comfy]

What column are you putting these formulas in?

R1C1 will allow you to enter these without a Loop.

So if you wanted the formula to start in Column 23 (W) in Row 2 and go down as far as your last row.

Sub Fill_Formula()
Dim rng As Range
Dim LastRow As Long


LastRow = Range("a65536").End(xlUp).Row


Set rng = Range(Cells(2, 23), Cells(LastRow, 23))


rng.FormulaR1C1 = "=SUM(RC[-22]:RC[-1])"


End Sub