Sub Nettoyage2003test()
Application.ScreenUpdating = False
Dim li As Long
li = Range("B" & Application.Rows.Count).End(xlUp).Row + 1
Range("m5:m" & li - 1).FormulaLocal = "=SI(ESTERREUR(RECHERCHEV(E5;'données'!$A:$B;2;FAUX));SOMMEPROD(DECALER(B5;1;11;SI(NB.SI(B6:$B$" & li & ";B5)>0;EQUIV(B5;B6:$B$" & li & ";0)-1;NBVAL(B6:$B$" & li & ")))*(DECALER(B5;1;;SI(NB.SI(B6:$B$" & li & ";B5)>0;EQUIV(B5;B6:$B$" & li & ";0)-1;NBVAL(B6:$B$" & li & ")))=CAR(CODE(B5)+1))*(DECALER(B5;1;5;SI(NB.SI(B6:$B$" & li & ";B5)>0;EQUIV(B5;B6:$B$" & li & ";0)-1;NBVAL(B6:$B$" & li & ")))));RECHERCHEV(E5;'données'!$A:$B;2;FAUX))"
Range("N2") = li
Range("M2").FormulaLocal = "=SOMMEPROD((DECALER(B4;1;11;SI(NB.SI(B5:$B$" & li & ";B4)>0;EQUIV(B4;B5:$B$" & li & ";0)-1;NBVAL(B5:$B$" & li & " )))*(DECALER(B4;1;;SI(NB.SI(B5:$B$" & li & ";B4)>0;EQUIV(B4;B5:$B$" & li & ";0)-1;NBVAL(B5:$B$" & li & ")))=CAR(CODE(B4)+1))*(DECALER(B4;1;5;SI(NB.SI(B5:$B$" & li & ";B4)>0;EQUIV(B4;B5:$B$" & li & ";0)-1;NBVAL(B5:$B$" & li & "))))))"
With Range("B4:B" & li)
.Replace what:="Niv.", replacement:="A"
.Replace what:="Niv", replacement:="A"
.Replace what:=".1", replacement:="B"
.Replace what:="..2", replacement:="C"
.Replace what:="...3", replacement:="D"
.Replace what:="....4", replacement:="E"
.Replace what:=" ", replacement:=""
.HorizontalAlignment = xlCenter
End With
With Range("E4:E" & li)
.Replace what:="wm.", replacement:=""
.Replace what:=" ", replacement:=""
.Replace what:="wp.", replacement:=""
.HorizontalAlignment = xlCenter
End With
Range("M4:M" & li).NumberFormat = _
"_-* #,##0.00 _-;-* #,##0.00 _-;_-* ""-""?? _-;_-@_-"
Range("B5").Activate
With Sheets("Nomenclature").Range("B5:M" & li - 1)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=$B5<$B6"
.FormatConditions(1).Font.Bold = True
End With
Application.ScreenUpdating = True
End Sub