Private Sub Worksheet_Calculate()
Dim colman1%, colman2%, colman3%, sel As Range, t, d1 As Object, d2 As Object, d3 As Object, i&
colman1 = 6 'n° de colonne 1ère entrée manuelle, à adapter
colman2 = 7 'n° de colonne 2ème entrée manuelle, à adapter
colman3 = 8 'n° de colonne 3ème entrée manuelle, à adapter
Application.ScreenUpdating = False 'fige l'écran
Application.EnableEvents = False 'désactive les évènements
On Error Resume Next
Set sel = Selection 'mémorise
With [A1].CurrentRegion
Application.Undo
t = .Resize(, Application.Max(colman1, colman2, colman3)) 'tableau précédent
Application.Undo
Set d1 = CreateObject("Scripting.Dictionary")
Set d2 = CreateObject("Scripting.Dictionary")
Set d3 = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(t)
d1(t(i, 1)) = t(i, colman1) 'mémorise
d2(t(i, 1)) = t(i, colman2) 'mémorise
d3(t(i, 1)) = t(i, colman3) 'mémorise
Next
t = .Resize(, Application.Max(colman1, colman2, colman3)) 'dernier tableau
For i = 1 To UBound(t)
t(i, colman1) = d1(t(i, 1))
t(i, colman2) = d2(t(i, 1))
t(i, colman3) = d3(t(i, 1))
Next
.Columns(colman1) = Application.Index(t, , colman1) 'restitution
.Columns(colman2) = Application.Index(t, , colman2) 'restitution
.Columns(colman3) = Application.Index(t, , colman3) 'restitution
End With
sel.Select
Application.EnableEvents = True 'réactive les évènements
Application.ScreenUpdating = True 'rafraîchit l'écran
End Sub