Private Sub Worksheet_Activate()
Dim francais As Range, a As Range, i, s, j
Set francais = [E2:E107]
Application.ScreenUpdating = False
Sheets("Données").[A:A].Copy [A1] 'copier-coller
For Each a In [A:A].SpecialCells(xlCellTypeConstants).Areas
If a.Count = 4 Then
For i = 3 To 4
a.Cells(i, 2) = "A" 'repère anglais en colonne B
s = Split(a.Cells(i).Text)
For j = 0 To UBound(s)
If Application.CountIf(francais, s(j)) Then a.Cells(i, 2) = "F": Exit For 'repère français en colonne B
Next j
Next i
If a.Cells(3, 2) = a.Cells(4, 2) Then
a.Cells(3) = a.Cells(3).Text & " " & a.Cells(4).Text 'concaténation
a.Cells(4).Delete xlUp
End If
a.Cells(3, 2).Resize(2) = "" 'effacement des repères en colonne B
ElseIf a.Count = 5 Then
For i = 3 To 5
a.Cells(i, 2) = "A" 'repère anglais en colonne B
s = Split(a.Cells(i).Text)
For j = 0 To UBound(s)
If Application.CountIf(francais, s(j)) Then a.Cells(i, 2) = "F": Exit For 'repère français en colonne B
Next j
Next i
If a.Cells(3, 2) = a.Cells(4, 2) Then
a.Cells(3) = a.Cells(3).Text & " " & a.Cells(4).Text 'concaténation
a.Cells(4).Delete xlUp
ElseIf a.Cells(4, 2) = a.Cells(5, 2) Then
a.Cells(4) = a.Cells(4).Text & " " & a.Cells(5).Text 'concaténation
a.Cells(5).Delete xlUp
End If
a.Cells(3, 2).Resize(3) = "" 'effacement des repères en colonne B
ElseIf a.Count = 6 Then
a.Cells(3) = a.Cells(3).Text & " " & a.Cells(4).Text 'concaténation
a.Cells(5) = a.Cells(5).Text & " " & a.Cells(6).Text 'concaténation
a.Cells(6).Delete xlUp
a.Cells(4).Delete xlUp
End If
Next a
Columns(1).AutoFit
End Sub