cjoint

Publicité


Publicité

Format du document : text/plain

Prévisualisation

Sub FOR_AGR()
Dim Calc
Application.ScreenUpdating = False
Calc = Application.Calculation
Application.Calculation = xlCalculationManual

'SELECTIONNER FEUILLE BA FO

Sheets("BA FO").Select
'Range("A1").Select

' BA FO A2

Range("A2:A400").FormulaArray = _
"=IF(ROWS(R2:R)<=COUNTA(FOURNI),INDEX(FOURNI,SMALL(IF(FOURNI<>"""",ROW(INDIRECT(""1:""&ROWS(FOURNI)))),ROWS(R2:R)),MOD(SMALL(IF(FOURNI<>"""",ROW(INDIRECT(""1:""&ROWS(FOURNI)))*10^5+COLUMN(FOURNI)),ROWS(R2:R)),10^5)-COLUMN(FOURNI)+1),"""")"


' BA FO B2

Range("B2:B400").FormulaArray = _
"=IF(INDEX(C1,MIN(IF(COMPILFOUR<>"""",IF(COUNTIF(R1C:R[-1]C,COMPILFOUR)=0,ROW(COMPILFOUR),ROWS(COMPILFOUR)+ROW(COMPILFOUR)))))=0,"""",INDEX(C1,MIN(IF(COMPILFOUR<>"""",IF(COUNTIF(R1C:R[-1]C,COMPILFOUR)=0,ROW(COMPILFOUR),ROWS(COMPILFOUR)+ROW(COMPILFOUR))))))"
' SELCTIONNER FEUILLE BA FA

Sheets("BA FA").Select


'BA FA A2

Range("A2:A400").FormulaArray = _
"=IF(ROWS(R2:R)<=COUNTA(FAMILLES),INDEX(FAMILLES,SMALL(IF(FAMILLES<>"""",ROW(INDIRECT(""1:""&ROWS(FAMILLES)))),ROWS(R2:R)),MOD(SMALL(IF(FAMILLES<>"""",ROW(INDIRECT(""1:""&ROWS(FAMILLES)))*10^5+COLUMN(FAMILLES)),ROWS(R2:R)),10^5)-COLUMN(FAMILLES)+1),"""")"

'BA FA B2

Range("B2:B400").FormulaArray = _
"=IF(INDEX(C1,MIN(IF(COMPILFAM<>"""",IF(COUNTIF(R1C:R[-1]C,COMPILFAM)=0,ROW(COMPILFAM),ROWS(COMPILFAM)+ROW(COMPILFAM)))))=0,"""",INDEX(C1,MIN(IF(COMPILFAM<>"""",IF(COUNTIF(R1C:R[-1]C,COMPILFAM)=0,ROW(COMPILFAM),ROWS(COMPILFAM)+ROW(COMPILFAM))))))"

'COLLAGE SPECIAL FORMULE BA FO B2 B3:B400

'SELECTIONNER FEUILLE BA SA

Sheets("BA SA").Select

' BA SA A2

Range("A2:A699").FormulaArray = _
"=IF(ROWS(R2:R)<=COUNTA(SAISONS),INDEX(SAISONS,SMALL(IF(SAISONS<>"""",ROW(INDIRECT(""1:""&ROWS(SAISONS)))),ROWS(R2:R)),MOD(SMALL(IF(SAISONS<>"""",ROW(INDIRECT(""1:""&ROWS(SAISONS)))*10^5+COLUMN(SAISONS)),ROWS(R2:R)),10^5)-COLUMN(SAISONS)+1),"""")"

' BA SA B2

Range("B2:B699").FormulaArray = _
"=IF(INDEX(C1,MIN(IF(COMPILSAI<>"""",IF(COUNTIF(R1C:R[-1]C,COMPILSAI)=0,ROW(COMPILSAI),ROWS(COMPILSAI)+ROW(COMPILSAI)))))=0,"""",INDEX(C1,MIN(IF(COMPILSAI<>"""",IF(COUNTIF(R1C:R[-1]C,COMPILSAI)=0,ROW(COMPILSAI),ROWS(COMPILSAI)+ROW(COMPILSAI))))))"

'SELECTIONNER FEUILLE AGR

Sheets("AGR").Select

' AGR A2

Range("A2:A400").FormulaR1C1 = "=IF(OR('BA FO'!RC[1]=0,'BA FO'!RC[1]=""""),"""",'BA FO'!RC[1])"

' AGR B2

Range("B2:B400").FormulaArray = _
"=IF(RC[-1]="""","""",INDEX(result,MAX(IF(RC[-1]=champRech,COLUMN(champRech)))-COLUMN(champRech)+1))"

' AGR C24

Range("C24").FormulaR1C1 = "=COUNTA(R[-22]C[-2]:R[376]C[-2])-COUNTBLANK(R[-22]C[-2]:R[376]C[-2])"


' AGR C25

Range("C25").FormulaR1C1 = "=R[-1]C[15]"


' AGR C26

Range("C26:R26").FormulaR1C1 = "=R[-2]C=R[-1]C"
' AGR D24

Range("D24:Q24").FormulaR1C1 = "=COUNTA(R[-22]C:R[-1]C)"

'AGR D25

Range("D25:Q25").FormulaR1C1 = "=COUNTIF(R2C2:R400C2,R[-24]C)"


'AGR R24

Range("R24:R25").FormulaR1C1 = "=SUM(RC[-14]:RC[-1])"
'AGR A404

Range("A404:A600").FormulaR1C1 = _
"=IF(OR('BA FA'!R[-402]C[1]=0,'BA FA'!R[-402]C[1]=""""),"""",'BA FA'!R[-402]C[1])"

'AGR B404

Range("B404:B600").FormulaArray = _
"=IF(RC[-1]="""","""",INDEX(result2,MAX(IF(RC[-1]=champRech2,COLUMN(champRech2)))-COLUMN(champRech2)+1))"
'AGR C452

Range("C452").FormulaR1C1 = "=COUNTA(R[-48]C[-2]:R[148]C[-2])-COUNTBLANK(R[-48]C[-2]:R[148]C[-2])"


'AGR C453

Range("C453").FormulaR1C1 = "=R[-1]C[24]"


'AGR C454

Range("C454:AA454").FormulaR1C1 = "=R[-2]C=R[-1]C"

'AGR D452

Range("D452:Z452").FormulaR1C1 = "=COUNTA(R[-48]C:R[-2]C)"

'AGR D453

Range("D453:Z453").FormulaR1C1 = "=COUNTIF(R404C2:R600C2,R[-50]C)"

'AGR AA452

Range("AA452:AA453").FormulaR1C1 = "=SUM(RC[-23]:RC[-1])"

'AGR A604

Range("A604:A1301").FormulaR1C1 = _
"=IF(OR('BA SA'!R[-602]C[1]=0,'BA SA'!R[-602]C[1]=""""),"""",'BA SA'!R[-602]C[1])"

'AGR B604

Range("B604:B1301").FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC[5]&"" ""&RC[3],R604C10:R711C11,2,FALSE)),RC[5]&"" ""&RC[3],VLOOKUP(RC[5]&"" ""&RC[3],R604C10:R711C11,2,FALSE))"

'AGR D604

Range("D604:D1301").FormulaR1C1 = _
"=IF(RC[-3]="""","""",IF(OR(LEN(RC[-3])<4,RC[-3]=""N0000""),""AUTRE"",RIGHT((SUBSTITUTE(RC[-3],""-SP"","""")),4)))"

'AGR E604

Range("E604:E1301").FormulaR1C1 = _
"=IF(RC[-4]="""","""",IF(RC[-1]=""AUTRE"",""AUTRE"",""20""&RIGHT(RC[-1],2)))"

'COLLAGE SPECIAL FORMULE AGR E604 E605:E1301

Range("E604").Select
Selection.Copy
Range("E605:E1301").Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = Falseü


'AGR F604

Range("F604").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]=""autre"",""autre"",IF(LEN(RC[-5])=5,LEFT(RC[-5],1),LEFT(RC[-5],2)))"

'COLLAGE SPECIAL FORMULE AGR F604 F605:F1301

Range("F604").Select
Selection.Copy
Range("F605:F1301").Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = Falseü


'AGR G604

Range("G604").Select
ActiveCell.FormulaR1C1 = _
"=IF(OR(LEN(RC[-6])>7,RC[-1]=""PR"",RC[-1]=""AU"",RC[-1]=""99"",RC[-1]=""L0""),""autre"",RC[-1])"


'COLLAGE SPECIAL FORMULE AGR G604 G605:G1301

Range("G604").Select
Selection.Copy
Range("G605:G1301").Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = Falseü


'AGR H604

Range("H604").Select
ActiveCell.FormulaR1C1 = _
"=IF(LEFT(RC[-6],5)=""AUTRE"",""AUTRE"",LEFT(RC[-6],2))"


'COLLAGE SPECIAL FORMULE AGR H604 H605:H1301

Range("H604").Select
Selection.Copy
Range("H605:H1301").Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = Falseü


'AGR I604
Range("I604").Select
ActiveCell.FormulaR1C1 = _
"=IF(RIGHT(RC[-7],5)=""AUTRE"",""AUTRE"",RIGHT(RC[-7],4))"

'COLLAGE SPECIAL FORMULE AGR I604 I605:I1301

Range("I604").Select
Selection.Copy
Range("I605:I1301").Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = Falseü


'AGR A1305
Range("A1305").Select
ActiveCell.FormulaR1C1 = _
"=IF(OR('BA SE'!R[-1303]C=0,'BA SE'!R[-1303]C=""""),"""",'BA SE'!R[-1303]C)"

'COLLAGE SPECIAL FORMULE AGR A1305 A1306:A1329

Range("A1305").Select
Selection.Copy
Range("A1306:A1329").Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = Falseü


'AGR A1305
Range("B1305").Select
Selection.FormulaArray = _
"=IF(RC[-1]="""","""",INDEX(result3,MAX(IF(RC[-1]=champRech3,COLUMN(champRech3)))-COLUMN(champRech3)+1))"

'COLLAGE SPECIAL FORMULE AGR A1305 B1305:B1329

Range("B1305").Select
Selection.Copy
Range("B1306:B1329").Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = Falseü


'AGR C1327
Range("C1327").Select
ActiveCell.FormulaR1C1 = _
"=COUNTA(R[-22]C[-2]:R[2]C[-2])-COUNTBLANK(R[-22]C[-2]:R[2]C[-2])"

'AGR C1329
Range("C1329").Select
ActiveCell.FormulaR1C1 = "=R[-2]C=R[-2]C[5]"

'COLLAGE SPECIAL FORMULE AGR C1329 D1329:H1329

Range("C1329").Select
Selection.Copy
Range("D1329:H1329").Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = Falseü


'AGR D1327
Range("D1327").Select
ActiveCell.FormulaR1C1 = "=COUNTA(R[-22]C:R[-1]C)"

'COLLAGE SPECIAL FORMULE AGR D1327 E1327:G1327

Range("D1327").Select
Selection.Copy
Range("E1327:G1327").Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = Falseü


'AGR D1328
Range("D1328").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R1305C2:R1329C2,R[-24]C)"

'COLLAGE SPECIAL FORMULE AGR D1328 E1328:G1328

Range("D1328").Select
Selection.Copy
Range("E1328:G1328").Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = Falseü


'AGR D1329
Range("D1329").Select
ActiveCell.FormulaR1C1 = "=R[-2]C=R[-1]C"

'COLLAGE SPECIAL FORMULE AGR D1329 E1329:H1329

Range("D1329").Select
Selection.Copy
Range("E1329:H1329").Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = Falseü


'AGR H1327
Range("H1327").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-4]:RC[-1])"


'AGR H1328
Range("H1328").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-4]:RC[-1])"


Range("A1").Select
Calculate





'SELECTIONNER FEUILLE AGR2

Sheets("AGR2").Select

' AGR2 A1

Range("A1").Select
ActiveCell.FormulaR1C1 = "=AGR!RC"

'COLLAGE SPECIAL FORMULE AGR2 A1 A1:AA1384

Range("A1").Select
Selection.Copy
Range("A1:AA1384").Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = Falseü

Range("A1").Select
Calculate





Application.ScreenUpdating = True

'SELECTIONNER FEUILLE AGR

Sheets("AGR").Select
Range("A1").Select

'SAUVEGARDER

ActiveWorkbook.SAVE

Call R
Call V
Call L

Call SAVE
Application.Calculation = xlCalculationAutomatic

End Sub

Publicité


Signaler le contenu de ce document

Publicité