Hi plk,

Try this:

Option Explicit
Sub Macro1()

    'http://www.excelforum.com/excel-general/838546-using-formula-in-vba.html?p=2828138

    Dim lngEndRow As Long
    
    lngEndRow = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row

    Sheets("Sheet2").Range("B21").Formula = "=SUMPRODUCT((A1:A" & lngEndRow & "<>"""")/COUNTIF(A1:A" & lngEndRow & ",A1:A" & lngEndRow & "&""""))"
    Sheets("Sheet1").Range("D10").Formula = "=Sheet2!B21"
    
End Sub
Note there are nine unique entries, not eight as the data starts from row one, not row two as your formula stated. I've also included code to find the last row from column A.

HTH

Robert