Tableau récapitulatif

ub Test_Indispo()
'
' Test_Indispo Macro
'
' Touche de raccourci du clavier: Ctrl+a
'
    Application.Run "PERSONAL.XLSB!Test_Indispo"
    Range("AI6").Select
    Application.Run "PERSONAL.XLSB!Test_Indispo"
    Option Explicit

' constantes à modifier selon ta configuration
' Feuille Source
Const FS = "Données_corrigées"
Const lidebFS = 2
Const comacFS = "AF"
Const coerrFS = "AJ"
' Feuille But
Const FB = "Listing-machine"
Const celdebFB = "A147"
' message recherché
Const s = "Machine non répertorié"

Public Sub OK()
Dim liFS As Long, lifinFS As Long
Dim dico As Object, cle As String, cles, nbcles As Long
' dictionnaire des machines en erreur
Set dico = CreateObject("scripting.dictionary")
With Sheets(FS)
  lifinFS = .Range(comacFS & Rows.Count).End(xlUp).Row
  For liFS = lidebFS To lifinFS
    If s = .Range(coerrFS & liFS).Value Then
      cle = .Range(comacFS & liFS).Value
      If (Not dico.exists(cle)) Then dico.Add cle, 1
    End If
  Next liFS
End With
nbcles = dico.Count
cles = dico.keys
' resultat
With Sheets(FB)
  .Columns(Range(celdebFB).Column).ClearContents
  .Range(celdebFB).Offset(-1, 0).Value = s
  .Range(celdebFB).Resize(nbcles, 1) = Application.Transpose(cles)
End With
End Sub

End Sub

Const s = "Machine non répertorié" => j'ai corriger la faute sur le fichier excel

 
Call OK
Dim liFS As Long, lifinFS As Long