Experimente:

ActiveCell.FormulaArray = "=SUM(1/COUNTIF($D$5:$D$25,$D$5:$D$25))"

EDIT: eu entendi que você queria inserir a fórmula via VBA, mas me parece que você quer é substituir a fórmula (o seu resultado) por VBA. De qualquer forma vou deixar, talvez alguém aproveite.

xlarruda,

Boa Noite!

Segue minha sugestão.

Sub ContarDistintos()
    Dim i As Long
    Dim j As Long
    Dim Contar As Long
    Dim UltimaLinha As Long
    Dim PrimeiraVezIgual As Boolean
    
    PrimeiraVezIgual = False
    UltimaLinha = Sheets("Plan1").Cells(Cells.Rows.Count, 4).End(xlUp).Row
    If UltimaLinha < 5 Then UltimaLinha = 5
    
    'Contar = 1
    For i = 5 To UltimaLinha
        For j = i + 1 To UltimaLinha
            If Range("D" & i).Value <> Range("D" & j).Value Then
                If j = UltimaLinha Then
                    Contar = Contar + 1
                End If
            Else
                If PrimeiraVezIgual = False Then
                    Contar = Contar + 1
                    PrimeiraVezIgual = True
                    Exit For
                Else
                    Exit For
                End If
            End If
        Next
    Next
    
    Range("D" & UltimaLinha + 1).Value = Contar
End Sub

O site não está aceitando anexar arquivos.

Resultado em 'F1':

Sub ContaÚnicos()
 [F1] = Evaluate("=SUM(1/COUNTIF($D$5:$D$25,$D$5:$D$25))")
End Sub

Pessoal, a todos muito obrigado pela ajuda, funcionaram perfeitamente.

Vlw!