Sub Macro_Tout_en_Un_oupresque()
Dim Plage As Range, filtre As Range, Cel As Range, Crit, r As Range, derl As Long
Application.ScreenUpdating = False
'tri et suppression ligne vide
Columns("B:J").Sort Key1:=Range("J1"), Order1:=xlAscending, Header:=xlGuess
Columns("J:J").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Columns("B:J").Sort Key1:=Range("B1"), Order1:=xlAscending, Key2:=Range("D1"), Order2:=xlAscending, Header:=xlGuess
'ajout valeurs
ActiveSheet.AutoFilterMode = False
Set Plage = ActiveSheet.Range(Cells(1, "J"), ActiveSheet.Cells(Rows.Count, "J").End(xlUp))
Crit = InputBox("Saisir votre valeur", "Choix", 40)
With Plage
.AutoFilter Field:=1, Criteria1:=">" & Crit
Set filtre = .SpecialCells(xlCellTypeVisible)
For Each Cel In filtre
If Cel.Row > 1 Then
Cel.Offset(1, 1) = Cel.Offset(1)
End If
Next
End With
ActiveSheet.AutoFilterMode = False
'suppression ligne
derl = ActiveSheet.Cells(Rows.Count, "J").End(xlUp).Row
With Range("L1:L" & derl)
.FormulaR1C1 = "=IF(OR(AND(RC10>0,RC10=RC11),RC10>40),""X"",1)"
.SpecialCells(xlCellTypeFormulas, 1).EntireRow.Delete
.Clear
End With
'calculs
For Each c In Columns("K:K").SpecialCells(xlCellTypeConstants, 1)
c.Offset(, 2) = Application.VLookup(c, [tablo], 3, 0)
c.Offset(, 4) = Application.VLookup(c, [tablo], 2, 0)
c.Offset(, 6) = Cells(c.Row, "G") * Cells(c.Row, "O")
c.Offset(, 8) = Cells(c.Row, "Q") - Cells(c.Row, "M")
Next
End Sub