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 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57
| Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
'mise en place des formules sur la feuille GLOBAL
Sheets("GLOBAL").Range("P2").FormulaR1C1 = _
"=IFERROR(IF(AND(RC[1]=TODAY(),RC[3]=""""),""1e relance"",IF(AND(RC[2]=TODAY(),RC[4]=""""),""2e relance"","""")),"""")"
Sheets("GLOBAL").Range("S2").FormulaR1C1 = _
"=IFERROR(IF(RC[-2]=TODAY(),IF(VLOOKUP(RC[3],BDD!C[-18]:C[-16],3,FALSE)=""OK"",RC[-2],""""),""""),"""")"
Sheets("GLOBAL").Range("T2").FormulaR1C1 = _
"=IFERROR(IF(RC[-2]=TODAY(),IF(VLOOKUP(RC[2],BDD!C[-19]:C[-17],3,FALSE)=""OK"",RC[-2],""""),""""),"""")"
Sheets("GLOBAL").Range("U2").FormulaR1C1 = _
"=IFERROR(IF(VLOOKUP(RC[1],BDD!C[-20]:C[-18],3,FALSE)=""OK"",VLOOKUP(RC[1],BDD!C[-20]:C[-17],4,FALSE),""""),"""")"
'tire les formules vers le bas
Sheets("GLOBAL").Range("P2").AutoFill Destination:=Sheets("GLOBAL").Range("P2:P" & Application.WorksheetFunction.CountA(Sheets("GLOBAL").Range("A:A")))
Sheets("GLOBAL").Range("S2:U2").AutoFill Destination:=Sheets("GLOBAL").Range("S2:U" & Application.WorksheetFunction.CountA(Sheets("GLOBAL").Range("A:A")))
Sheets("GLOBAL").Calculate
'copie colle en valeur les résultats des formules
Sheets("GLOBAL").Range(Sheets("GLOBAL").Rows("2:2"), Sheets("GLOBAL").Rows("2:2").End(xlDown)).Copy
Sheets("GLOBAL").Range(Sheets("GLOBAL").Rows("2:2"), Sheets("GLOBAL").Rows("2:2").End(xlDown)).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'mise en place des formules sur la feuille RECAP
Sheets("RECAP").Range("D9").FormulaR1C1 = _
"=COUNTIFS(GLOBAL!C[2],RECAP!RC[-1],GLOBAL!C[12],""1e relance"")+COUNTIFS(GLOBAL!C[2],RECAP!RC[-1],GLOBAL!C[12],""2e relance"")"
Sheets("RECAP").Range("G9").FormulaR1C1 = _
"=COUNTIFS(GLOBAL!C[-1],RECAP!RC[-1],GLOBAL!C[9],""1e relance"",GLOBAL!C[8],""TEL"")+COUNTIFS(GLOBAL!C[-1],RECAP!RC[-1],GLOBAL!C[9],""2e relance"",GLOBAL!C[8],""TEL"")"
Sheets("RECAP").Range("J9").FormulaR1C1 = _
"=COUNTIFS(GLOBAL!C[-4],RECAP!RC[-1],GLOBAL!C[6],""1e relance"",GLOBAL!C[5],""FAX"")+COUNTIFS(GLOBAL!C[-4],RECAP!RC[-1],GLOBAL!C[6],""2e relance"",GLOBAL!C[5],""FAX"")"
'tire les formules vers le bas
Sheets("RECAP").Range("D9").AutoFill Destination:=Sheets("RECAP").Range("D9:D" & Application.WorksheetFunction.CountA(Sheets("TEMP").Range("D:D")) + 7)
Sheets("RECAP").Range("J9").AutoFill Destination:=Sheets("RECAP").Range("J9:J" & Application.WorksheetFunction.CountA(Sheets("TEMP").Range("F:F")) + 7)
Sheets("RECAP").Range("G9").AutoFill Destination:=Sheets("RECAP").Range("G9:G" & Application.WorksheetFunction.CountA(Sheets("TEMP").Range("H:H")) + 7)
Sheets("RECAP").Calculate
'copie colle en valeur les résultats des formules
Sheets("RECAP").Range(Sheets("RECAP").Rows("9:9"), Sheets("RECAP").Rows("9:9").End(xlDown)).Copy
Sheets("RECAP").Range(Sheets("RECAP").Rows("9:9"), Sheets("RECAP").Rows("9:9").End(xlDown)).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
'remise en place des formules
Sheets("GLOBAL").Range("W2").FormulaR1C1 = "=IF(LEN(MENU!R7C4)*(RC[-1]=MENU!R7C4)>0,ROW(),"""")"
'retire les formules vers le bas
Sheets("GLOBAL").Range("W2").AutoFill Destination:=Sheets("GLOBAL").Range("W2:W" & Application.WorksheetFunction.CountA(Sheets("GLOBAL").Range("A:A")))
Sheets("GLOBAL").Calculate
End Sub |
Partager