Sub Test()
Dim f As Worksheet, cell As Range, debut As String
Dim dicoExiste As New Dictionary, fichier As String, fichierExiste
Dim dicoCell As New Dictionary, clef
Set dicoExiste = CreateObject("scripting.dictionary")
dicoExiste.CompareMode = TextCompare
On Error Resume Next
For Each f In Worksheets
With f
.Select
Set cell = Cells.Find("[*]", LookIn:=xlFormulas)
If Not cell Is Nothing Then
debut = cell.Address
Do
fichier = Left(cell.Formula, InStr(cell.Formula, "]") - 1)
fichier = Replace(fichier, "[", "")
fichier = Replace(fichier, "=", "")
fichier = Replace(fichier, "'", "")
If Not dicoExiste.Exists(fichier) Then
fichierExiste = Dir(fichier)
dicoExiste.Add fichier, fichierExiste <> ""
End If
If Not dicoExiste(fichier) Then dicoCell.Add cell.Address, ""
Set cell = Cells.FindNext(cell)
Loop Until debut = cell.Address
If dicoCell.Count > 0 Then
For Each clef In dicoCell.Keys: ActiveSheet.Range(clef) = CVErr(xlErrRef): Next
End If
dicoCell.RemoveAll
End If
End With
Next f
End Sub