Optimisation calcul macro enregistreur de macro

Le 06/08/2015 à 17:24

Bonjour à toutes et tous,

A partir d’une extraction d’une base de données (onglets : BA SE, BA FO, BA FA,BA SA, L, V & R ; colonnes A à AC pour ces 3 derniers cités) j’ai créé par formules des agrégats (onglets : AGR & AGR 2) et ou des résultats de calcul qui se trouvent dans les colonnes AD à AP des feuilles L, V & R.

Des formules dont quelques unes reposent sur des plages nommées (certains d’entre vous m’ont d’ailleurs déjà aidé dans leur conception) sont aussi présentes sur toutes le feuilles.

Les calculs sont interdépendants et il faut recalculer chaque feuille lors de toutes modifications.

Les résultats sont conformes à ce que j’attend mais la volumétrie du fichier obtenu le rend difficilement exploitable (je suis sur mac et je n’ai que Excel à ma disposition)

Partant de ce constat je cherche à optimiser mon fichier et via l’enregistreur de macro (je ne maitrise pas VBA) j’ai créé des macros qui reprennent l’ensemble des formules de tous les onglets du classeur pour ensuite les coller sur les différentes lignes et colonnes correspondantes.

Mais les temps de calculs sont très longs et plantent même souvent au motif mémoire insuffisante.

Mon objectif final étant que tous les calculs s’effectuent au lancement d’une macro et ensuite remplacer ces calculs uniquement par leur valeur dans tous les onglets du classeur via collage spécial par exemple. Je pense aussi qu’il ne devrait y’avoir des calculs que si des données sont présentes sur les lignes des onglets L, V & R (par exemple la macro ne s’exécuterait sur l’onglet L que sur les lignes 2 à 5 (2 à 45709 sur fichier non épuré lien dropbox) et sur l’onglet V des lignes 2 à 3 (2 à 197233 fichier non épuré). Or je colle toutes les formules dans une plage allant de 2 à 340305 (plus dans l’avenir quand la base grandira) sur les onglets L, V & R.

En parcourant le forum, je pense que les lenteurs sont le résultat de mes macro non optimisées (pleins de select par exemple, mettre les plages dans des tableaux intermédiaires de variables temporaires, peut être des boucles). Peut être que mes formules aussi peuvent et devraient être optimisées ?

J’ai atteint la limite de mes compétences c’est pourquoi je vous soumet mon fichier pour aide. Ce n’est bien entendu pas un jeu de données réelles pour la confidentialité.

Vous trouverez une version épuré en lien direct (pour respecter la taille des pièces jointes exemple) et un lien dropbox vers une version complète.

J’espère être compréhensible, avoir respecté les règles du jeu de ce forum et que vous pourrez m’aider.

Merci à toutes et tous

Sub FOR_AGR()

Application.ScreenUpdating = False

'SELECTIONNER FEUILLE BA FO

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

' BA FO A2

Range("A2").Select
    Selection.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),"""")"

    'COLLAGE SPECIAL FORMULE BA FO A2 A3:A400

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

' BA FO B2

Range("B2").Select
    Selection.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))))))"

    'COLLAGE SPECIAL FORMULE BA FO B2 B3:B400

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

Range("A1").Select
'Calculate

' SELCTIONNER FEUILLE BA FA

Sheets("BA FA").Select

'BA FA A2

Range("A2").Select
    Selection.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),"""")"

    'COLLAGE SPECIAL FORMULE BA FA A2 A3:A400

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

'BA FA B2

Range("B2").Select
    Selection.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

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

Range("A1").Select
'Calculate

'SELECTIONNER FEUILLE BA SA

Sheets("BA SA").Select
Range("A1").Select

' BA SA A2

Range("A2").Select
    Selection.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),"""")"

    'COLLAGE SPECIAL FORMULE BA SA A2 A3:A699

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

' BA SA B2

Range("B2").Select
    Selection.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))))))"

     'COLLAGE SPECIAL FORMULE BA SA B2 B3:B699

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

Range("A1").Select
'Calculate

'SELECTIONNER FEUILLE AGR

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

' AGR A2

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

 ' COLLAGE SPECIAL FORMULE AGR A2 A3:A400

Range("A2").Select
Selection.Copy
Range("A3:A400").Select
    Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Application.CutCopyMode = False

' AGR B2

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

     'COLLAGE SPECIAL FORMULE AGR B2 B3:B400

Range("B2").Select
Selection.Copy
Range("B3:B400").Select
    Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Application.CutCopyMode = False

' AGR C24

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

' AGR C25

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

' AGR C26

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

    ' COLLAGE SPECIAL FORMULE AGR C26 D26:R26

    Range("C26").Select
    Selection.Copy
    Range("D26:R26").Select
        Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
    Application.CutCopyMode = False

' AGR D24

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

    ' COLLAGE SPECIAL FORMULE AGR D24 E24:Q24

    Range("D24").Select
    Selection.Copy
    Range("E24:Q24").Select
        Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
    Application.CutCopyMode = False

'AGR D25

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

    ' COLLAGE SPECIAL FORMULE AGR D25 E25:Q25

    Range("D25").Select
    Selection.Copy
    Range("E25:Q25").Select
        Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
    Application.CutCopyMode = False

'AGR R24

Range("R24").Select
    ActiveCell.FormulaR1C1 = "=SUM(RC[-14]:RC[-1])"

'AGR R25

Range("R25").Select
    ActiveCell.FormulaR1C1 = "=SUM(RC[-14]:RC[-1])"

'AGR A404

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

    ' COLLAGE SPECIAL FORMULE AGR A405:A600

    Range("A404").Select
    Selection.Copy
    Range("A405:A600").Select
        Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
    Application.CutCopyMode = False

'AGR B404

Range("B404").Select
    Selection.FormulaArray = _
        "=IF(RC[-1]="""","""",INDEX(result2,MAX(IF(RC[-1]=champRech2,COLUMN(champRech2)))-COLUMN(champRech2)+1))"

    ' COLLAGE SPECIAL FORMULE AGR A405:A600

    Range("B404").Select
    Selection.Copy
    Range("B405:B600").Select
        Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
    Application.CutCopyMode = False

 'AGR C452

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

 'AGR C453

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

'AGR C454

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

         'COLLAGE SPECIAL FORMULE AGR C454 D454:AA454

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

'AGR D452

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

    'COLLAGE SPECIAL FORMULE AGR D452 E452:Z452

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

 'AGR D453

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

    'COLLAGE SPECIAL FORMULE AGR D453 E453:Z453

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

'AGR AA452

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

'AGR AA453

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

'AGR A604

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

    'COLLAGE SPECIAL FORMULE AGR A604 A605:A1301

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

'AGR B604

Range("B604").Select
ActiveCell.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))"

     'COLLAGE SPECIAL FORMULE AGR B604 B605:B1301

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

 'AGR D604

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

    'COLLAGE SPECIAL FORMULE AGR D604 D605:D1301

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

'AGR E604

Range("E604").Select
ActiveCell.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

End Sub

Sub R()

Application.ScreenUpdating = False

'SELECTIONNER FEUILLE R

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

' R AD2, AE2, AF2, AG2, AH2, AI2, AJ2, AL2, AM2, AN2, AO2, AP2

Range("AD2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISBLANK(RC[-25]),"""",IF(ISERROR(VLOOKUP(RC[-14],SECTEURS2,2,FALSE)),""AUTRE"",IF(OR(RC[-14]="""",RC[-14]="" ""),""AUTRE"",VLOOKUP(RC[-14],SECTEURS2,2,FALSE))))"

Range("AE2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISBLANK(RC[-26]),"""",IF(ISERROR(VLOOKUP(RC[-4],FOURNISSEURS2,2,FALSE)),""AUTRE"",IF(OR(RC[-4]="""",RC[-4]="" ""),""AUTRE"",VLOOKUP(RC[-4],FOURNISSEURS2,2,FALSE))))"

Range("AF2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISBLANK(RC[-27]),"""",IF(ISERROR(VLOOKUP(RC[-9],FAMILLES2,2,FALSE)),""AUTRE"",IF(OR(RC[-9]="""",RC[-9]="" ""),""AUTRE"",VLOOKUP(RC[-9],FAMILLES2,2,FALSE))))"

Range("AG2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISBLANK(RC[-28]),"""",IF(RC[-10]=""JEAN"",""DENIM"",IF(ISERROR(SEARCH(""DENIM"",RC[-10])),"""",IF(SEARCH(""DENIM"",RC[-10])>=1,""DENIM"",""""))))"

Range("AH2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISBLANK(RC[-29]),"""",IF(ISERROR(VLOOKUP(RC[-25],SAISONS2,2,FALSE)),""autre AUTRE"",IF(OR(RC[-25]="""",RC[-25]="" ""),""autre AUTRE"",VLOOKUP(RC[-25],SAISONS2,2,FALSE))))"

Range("AI2").Select
    ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[-30]),"""",RIGHT(RC[-1],4))"

Range("AJ2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISBLANK(RC[-31]),"""",DATE(YEAR(RC[-31]),MONTH(RC[-31]),1))"

Range("AL2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISBLANK(RC[-33]),"""",IF(RC[-23]=0,RC[-17]/0.01,RC[-17]/RC[-23]))"

Range("AM2").Select
    ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[-34]),"""",(RC[-18]/1.2)-RC[-24])"

Range("AN2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISBLANK(RC[-35]),"""",IF(ISERROR(RC[-1]/RC[-19]),-100%,RC[-1]/RC[-19]))"

Range("AO2").Select
    ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[-36]),"""",RC[-20]-RC[-24])"

Range("AP2").Select
    ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[-37]),"""",RC[-1]/RC[-25])"

' R AK2

Range("AK2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(R[-1]C[-32]=""Date"",""SEM ""&TEXT(WEEKNUM(RC[-32],21),""0"")&"" ""&TEXT(RC[-32],""aaaa""),IF(AND(OR(WEEKNUM(R[-1]C[-32],21)=52,WEEKNUM(R[-1]C[-32],21)=53),TEXT(R[-1]C[-32],""aaaa"")=TEXT(RC[-32],""aaaa""),WEEKNUM(RC[-32],21)=1),""SEM ""&TEXT(WEEKNUM(RC[-32],21),""0"")&"" ""&TEXT(RC[-32],""aaaa"")+1,""SEM ""&TEXT(WEEKNUM(RC[-32],21),""0"")&"" ""&TEXT(RC[-32],""aaaa"")))"

'R AK3

Range("AK3").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISBLANK(R[-1]C[-32]),"""",IF(AND(OR(WEEKNUM(R[-1]C[-32],21)=52,WEEKNUM(R[-1]C[-32],21)=53),TEXT(R[-1]C[-32],""aaaa"")=TEXT(RC[-32],""aaaa""),WEEKNUM(RC[-32],21)=1),""SEM ""&TEXT(WEEKNUM(RC[-32],21),""0"")&"" ""&TEXT(RC[-32],""aaaa"")+1,""SEM ""&TEXT(WEEKNUM(RC[-32],21),""0"")&"" ""&TEXT(RC[-32],""aaaa"")))"

    'COLLAGE SPECIAL FORMULE R AK3

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

'COLLAGE SPECIAL FORMULE R AD2, AE2, AF2, AG2, AH2, AI2, AJ2, AL2, AM2, AN2, AO2, AP2

Range("AD2:AJ2").Select
Selection.Copy
Range("AD3:AJ340305").Select
    Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Application.CutCopyMode = Falseü

Range("AL2:AP2").Select
Selection.Copy
Range("AL3:AL340305").Select
    Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Application.CutCopyMode = Falseü

Calculate

Range("A1").Select

Application.ScreenUpdating = True

End Sub

Sub V()

Application.ScreenUpdating = False

'SELECTIONNER FEUILLE V

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

' R AD2, AE2, AF2, AG2, AH2, AI2, AJ2, AL2, AM2, AN2, AO2, AP2

Range("AD2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISBLANK(RC[-25]),"""",IF(ISERROR(VLOOKUP(RC[-14],SECTEURS2,2,FALSE)),""AUTRE"",IF(OR(RC[-14]="""",RC[-14]="" ""),""AUTRE"",VLOOKUP(RC[-14],SECTEURS2,2,FALSE))))"

Range("AE2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISBLANK(RC[-26]),"""",IF(ISERROR(VLOOKUP(RC[-4],FOURNISSEURS2,2,FALSE)),""AUTRE"",IF(OR(RC[-4]="""",RC[-4]="" ""),""AUTRE"",VLOOKUP(RC[-4],FOURNISSEURS2,2,FALSE))))"

Range("AF2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISBLANK(RC[-27]),"""",IF(ISERROR(VLOOKUP(RC[-9],FAMILLES2,2,FALSE)),""AUTRE"",IF(OR(RC[-9]="""",RC[-9]="" ""),""AUTRE"",VLOOKUP(RC[-9],FAMILLES2,2,FALSE))))"

Range("AG2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISBLANK(RC[-28]),"""",IF(RC[-10]=""JEAN"",""DENIM"",IF(ISERROR(SEARCH(""DENIM"",RC[-10])),"""",IF(SEARCH(""DENIM"",RC[-10])>=1,""DENIM"",""""))))"

Range("AH2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISBLANK(RC[-29]),"""",IF(ISERROR(VLOOKUP(RC[-25],SAISONS2,2,FALSE)),""autre AUTRE"",IF(OR(RC[-25]="""",RC[-25]="" ""),""autre AUTRE"",VLOOKUP(RC[-25],SAISONS2,2,FALSE))))"

Range("AI2").Select
    ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[-30]),"""",RIGHT(RC[-1],4))"

Range("AJ2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISBLANK(RC[-31]),"""",DATE(YEAR(RC[-31]),MONTH(RC[-31]),1))"

Range("AL2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISBLANK(RC[-33]),"""",IF(RC[-23]=0,RC[-17]/0.01,RC[-17]/RC[-23]))"

Range("AM2").Select
    ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[-34]),"""",(RC[-18]/1.2)-RC[-24])"

Range("AN2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISBLANK(RC[-35]),"""",IF(ISERROR(RC[-1]/RC[-19]),-100%,RC[-1]/RC[-19]))"

Range("AO2").Select
    ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[-36]),"""",RC[-20]-RC[-24])"

Range("AP2").Select
    ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[-37]),"""",RC[-1]/RC[-25])"

' R AK2

Range("AK2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(R[-1]C[-32]=""Date"",""SEM ""&TEXT(WEEKNUM(RC[-32],21),""0"")&"" ""&TEXT(RC[-32],""aaaa""),IF(AND(OR(WEEKNUM(R[-1]C[-32],21)=52,WEEKNUM(R[-1]C[-32],21)=53),TEXT(R[-1]C[-32],""aaaa"")=TEXT(RC[-32],""aaaa""),WEEKNUM(RC[-32],21)=1),""SEM ""&TEXT(WEEKNUM(RC[-32],21),""0"")&"" ""&TEXT(RC[-32],""aaaa"")+1,""SEM ""&TEXT(WEEKNUM(RC[-32],21),""0"")&"" ""&TEXT(RC[-32],""aaaa"")))"

'R AK3

Range("AK3").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISBLANK(R[-1]C[-32]),"""",IF(AND(OR(WEEKNUM(R[-1]C[-32],21)=52,WEEKNUM(R[-1]C[-32],21)=53),TEXT(R[-1]C[-32],""aaaa"")=TEXT(RC[-32],""aaaa""),WEEKNUM(RC[-32],21)=1),""SEM ""&TEXT(WEEKNUM(RC[-32],21),""0"")&"" ""&TEXT(RC[-32],""aaaa"")+1,""SEM ""&TEXT(WEEKNUM(RC[-32],21),""0"")&"" ""&TEXT(RC[-32],""aaaa"")))"

    'COLLAGE SPECIAL FORMULE R AK3

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

'COLLAGE SPECIAL FORMULE R AD2, AE2, AF2, AG2, AH2, AI2, AJ2, AL2, AM2, AN2, AO2, AP2

Range("AD2:AJ2").Select
Selection.Copy
Range("AD3:AJ340305").Select
    Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Application.CutCopyMode = Falseü

Range("AL2:AP2").Select
Selection.Copy
Range("AL3:AL340305").Select
    Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Application.CutCopyMode = Falseü

Calculate

Range("A1").Select

Application.ScreenUpdating = True

End Sub

Sub L()

Application.ScreenUpdating = False

'SELECTIONNER FEUILLE L

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

' R AD2, AE2, AF2, AG2, AH2, AI2, AJ2, AL2, AM2, AN2, AO2, AP2

Range("AD2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISBLANK(RC[-25]),"""",IF(ISERROR(VLOOKUP(RC[-14],SECTEURS2,2,FALSE)),""AUTRE"",IF(OR(RC[-14]="""",RC[-14]="" ""),""AUTRE"",VLOOKUP(RC[-14],SECTEURS2,2,FALSE))))"

Range("AE2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISBLANK(RC[-26]),"""",IF(ISERROR(VLOOKUP(RC[-4],FOURNISSEURS2,2,FALSE)),""AUTRE"",IF(OR(RC[-4]="""",RC[-4]="" ""),""AUTRE"",VLOOKUP(RC[-4],FOURNISSEURS2,2,FALSE))))"

Range("AF2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISBLANK(RC[-27]),"""",IF(ISERROR(VLOOKUP(RC[-9],FAMILLES2,2,FALSE)),""AUTRE"",IF(OR(RC[-9]="""",RC[-9]="" ""),""AUTRE"",VLOOKUP(RC[-9],FAMILLES2,2,FALSE))))"

Range("AG2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISBLANK(RC[-28]),"""",IF(RC[-10]=""JEAN"",""DENIM"",IF(ISERROR(SEARCH(""DENIM"",RC[-10])),"""",IF(SEARCH(""DENIM"",RC[-10])>=1,""DENIM"",""""))))"

Range("AH2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISBLANK(RC[-29]),"""",IF(ISERROR(VLOOKUP(RC[-25],SAISONS2,2,FALSE)),""autre AUTRE"",IF(OR(RC[-25]="""",RC[-25]="" ""),""autre AUTRE"",VLOOKUP(RC[-25],SAISONS2,2,FALSE))))"

Range("AI2").Select
    ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[-30]),"""",RIGHT(RC[-1],4))"

Range("AJ2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISBLANK(RC[-31]),"""",DATE(YEAR(RC[-31]),MONTH(RC[-31]),1))"

Range("AL2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISBLANK(RC[-33]),"""",IF(RC[-23]=0,RC[-17]/0.01,RC[-17]/RC[-23]))"

Range("AM2").Select
    ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[-34]),"""",(RC[-18]/1.2)-RC[-24])"

Range("AN2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISBLANK(RC[-35]),"""",IF(ISERROR(RC[-1]/RC[-19]),-100%,RC[-1]/RC[-19]))"

Range("AO2").Select
    ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[-36]),"""",RC[-20]-RC[-24])"

Range("AP2").Select
    ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[-37]),"""",RC[-1]/RC[-25])"

' R AK2

Range("AK2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(R[-1]C[-32]=""Date"",""SEM ""&TEXT(WEEKNUM(RC[-32],21),""0"")&"" ""&TEXT(RC[-32],""aaaa""),IF(AND(OR(WEEKNUM(R[-1]C[-32],21)=52,WEEKNUM(R[-1]C[-32],21)=53),TEXT(R[-1]C[-32],""aaaa"")=TEXT(RC[-32],""aaaa""),WEEKNUM(RC[-32],21)=1),""SEM ""&TEXT(WEEKNUM(RC[-32],21),""0"")&"" ""&TEXT(RC[-32],""aaaa"")+1,""SEM ""&TEXT(WEEKNUM(RC[-32],21),""0"")&"" ""&TEXT(RC[-32],""aaaa"")))"

'R AK3

Range("AK3").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISBLANK(R[-1]C[-32]),"""",IF(AND(OR(WEEKNUM(R[-1]C[-32],21)=52,WEEKNUM(R[-1]C[-32],21)=53),TEXT(R[-1]C[-32],""aaaa"")=TEXT(RC[-32],""aaaa""),WEEKNUM(RC[-32],21)=1),""SEM ""&TEXT(WEEKNUM(RC[-32],21),""0"")&"" ""&TEXT(RC[-32],""aaaa"")+1,""SEM ""&TEXT(WEEKNUM(RC[-32],21),""0"")&"" ""&TEXT(RC[-32],""aaaa"")))"

    'COLLAGE SPECIAL FORMULE R AK3

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

'COLLAGE SPECIAL FORMULE R AD2, AE2, AF2, AG2, AH2, AI2, AJ2, AL2, AM2, AN2, AO2, AP2

Range("AD2:AJ2").Select
Selection.Copy
Range("AD3:AJ340305").Select
    Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Application.CutCopyMode = Falseü

Range("AL2:AP2").Select
Selection.Copy
Range("AL3:AL340305").Select
    Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Application.CutCopyMode = Falseü

Calculate

Range("A1").Select

Application.ScreenUpdating = True

End Sub

Sub SAVE()

'SELECTIONNER FEUILLE AGR

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

'SAUVEGARDER

        ActiveWorkbook.SAVE

End Sub
35agregat-fo-xlsb.zip (226.50 Ko)
Le 07/08/2015 à 04:59

Hello,

Franchement, 1000 lignes, y'a du taff.

Si déjà tu souhaites optimiser ta version actuelle, quelques clés :

  • si un Range("xx").Select est suivi d'un second sans qu'il y ait d'instruction entre, c'est qu'il sert à rien ;
  • si tu as un Range("xx").Select suivi d'une instruction contenant "Selection" (en général au début), remplace ce dernier par Range("xx")
  • Vire tous les "Sheets("xx").Select". Lorsque tu fais référence à une cellule via vba par Range("xx"), disons range("C3"), il va prendre le C3 de la feuille active. Soit tu regardes les points "critiques" où ta feuille active pourrait changer, soit tu joues la prudence et tu précises la feuille à chaque fois.

Si le temps d'exec devient raisonnable, nickel, sinon, à voir

Le 07/08/2015 à 07:41

Bonjour,

Tu t'embêtes bien.

Garde tes formules sur la 1ère ligne ça n'en fera pas beaucoup. Et quand tu dois mettre à jour tu mets en calcul manuel, tu les recopies vers le bas avec .AutoFill, tu remets le calcul auto, et tu fais ton copier-collage spécial valeur (sauf la ligne de formules)

eric

Le 07/08/2015 à 10:53
SoumZoum a écrit :

Hello,

Franchement, 1000 lignes, y'a du taff.

Si déjà tu souhaites optimiser ta version actuelle, quelques clés :

  • si un Range("xx").Select est suivi d'un second sans qu'il y ait d'instruction entre, c'est qu'il sert à rien ;
  • si tu as un Range("xx").Select suivi d'une instruction contenant "Selection" (en général au début), remplace ce dernier par Range("xx")
  • Vire tous les "Sheets("xx").Select". Lorsque tu fais référence à une cellule via vba par Range("xx"), disons range("C3"), il va prendre le C3 de la feuille active. Soit tu regardes les points "critiques" où ta feuille active pourrait changer, soit tu joues la prudence et tu précises la feuille à chaque fois.

Si le temps d'exec devient raisonnable, nickel, sinon, à voir

Bonjour,

Je te remercie pour ta réponse et je vais essayer de mettre en place tes conseils.

Cordialement

eriiic a écrit :

Bonjour,

Tu t'embêtes bien.

Garde tes formules sur la 1ère ligne ça n'en fera pas beaucoup. Et quand tu dois mettre à jour tu mets en calcul manuel, tu les recopies vers le bas avec .AutoFill, tu remets le calcul auto, et tu fais ton copier-collage spécial valeur (sauf la ligne de formules)

eric

Bonjour Eric,

C'est effectivement une solution.

Même si je cherchais à tout automatiser, copier les données de l'extraction, lancer une macro et que cela génère mon fichier final.

Merci à toi d'avoir regardé mon problème

Cordialement

Le 07/08/2015 à 11:21

Bonjour,

Il est certain que ton code pourrait être allégé au moins de moitié. Tous les "Select", Selection" et consorts sont inutiles. VBA ne travaille pas à la souris, il n'a nul besoin de sélectionner, tu lui fait juste faire des actions supplémentaires ! Et les "CutCopyMode=False", totalement superflus (sauf dans 2 cas : 1) si une fermeture du classeur intervient après la copie, pour éviter le message te demandant si tu veux vider le presse-papier, 2) lorsque tu rends la main à l'utilisateur immédiatement après copie pour éliminer le cas échéant l'encadrement en pointillés clignotants de la zone copiée). Et les propriétés par défaut que tu ne changes pas et qu'il est donc inutile de confirmer (sauf dans les quelques cas connus de méthodes où Excel ne rétablit pas les valeurs par défaut entre deux appels)...

Ceci étant, le problème n'est pas là. Si tes ressources ne permettent plus de faire face au volume de calculs, Laisser les formules où utiliser VBA pour les insérer n'y changera rien. Il n'y a, comme le propose Eriiic, que le calcul manuel et le collage en valeur qui peut améliorer ta situation. S'il s'agissait d'alléger le classeur, il faudrait tenter de faire faire le maximum de calculs directement par VBA plutôt que par formules, mais le problème étant le calcul lui-même cela ne changerait pas grand-chose sur ce point.

Reste à voir si tu peux fragmenter ton volume global de calculs en lots plus restreints, à exécuter successivement... Sinon c'est qu'il te faut plus de puissance et plus de ressources...

Cordialement.

Ferrand

Le 07/08/2015 à 14:29

Bonjour,

je cherchais à tout automatiser

Ce que je te proposais est faisable par macro, je t'ai parlé d'AutoFill. Elle sera seulement 100 fois plus simple.

eric

Le 10/08/2015 à 14:53
MFerrand a écrit :

Bonjour,

Il est certain que ton code pourrait être allégé au moins de moitié. Tous les "Select", Selection" et consorts sont inutiles. VBA ne travaille pas à la souris, il n'a nul besoin de sélectionner, tu lui fait juste faire des actions supplémentaires ! Et les "CutCopyMode=False", totalement superflus (sauf dans 2 cas : 1) si une fermeture du classeur intervient après la copie, pour éviter le message te demandant si tu veux vider le presse-papier, 2) lorsque tu rends la main à l'utilisateur immédiatement après copie pour éliminer le cas échéant l'encadrement en pointillés clignotants de la zone copiée). Et les propriétés par défaut que tu ne changes pas et qu'il est donc inutile de confirmer (sauf dans les quelques cas connus de méthodes où Excel ne rétablit pas les valeurs par défaut entre deux appels)...

Ceci étant, le problème n'est pas là. Si tes ressources ne permettent plus de faire face au volume de calculs, Laisser les formules où utiliser VBA pour les insérer n'y changera rien. Il n'y a, comme le propose Eriiic, que le calcul manuel et le collage en valeur qui peut améliorer ta situation. S'il s'agissait d'alléger le classeur, il faudrait tenter de faire faire le maximum de calculs directement par VBA plutôt que par formules, mais le problème étant le calcul lui-même cela ne changerait pas grand-chose sur ce point.

Reste à voir si tu peux fragmenter ton volume global de calculs en lots plus restreints, à exécuter successivement... Sinon c'est qu'il te faut plus de puissance et plus de ressources...

Cordialement.

Ferrand

Bonjour

Merci pour ta réponse

Je vais essayer t'appliquer tes conseils même si mes connaissances en VBA sont très limitées.

Ainsi quand Eric écrit : AUTOFILL je ne connais pas cette procédure et comment on l'écrit. Je vais me renseigner.

" S'il s'agissait d'alléger le classeur, il faudrait tenter de faire faire le maximum de calculs directement par VBA plutôt que par formules"

C'est tout à fait ce que j'essaye de faire en ayant enregistré les formules par l'enregistreur de macro qui y arrive mais crée un code non optimisé. Je vais donc voir comment l'alléger en m'appuyant sur tes remarques versus les SELECT

Concernant le problème mémoire ma machine (macbook pro; processeur 2,3 ghz intel core I7, dernier OSX, mémoire 8Go) ne plante qu' à partir de la macro call L, call R et call V. Il n'y a pas de plantage sur mon fichier dans macro et calculs qui se font à partir des formules saisies dans les cellules des différents onglets.

Merci

Cordialement

Hugues

eriiic a écrit :

Bonjour,

je cherchais à tout automatiser

Ce que je te proposais est faisable par macro, je t'ai parlé d'AutoFill. Elle sera seulement 100 fois plus simple.

eric

Bonjour Eric,

Merci pour ton aide

Et pardonne mon ignorance je ne connais pas la procédure autofill. Je vais me renseigner.

Peux tu me donner un petit exemple de mise en place sur un bout de code pour éclaircir le champ de mes recherches.

Merci

Cordialement

Hugues

Le 10/08/2015 à 16:33

Bonjour,

Un exemple.

Les formules en lignes 2 servent de modèle à recopier lors d'une mise à jour.

Après la maj on fait un copié collé spécial valeurs des autres lignes.

Pas sûr que ça te dégonfle beaucoup ton classeur mais tu devrais gagner du temps à l'ouverture et en réactivité.

Je ne recalcule qu'une fois toutes les formules recopiées pour gagner du temps.

Si ça fait trop d'un coup pour ton classeur tu peux le faire après chaque area, voire même, en modifiant un peu, colonne par colonne. Mais à condition que tes formules soient optimisées et n'aient d'arguments qu'à gauche sinon tu risques d'avoir des formules ré-évaluées plusieurs fois.

eric

PS: je viens de regarder ton fichier. Je ne sais pas ce que c'est que toutes ces listes que tu construis par formules mais tant qu'à faire du vba autant que tu les construises avec, là tu auras du gain.

18formules-lig2-puis-valeurs-ensuite.xlsm (20.05 Ko)
Le 10/08/2015 à 22:46
eriiic a écrit :

Bonjour,

Un exemple.

Les formules en lignes 2 servent de modèle à recopier lors d'une mise à jour.

Après la maj on fait un copié collé spécial valeurs des autres lignes.

Pas sûr que ça te dégonfle beaucoup ton classeur mais tu devrais gagner du temps à l'ouverture et en réactivité.

Je ne recalcule qu'une fois toutes les formules recopiées pour gagner du temps.

Si ça fait trop d'un coup pour ton classeur tu peux le faire après chaque area, voire même, en modifiant un peu, colonne par colonne. Mais à condition que tes formules soient optimisées et n'aient d'arguments qu'à gauche sinon tu risques d'avoir des formules ré-évaluées plusieurs fois.

eric

PS: je viens de regarder ton fichier. Je ne sais pas ce que c'est que toutes ces listes que tu construis par formules mais tant qu'à faire du vba autant que tu les construises avec, là tu auras du gain.

Bonsoir Eric,

Merci pour ton fichier et ton partage de code que je vais essayer de comprendre et adapter à ma situation.

Cordialement

Hugues

Le 12/08/2015 à 00:19

Bonjour,

tu as sans doute fini mais comme j'ai fignolé un peu plus le code.

Possibilité d'ajouter des paramètres lors de l'appel pour élargir et assouplir son utilisation.

Dont : parColonne

Sub majFormules(plage As Range, Optional ligFormules As Long = 2, Optional colNbLig As Variant = "A", Optional parColonne As Boolean = False)

' Paramètres :

' plage : colonnes à traiter

' Ex : Feuil1.Cells (toutes), Range("B:E,M:P")

' Les colonnes sans formule dans ligFormule sont exclues automatiquement.

' Optional ligFormule: ligne des formules, ligne 2 par défaut

' Optional colNbLig : colonne de la feuille où on relève le nombre de lignes à traiter.

' indiquée par son n° ou sa lettre, "A" par défaut.

' Non pris en compte si parColonne=True

' Optional parColonne: False par défaut

' - True: les formules sont traitées colonne par colonne, le nombre de ligne est relevé sur chaque colonne.

' - False: les formules sont traitées par blocs.

' Le nombre de lignes est pris sur la colonne colNbLig de la feuille

' Par défaut : 1ère colonne de 'plage' ayant une formule

eric

23formules-lig2-puis-valeurs-ensuite-v2.xlsm (26.76 Ko)
Le 12/08/2015 à 08:54
eriiic a écrit :

Bonjour,

tu as sans doute fini mais comme j'ai fignolé un peu plus le code.

Possibilité d'ajouter des paramètres lors de l'appel pour élargir et assouplir son utilisation.

eric

Bonjour Eric,

Non je n'ai pas encore fini. Je continu d'adapter vos conseils respectifs et notamment ton code.

Je vais donc de nouveau étudier ton nouveau code.

Un grand merci pour l'intérêt que tu portes à mon problème.

Hugues

Le 12/08/2015 à 11:44

Re,

léger changement :

' Optional colNbLig : colonne de la feuille où on relève le nombre de lignes à traiter.
'                     Par défaut : 1ère colonne de 'plage' (avec ou sans formule)
au lieu de :
'           Par défaut : 1ère colonne de 'plage' ayant une formule

Ce qui permet de se baser sur la 1ère colonne de 'plage' (là où on ajoute les entrées en général) pour le nombre de lignes, et d'étendre les formules en conséquence sans être obligé d'utiliser le paramètre colNbLig pour imposer cette colonne.

Ca ne change rien pour toi vu que tu utiliseras parColonne=True je pense.

eric

20formules-lig2-puis-valeurs-ensuite-v2.xlsm (27.56 Ko)
Le 12/08/2015 à 13:14
eriiic a écrit :

Re,

léger changement :

' Optional colNbLig : colonne de la feuille où on relève le nombre de lignes à traiter.
'                     Par défaut : 1ère colonne de 'plage' (avec ou sans formule)
au lieu de :
'           Par défaut : 1ère colonne de 'plage' ayant une formule

Ce qui permet de se baser sur la 1ère colonne de 'plage' (là où on ajoute les entrées en général) pour le nombre de lignes, et d'étendre les formules en conséquence sans être obligé d'utiliser le paramètre colNbLig pour imposer cette colonne.

Ca ne change rien pour toi vu que tu utiliseras parColonne=True je pense.

eric

Bonjour Eric,

Je vais regarder cette modif même si pour l'instant j'utilise effectivement ton code avec colonne à true.

Mais je pense aussi utiliser sur d'autres onglets avec colonne à false

J'ai réussi a adapter ton code (plus exactement je l'ai repris en faisant varier les plages de son action) mais si j'arrive à lui dire de traiter des plages AD à AP je n'arrive pas à lui dire d'agir à partir des formules de la ligne 3

Ex1, sur feuille active, colonnes restreintes par plage :
    'paramètres par défaut ligFormules=2, parColonne=False, le nombre de lignes est relevé en colonne "A"
    majFormules [AD:AP]

    'Ex2, sur feuille "Feuil1", colonnes restreintes par plage :
    ' formules en ligne 2 j'ai modifié par 3, le nombre de lignes est relevé sur la colonne C j'ai remplacé par AC. Traitement par blocs
    majFormules Sheets("L").[AD:AP], ligFormules:=3, colNbLig:="AC"

    'Ex3, sur feuille active, toutes les colonnes :
    ' formules en ligne 2 j'ai modifié par 3, le nombre de lignes est relevé sur chaque colonne traitée individuellement
    majFormules Sheets("L").Cells, ligFormules:=3, parColonne:=True
End Sub

Entraine erreur à ci aprés :

Set pl = plage.Rows(ligFormules).SpecialCells(xlCellTypeFormulas)

A ton avis quelle erreur ai je commis ?

Merci

Hugues

Le 12/08/2015 à 14:08

Je pensais plus que c'était moi qui en avait fait une vu que je n'ai pas tout testé, ni blindé les contrôles...

Là je viens de tester et c'est ok chez moi.

Sans doute un cas que je n'ai pas imaginé, il faudrait ton fichier (réduit au nécessaire, 5 lignes c'est assez)

eric

Le 12/08/2015 à 19:27
eriiic a écrit :

Je pensais plus que c'était moi qui en avait fait une vu que je n'ai pas tout testé, ni blindé les contrôles...

Là je viens de tester et c'est ok chez moi.

Sans doute un cas que je n'ai pas imaginé, il faudrait ton fichier (réduit au nécessaire, 5 lignes c'est assez)

eric

Bonsoir Eric,

Ci joint le fichier comme demandé.

Ton code est celui appelé COPY dans module 3

Dans le

22copy-test-eric.xlsm (274.66 Ko)

module 1 c'est le code que j'ai bâti en suivant les conseils de cette discussion (suppression d'un maximum de select), il fonctionne plus rapidement, ne plante plus mais reste encore un peu long en exécution. Si en plus d'examiner ton code tu peux jeter un oeil sur celui du module 1 et me donner d'autres conseils c'est avec plaisir. Mais ce n'est pas une priorité je préfère que tu t'attaches à ton code car mis à part ce petit bug quand on veut changer la ligne de formule à prendre en compte pour commencer le copiage il fonctionne très bien et rapidement. Encore merci pour ce partage

Cordialement

Hugues

Le 12/08/2015 à 23:32

Hé, j'avais dit avec un classeur réduit aui nécessaire.

Toutes les autres feuilles sont inutiles pour tester ma macro. Sauf à faire attendre 10 min sur le test par colonne...

On est bien d'accord que je t'ai mis 3 exemples de syntaxe pour les tests mais que dans la vraie vie il ne faut qu'un seul appel avec les bons arguments pour ce que tu veux faire hein ?

Et que tes formules dans L sont en ligne 2, pas 3 ?

Quoiqu'il en soit j'ai testé les 3 exemples, pas d'erreur mais je pense que j'ai trouvé le pourquoi.

Dans l'exemple 1 la feuille n'est pas stipulée. Dans ta tête c'est peut-être la feuille L mais pour excel c'est la feuille active.

Et si sur ligFormules (par défaut =2) il n'y a pas de formule le SpecialCells(xlCellTypeFormulas) plante, c'est normal.

Je pourrais ajouter un test pour éviter le plantage (pas urgent) mais si tu n'as pas de formules sur cette ligne tu n'as aucune raison d'appeler cette macro, cqfd.

Donc l'appeler avec raison, une seule fois, et avec les bons arguments

Dis-moi si c'était bien dans ce contexte ou sinon donne plus de précision sur tes manip.

Par ailleurs j'ai oublié de réactiver la ligne ScreenUpdating= False, à corriger

Si en plus d'examiner ton code tu peux jeter un oeil sur celui du module 1 et me donner d'autres conseils c'est avec plaisir.

Oh non, je ne regarde même pas, inutile je pense.

Tu as plusieurs centaines de formules, matricielles pour commencer (pas conseillé du tout s'il y en a trop), mais en plus un certain nombre sont volatiles !!!. C'est à dire qu'elles sont recalculées à chaque fois, à la moindre petite saisie d'un 1 dans une cellule. Et non seulement elles sont recalculées, mais du coup toutes celles qui dépendent d'elles aussi. Si ça se trouve presque toutes parce qu'Indirect() est volatile, et tu l'as en A qui est utilisé par les autres colonnes...

Teste ce que ça donne en limitant le nombre de formules avec ma macro.

Tout dépend si tes listes sont à mettre à jour régulièrement ou pas car je pense que ça prendra 5-10 min à chaque fois, mais tu n'attendras plus 30s entre chaque saisie.

Donc essaie avec :

    majFormules Sheets("BA FO").Cells, parColonne:=True
    majFormules Sheets("BA FA").Cells, parColonne:=True
    majFormules Sheets("BA SA").Cells, parColonne:=True
    majFormules Sheets("AGR").Cells, parColonne:=True
    ' sûrement pas nécessaire, je pense qu'il faut une mise à jour au fil des saisies, tu vois :
    'majFormules Sheets("L").[AD:AP], colNbLig:="AC"

Ca sera long à chaque mise à jour à cause de tes formules mais je pense que tu ne reconnaitras pas ton classeur en temps d'ouverture et en réactivité.

Sinon tu abandonnes cette voie et tu fais ce que je t'ai dit auparavant : construire tes listes par macro, sans formules. Tu fabriques une liste dans une variable tableau texte, tu la colles, et tu passes à la suivante.

eric

PS: je n'aime pas trop ta feuille AGR avec des formules en plein milieu (C24:R26). Pas compatible avec ma macro si en vrai tu as des formules en C2:R2. Tes formules au milieu vont sauter. Coupe-colle dans une feuille dédiée ou modifie ma macro pour en tenir compte.

Le 13/08/2015 à 13:37

Bonjour Eric,

Comme tu le vois mes connaissance en vba sont limitées et c'est pourquoi j'ai mal adapté ton code de copy car trés certainement pas tout compris dans le détail.

Je te cite donc pour répondre plus clairement à tes questions :

Hé, j'avais dit avec un classeur réduit au nécessaire.Toutes les autres feuilles sont inutiles pour tester ma macro. Sauf à faire attendre 10 min sur le test par colonne..."

En fait je t'ai envoyé le classeur complet pour que tu visualises tout le cheminement. A terme je chercher effectivement à remplacer toutes les formules uniquement par leur résultat. C'est pourquoi vu mes lacunes en VBA j'ai travaillé via l'enregistreur de macro.

"On est bien d'accord que je t'ai mis 3 exemples de syntaxe pour les tests mais que dans la vraie vie il ne faut qu'un seul appel avec les bons arguments pour ce que tu veux faire hein ?"

Je commence à conprendre et vais m'atteler à ne qu'une syntaxe en fonction de mes tests, j'essaye de comprendre ton code

"Et que tes formules dans L sont en ligne 2, pas 3 ?"

En fait les formules à prendre en compte pour la recopie dans onglet L(il y'a dans mon classseur final 2 autres onglets identiques sur la struture mais avec des données différentes qui représentent des magasins) sont en ligne 3. La ligne 2 contient des formules mais uniquement valable en ligne 2 notamment pour le numéro de semaine. C'est pourquoi je cherche à adapter ton code dans ce sens.

"Dans l'exemple 1 la feuille n'est pas stipulée. Dans ta tête c'est peut-être la feuille L mais pour excel c'est la feuille active.

Et si sur ligFormules (par défaut =2) il n'y a pas de formule le SpecialCells(xlCellTypeFormulas) plante, c'est normal.

Je pourrais ajouter un test pour éviter le plantage (pas urgent) mais si tu n'as pas de formules sur cette ligne tu n'as aucune raison d'appeler cette macro, cqfd. Donc l'appeler avec raison, une seule fois, et avec les bons arguments

Dis-moi si c'était bien dans ce contexte ou sinon donne plus de précision sur tes manip."

Tu as raison je pensais faire agir la macro uniquement sur la feuille L ( et sur d'autres feuilles ayant la même structure 3 en tout aujourd'hui peut être plus demain). Je ne comprend pas bien la notion de feuille active mais avec tes explications du jour et le bout de code ci après que tu m'as envoyé les choses commencent à germer dans la mise en place des bonnes syntaxes.

majFormules Sheets("BA FO").Cells, parColonne:=True
    majFormules Sheets("BA FA").Cells, parColonne:=True
    majFormules Sheets("BA SA").Cells, parColonne:=True
    majFormules Sheets("AGR").Cells, parColonne:=True
    ' sûrement pas nécessaire, je pense qu'il faut une mise à jour au fil des saisies, tu vois :
   'majFormules Sheets("L").[AD:AP], colNbLig:="AC"

"Tu as plusieurs centaines de formules, matricielles pour commencer (pas conseillé du tout s'il y en a trop), mais en plus un certain nombre sont volatiles !!!. C'est à dire qu'elles sont recalculées à chaque fois, à la moindre petite saisie d'un 1 dans une cellule. Et non seulement elles sont recalculées, mais du coup toutes celles qui dépendent d'elles aussi. Si ça se trouve presque toutes parce qu'Indirect() est volatile, et tu l'as en A qui est utilisé par les autres colonnes...

Teste ce que ça donne en limitant le nombre de formules avec ma macro.

Tout dépend si tes listes sont à mettre à jour régulièrement ou pas car je pense que ça prendra 5-10 min à chaque fois, mais tu n'attendras plus 30s entre chaque saisie."

Je veux effectivement avec ta macro, supprimer les formules par leur résultats. Les listes sont à mettre à jour assez régulièrement et les 5/10 minutes sont à fait acceptable (je ferais appel à ce classeur recalculé une fois par semaine le lundi, le reste je du temps je ne ne me servirait que de la base L non réactualisée).

"Sinon tu abandonnes cette voie et tu fais ce que je t'ai dit auparavant : construire tes listes par macro, sans formules. Tu fabriques une liste dans une variable tableau texte, tu la colles, et tu passes à la suivante."

Je trouve que avec ton aide et tes conseils je suis sur une très bonne voie concernant onglet L. Pour les autres onglets j'ai effectivement lu des éléments sur les ARRAY et les variable tableau sans grande compréhension sur leur mise en place et ce que tu dis ci dessus est incompréhensible pour une mise en place en l'état actuel de mes connaissances mais je vais continuer à chercher et me documenter.

PS: je n'aime pas trop ta feuille AGR avec des formules en plein milieu (C24:R26). Pas compatible avec ma macro si en vrai tu as des formules en C2:R2. Tes formules au milieu vont sauter. Coupe-colle dans une feuille dédiée ou modifie ma macro pour en tenir compte.[b]]

Je vais voir pour modifier cette feuille effectivement ou adapter ta macro.

Merci

Cordialement

Hugues

Rechercher des sujets similaires à "optimisation calcul macro enregistreur"