Sub Extraire()
Dim Titre, dt As Integer, WS As Worksheet, cel As Range, n As Integer
Set WS = ThisWorkbook.Worksheets("tableau")
Titre = Array("DATE", "LIBELLE FORMATION", "SALLE", "ORGANISME/FORMATEUR", "REFERENT ACADEMIE")
WS.Range("a1").CurrentRegion.ClearContents
WS.Range("a1").Resize(1, 5) = Titre
With Sheets("extraction")
For Each cel In .Range("A2:A" & .Range("A" & Rows.Count).End(xlUp).Row)
If IsDate(cel.Offset(, 6)) And IsDate(cel.Offset(, 7)) Then
If cel.Offset(, 6) <> cel.Offset(, 7) Then
n = DateDiff("d", cel.Offset(, 6).Value2, cel.Offset(, 7).Value2)
If n > 0 Then
dt = WS.Cells(Rows.Count, 1).End(xlUp).Row + 1
For n = 0 To n
WS.Range("A" & dt + n) = cel.Offset(, 6).Value2 + n
WS.Range("A" & dt + n).NumberFormat = "m/d/yyyy"
WS.Range("B" & dt + n) = cel.Offset(, 3)
WS.Range("C" & dt + n) = cel.Offset(, 1)
Next n
Else
If cel.Offset(, 8) Like "*Validée*" Then ' au lieu de cel.offset(,8)="Validée"'
dt = WS.Cells(Rows.Count, 1).End(xlUp).Row + 1
WS.Range("A" & dt) = cel.Offset(, 6).Value2
WS.Range("A" & dt).NumberFormat = "m/d/yyyy"
WS.Range("B" & dt) = cel.Offset(, 3)
WS.Range("C" & dt) = cel.Offset(, 1)
End If
End If
End If
End If
Next cel
End With
End Sub