Optimiser un code

**aziz1015** · 20/01/2017, 13h44

Bonjour,

Je fais appel à votre expertise, j'ai automatiser un travail qui prend une journée ou 2 sous VBA, mais je trouve que c'est quand même encore lent par macro parce que cela me prend environ 5 min et là je n'ai que 20 000 lignes, j'aurai parfois 40 000 lignes à manipuler par fichier, cela vient du fait que j'utilise les formules que j'ajoute directement dans chaque cellule Excel et je ne vois pas commment je pourrais faire autrement, j'ai ess&yé :

1. faire un copier coller par macro mais ça ne change rien puisque je le fais après l'application de la macro
2. j'ai essayé d'utiliser une boucle for mais c'est pire

Je vous mets mon code :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
Sub Final()
 
Dim Ws1 As Worksheet
Dim Ws2 As Worksheet
Dim Ws3 As Worksheet
Dim a As Integer
 
    Set Ws1 = ThisWorkbook.Worksheets("TCD DIRECT")
    Set Ws2 = ThisWorkbook.Worksheets("Final")
    Set Ws3 = ThisWorkbook.Worksheets("DIRECT GIARD")
 
    Ws1.PivotTables("TCD Direct").PivotFields("EDW?").PivotItems("N").Visible = True
    Ws1.PivotTables("TCD Direct").PivotFields("EDW?").PivotItems("O").Visible = False
    Nbl = Ws1.Cells(Rows.Count, 1).End(xlUp).Row
    cel = Ws1.Cells(1, 1).End(xlDown).Row
    If Not IsEmpty(Ws2.Range("B2")) Then Ws2.Range(Ws2.Cells(2, 1), Ws2.Cells(60000, 30)).Clear
 
    Ws1.Range(Ws1.Cells(cel + 2, 1), Ws1.Cells(Nbl - 1, 1)).Copy Ws2.Range("B2")
 
    Nbl1 = Ws2.Cells(Rows.Count, 2).End(xlUp).Row
 
    Application.ScreenUpdating = False
    Ws2.Range(Ws2.Cells(2, 1), Ws2.Cells(Nbl1, 1)).Value = "DIRECT"
    Ws2.Range(Ws2.Cells(2, 3), Ws2.Cells(Nbl1, 3)).Formula = "=RC[-1]"
    Ws2.Range(Ws2.Cells(2, 5), Ws2.Cells(Nbl1, 5)).Formula = "=INDEX('DIRECT GIARD'!R2C55:R32000C55,MATCH(RC[-3],'DIRECT GIARD'!R2C63:R32000C63,0),1)"
 
    Ws2.Range(Ws2.Cells(2, 7), Ws2.Cells(Nbl1, 7)).FormulaR1C1 = _
    "=INDEX('DIRECT GIARD'!R2C60:R22000C60,MATCH(RC[-5],'DIRECT GIARD'!R2C63:R22000C63,0),1)"
    Ws2.Range(Ws2.Cells(2, 8), Ws2.Cells(Nbl1, 8)).FormulaR1C1 = "=RC[-3]&RC[-1]"
    Ws2.Range(Ws2.Cells(2, 9), Ws2.Cells(Nbl1, 9)).FormulaR1C1 = _
        "=IF(ISERROR(MATCH(RC[-1],Priorités!R2C1:R106C1,0)),"""",INDEX(Priorités!R2C2:R106C2,MATCH(RC[-1],Priorités!R2C1:R106C1,0),1))"
 
    Ws2.Range(Ws2.Cells(2, 10), Ws2.Cells(Nbl1, 10)).FormulaR1C1 = _
        "=INDEX('DIRECT GIARD'!R2C64:R22000C64,MATCH(RC[-8],'DIRECT GIARD'!R2C63:R22000C63,0),1)"
    Ws2.Range(Ws2.Cells(2, 11), Ws2.Cells(Nbl1, 11)).FormulaR1C1 = _
        "=GETPIVOTDATA(""Somme de cout2016"",'TCD DIRECT'!R3C1,""N_ID"",RC2)"
    Ws2.Range(Ws2.Cells(2, 12), Ws2.Cells(Nbl1, 12)).FormulaR1C1 = _
        "=GETPIVOTDATA(""Somme de reg2016"",'TCD DIRECT'!R3C1,""N_ID"",RC2)"
    Ws2.Range(Ws2.Cells(2, 13), Ws2.Cells(Nbl1, 13)).FormulaR1C1 = _
        "=GETPIVOTDATA(""Somme de cout2015"",'TCD DIRECT'!R3C1,""N_ID"",RC2)"
    Ws2.Range(Ws2.Cells(2, 14), Ws2.Cells(Nbl1, 14)).FormulaR1C1 = _
        "=GETPIVOTDATA(""Somme de reg2015"",'TCD DIRECT'!R3C1,""N_ID"",RC2)"
    Ws2.Range(Ws2.Cells(2, 19), Ws2.Cells(Nbl1, 19)).FormulaR1C1 = _
            "=IF(RC[-10]="""",""pas de seuil"",IF(RC[-8]>=RC[-10]*75%,""OUI"",""NON""))"
    Application.ScreenUpdating = True
 
    Ws1.PivotTables("TCD Direct").PivotFields("EDW?").PivotItems("O").Visible = True
    Ws1.PivotTables("TCD Direct").PivotFields("EDW?").PivotItems("N").Visible = False
    Nbl2 = Ws1.Cells(Rows.Count, 1).End(xlUp).Row
    cel = Ws1.Cells(1, 1).End(xlDown).Row
    Ws1.Range(Ws1.Cells(cel + 2, 1), Ws1.Cells(Nbl2 - 1, 1)).Copy Ws2.Range("B" & Nbl1 + 1)
    Ws2.Range(Cells(Nbl1 + 1, 1), Cells(Nbl1 + Nbl2 - 6, 1)).Value = "EDW"
 
    Ws2.Range(Ws2.Cells(Nbl1 + 1, 3), Ws2.Cells(Nbl1 + Nbl2 - 6, 3)).Formula = "=INDEX(Liste!R2C18:R3500C18,MATCH(RC[-1],Liste!R2C16:R3500C16,0),1)"
    Ws2.Range(Ws2.Cells(Nbl1 + 1, 4), Ws2.Cells(Nbl1 + Nbl2 - 6, 4)).Formula = "=INDEX(GIARD!R2C7:R3146C7,MATCH(RC[-2],GIARD!R2C14:R3146C14,0),1)"
    Ws2.Range(Ws2.Cells(Nbl1 + 1, 5), Ws2.Cells(Nbl1 + Nbl2 - 6, 5)).Formula = "=INDEX('DIRECT GIARD'!R2C55:R32000C55,MATCH(RC[-3],'DIRECT GIARD'!R2C63:R32000C63,0),1)"
    Ws2.Range(Ws2.Cells(Nbl1 + 1, 6), Ws2.Cells(Nbl1 + Nbl2 - 6, 6)).Formula = "=INDEX(GIARD!R2C12:R3146C12,MATCH(RC[-4],GIARD!R2C14:R3146C14,0),1)"
 
    Ws2.Range(Ws2.Cells(Nbl1 + 1, 7), Ws2.Cells(Nbl1 + Nbl2 - 6, 7)).FormulaR1C1 = _
    "=INDEX('DIRECT GIARD'!R2C60:R22000C60,MATCH(RC[-5],'DIRECT GIARD'!R2C63:R22000C63,0),1)"
    Ws2.Range(Ws2.Cells(Nbl1 + 1, 8), Ws2.Cells(Nbl1 + Nbl2 - 6, 8)).FormulaR1C1 = "=RC[-3]&RC[-1]"
    Ws2.Range(Ws2.Cells(Nbl1 + 1, 9), Ws2.Cells(Nbl1 + Nbl2 - 6, 9)).FormulaR1C1 = _
        "=IF(ISERROR(MATCH(RC[-1],Priorités!R2C1:R106C1,0)),"""",INDEX(Priorités!R2C2:R106C2,MATCH(RC[-1],Priorités!R2C1:R106C1,0),1))"
    Ws2.Range(Ws2.Cells(Nbl1 + 1, 10), Ws2.Cells(Nbl1 + Nbl2 - 6, 10)).FormulaR1C1 = _
        "=INDEX('DIRECT GIARD'!R2C64:R22000C64,MATCH(RC[-8],'DIRECT GIARD'!R2C63:R22000C63,0),1)"
    Ws2.Range(Ws2.Cells(Nbl1 + 1, 11), Ws2.Cells(Nbl1 + Nbl2 - 6, 11)).FormulaR1C1 = _
        "=GETPIVOTDATA(""Somme de cout2016"",'TCD DIRECT'!R3C1,""N_ID"",RC2)"
    Ws2.Range(Ws2.Cells(Nbl1 + 1, 12), Ws2.Cells(Nbl1 + Nbl2 - 6, 12)).FormulaR1C1 = _
        "=GETPIVOTDATA(""Somme de reg2016"",'TCD DIRECT'!R3C1,""N_ID"",RC2)"
    Ws2.Range(Ws2.Cells(Nbl1 + 1, 13), Ws2.Cells(Nbl1 + Nbl2 - 6, 13)).FormulaR1C1 = _
        "=GETPIVOTDATA(""Somme de cout2015"",'TCD DIRECT'!R3C1,""N_ID"",RC2)"
    Ws2.Range(Ws2.Cells(Nbl1 + 1, 14), Ws2.Cells(Nbl1 + Nbl2 - 6, 14)).FormulaR1C1 = _
        "=GETPIVOTDATA(""Somme de reg2015"",'TCD DIRECT'!R3C1,""N_ID"",RC2)"
    Ws2.Range(Ws2.Cells(Nbl1 + 1, 15), Ws2.Cells(Nbl1 + Nbl2 - 6, 15)).FormulaR1C1 = _
        "=GETPIVOTDATA(""Somme de COUT_TOTAL"",'TCD EDW'!R3C1,""DATE_STAT"",DATE(Extraction!R2C5,Extraction!R2C4,Extraction!R2C3),""EDW"",RC[-13])"
        Ws2.Range(Ws2.Cells(Nbl1 + 1, 16), Ws2.Cells(Nbl1 + Nbl2 - 6, 16)).FormulaR1C1 = _
        "=GETPIVOTDATA(""Somme de REGLEMENT"",'TCD EDW'!R3C1,""DATE_STAT"",DATE(Extraction!R2C5,Extraction!R2C4,Extraction!R2C3),""EDW"",RC[-14])"
    Ws2.Range(Ws2.Cells(Nbl1 + 1, 17), Ws2.Cells(Nbl1 + Nbl2 - 6, 17)).FormulaR1C1 = "=RC[-2]-RC[-6]"
    Ws2.Range(Ws2.Cells(Nbl1 + 1, 18), Ws2.Cells(Nbl1 + Nbl2 - 6, 18)).FormulaR1C1 = "=RC[-2]-RC[-6]"
    Ws2.Range(Ws2.Cells(Nbl1 + 1, 19), Ws2.Cells(Nbl1 + Nbl2 - 6, 19)).FormulaR1C1 = _
            "=IF(RC[-10]="""",""pas de seuil"",IF(RC[-8]>=RC[-10]*75%,""OUI"",""NON""))"
    Application.ScreenUpdating = True
 
    Ws1.PivotTables("TCD Direct").PivotFields("EDW?").PivotItems("N").Visible = True
    Ws1.PivotTables("TCD Direct").PivotFields("EDW?").PivotItems("O").Visible = True
 
 
End Sub

Merci d'avance pour votre aide

**Menhir** · 20/01/2017, 13h55

Peut-être avec ça : https://msdn.microsoft.com/fr-fr/lib.../ff821260.aspx

Tu peux aussi mettre tes formules dans les cellules d'une seule ligne et ensuite faire une copie de cette ligne dans toute la zone traitée.

**aziz1015** · 20/01/2017, 14h11

Merci Menhir pour votre réponse, je pense en effet que cela fonctionnerait mieux. Pourrais je svp avoir un exemple d'application en VBA ?

Merci d'avance