1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37
| Sub formule()
With Sheets("DOUBLONS")
derlg = Range("A" & Rows.Count).End(xlUp).Row
Range("B2").FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC[-1],'EXP DIF'!C[29],1,FALSE)),0,1)"
Range("B2").AutoFill Destination:=Range("B2:B" & derlg), Type:=xlFillDefault
Range("C2").FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-2],AAR!C[28],1,FALSE)),0,1)"
Range("C2").AutoFill Destination:=Range("C2:C" & derlg), Type:=xlFillDefault
Range("D2").FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC[-3],AAR35!C[27],1,FALSE)),0,1)"
Range("D2").AutoFill Destination:=Range("D2:D" & derlg), Type:=xlFillDefault
Range("E2").FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-4],RST!C[26],1,FALSE)),0,1)"
Range("E2").AutoFill Destination:=Range("E2:E" & derlg), Type:=xlFillDefault
Range("F2").FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-5],PCH!C[25],1,FALSE)),0,1)"
Range("F2").AutoFill Destination:=Range("F2:F" & derlg), Type:=xlFillDefault
Range("G2").FormulaR1C1 = "=SUM(RC[-5]:RC[-1])"
Range("G2").AutoFill Destination:=Range("G2:G" & derlg), Type:=xlFillDefault
Range("B2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.End(xlUp).Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("G1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=7, Criteria1:="1"
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.delete Shift:=xlUp
Selection.AutoFilter
End With
End Sub |
Partager