Sub test_Evaluate()
Dim lastrow&
With Range("A1", Range("A" & Rows.Count).End(xlUp))
lastrow = .Count
'Résultat en colonne C
.Offset(, 2) = "=IF(ISBLANK(A1),"""",COUNTIF(A$1:A1,A1))"
'Résultat en colonne D
.Offset(, 3) = Evaluate("=IF(ISBLANK(A1:A" & lastrow & "),"""",COUNTIF(OFFSET(A1,,,ROW(A1:A" & lastrow & ")),A1:A" & lastrow & "))")
End With
End Sub