Bonjour à tous,
Une autre solution en VBA. Il faut indexer le tableau car il peut y avoir plusieurs retours successifs :
Option Explicit
Sub CalculerLEcartEntreDates()
Dim I As Integer, Index As Integer
Dim AireReference As Range, AireVente As Range, AirePrix As Range, AireNbJours As Range, AireIndexation As Range
Dim ValeurDateRetour As Date, DateEncours As Date
Dim ReferenceEnCours As String, IndexEnCours As String
Application.ScreenUpdating = False
With Sheets("Feuil1").ListObjects("TableDesVentes")
.Sort.SortFields.Clear
.Sort.SortFields.Add2 Key:=Range("TableDesVentes[Références]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
.Sort.SortFields.Add2 Key:=Range("TableDesVentes[Vente_date]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With .Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
Set AireReference = Range("TableDesVentes[Références]")
Set AireVente = Range("TableDesVentes[Vente_date]")
Set AirePrix = Range("TableDesVentes[Vente_prix_vendu]")
Set AireNbJours = Range("TableDesVentes[Nb jours]")
Set AireIndexation = Range("TableDesVentes[Indexation]")
Index = 0
AireIndexation.ClearContents
For I = AireIndexation.Count To 1 Step -1
If AirePrix(I) < 0 Then
Index = Index + 1
ReferenceEnCours = AireReference(I)
IndexEnCours = ReferenceEnCours & "-" & Format(Index, "000")
AireIndexation(I) = IndexEnCours
End If
If AireReference(I) = ReferenceEnCours Then
AireIndexation(I) = IndexEnCours
End If
Next I
For I = 1 To AireIndexation.Count
With AireIndexation(I)
ValeurDateRetour = DateRetour(AireReference(I))
DateEncours = CDate(AireVente(I))
If DateDiff("d", DateEncours, ValeurDateRetour) > 0 Then
AireNbJours(I) = DateDiff("d", DateEncours, ValeurDateRetour)
End If
End With
Next I
AireNbJours.ClearContents
For I = 1 To AireIndexation.Count
With AireIndexation(I)
ValeurDateRetour = DateRetour(.Value)
DateEncours = CDate(AireVente(I))
If DateDiff("d", DateEncours, ValeurDateRetour) > 0 Then
AireNbJours(I) = DateDiff("d", DateEncours, ValeurDateRetour)
End If
End With
Next I
Set AireReference = Nothing: Set AireVente = Nothing: Set AireNbJours = Nothing
Application.ScreenUpdating = True
End Sub
Function DateRetour(ByVal NumeroIndex As String) As Date
Dim I As Integer
Dim AireIndexation As Range, AireVente As Range, AirePrix As Range
Set AireIndexation = Range("TableDesVentes[Indexation]")
Set AireVente = Range("TableDesVentes[Vente_date]")
Set AirePrix = Range("TableDesVentes[Vente_prix_vendu]")
For I = 1 To AireIndexation.Count
If CStr(AireIndexation(I)) = NumeroIndex And AirePrix(I) < 0 Then
DateRetour = CDate(AireVente(I))
Exit For
End If
Next I
Set AireIndexation = Nothing: Set AireVente = Nothing: Set AirePrix = Nothing
End Function