Excel formula error on VBA

Question

I am trying to put the following code in VBA. I ideally want to apply the formula to column H when column A is empty. Any help would be greatly appreciated. endPosition is a variable that contains the value of row #, where column A is empty.

ActiveCell.Formula = "=SUM(N(FREQUENCY(R[endPosition]C[-1]:R[endPosition]C[-1],R[endPosition]C[-1]:R[endPosition]C[-1])>0))" 

This formula is applied at the end when the loop sees an empty cell:

  Range("A1").Select beginPosition = 2 'Start from row number 2 Do While IsEmpty(ActiveCell.Offset(1, 0)) = False ActiveCell.Offset(1, 0).Select endPosition = ActiveCell.Row Loop endPosition = endPosition + 1 Range("H15").Select ActiveCell.Formula = "=SUM(N(FREQUENCY(R[endPosition]C[-1]:R[endPosition]C[-1],R[endPosition]C[-1]:R[endPosition]C[-1])>0))" 

Answer 1

Assuming the formula is correct, if endPosition is a variable, you should not include it in the line:

 ActiveCell.FormulaR1C1 = "=SUM(N(FREQUENCY(R[" & endPosition & _ "]C[-1]:R[" & endPosition & "]C[-1],R[" & endPosition & _ "]C[-1]:R[" & endPosition & "]C[-1])>0))"