Sub Test()
With Sheets("Résultat").Range("D1:D" & Sheets("Résultat").[A65536].End(xlUp).Row)
.FormulaR1C1 = "=IF(COUNTIF(RefMail!C1,RC1)*COUNTIF(Mail!C1,RC1), ""Attention : à vérifier"",IF(COUNTIF(RefMail!C1,RC1)*NOT(COUNTIF(Mail!R1,RC1)), ""Refusé"",IF(NOT(COUNTIF(RefMail!C1,RC1))*COUNTIF(Mail!C1,RC1),""Ok"",""?"")))"
.Value = .Value 'facultatif, supprime les formules
End With
End Sub