Optimiser un code

Le 04/06/2013 à 17:36

Bonjour à toutes et à tous,

Je suis novice en VBA. Il m'a fallu pour mon stage réaliser un code vba pour améliorer le fonctionnement d'un fichier. Malheureusement, la macro est trop lente 12secondes. Pourriez-vous m'aider à l'optimiser afin de réduire le temps au maximum. En effet, le traitement de cette macro doit au final quasiment être instantannée. Merci pour votre aide!

Je ne peux pas mettre de fichier en pièce jointe, car je travaille sur du confidentiel industrie. Mais voici le code :

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

' ETAPE 1 :  SOMME DES MASSES

Range("C93") = Application.Sum(Range("C8:C90"))
Range("C165") = Application.Sum(Range("C104:C162"))
Range("C184") = Application.Sum(Range("C174:C181"))
Range("C200") = Application.Sum(Range("C193:C197"))
Range("C266") = Application.Sum(Range("C209:C263"))
Range("C324") = Application.Sum(Range("C275:C321"))
Range("C360") = Application.Sum(Range("C333:C357"))
Range("C381") = Application.Sum(Range("C369:C378"))
Range("C399") = Application.Sum(Range("C390:C396"))
Range("C466") = Application.Sum(Range("C408:C463"))
Range("C493") = Application.Sum(Range("C475:C490"))
Range("C517") = Application.Sum(Range("C502:C514"))
Range("C530") = Application.Sum(Range("C526:C527"))
Range("C545") = Application.Sum(Range("C539:C542"))

'ETAPE 2 :

For i = 8 To 547
    For j = 4 To 6
        Select Case i
        Case 8 To 90
            Cells(i, j + 17) = Cells(i, "C") * Cells(i, j)
        Case 104 To 162
            Cells(i, j + 17) = Cells(i, "C") * Cells(i, j)
        Case 174 To 181
            Cells(i, j + 17) = Cells(i, "C") * Cells(i, j)
        Case 193 To 197
            Cells(i, j + 17) = Cells(i, "C") * Cells(i, j)
        Case 209 To 263
            Cells(i, j + 17) = Cells(i, "C") * Cells(i, j)
        Case 275 To 321
            Cells(i, j + 17) = Cells(i, "C") * Cells(i, j)
        Case 333 To 357
            Cells(i, j + 17) = Cells(i, "C") * Cells(i, j)
        Case 369 To 378
            Cells(i, j + 17) = Cells(i, "C") * Cells(i, j)
        Case 390 To 396
            Cells(i, j + 17) = Cells(i, "C") * Cells(i, j)
        Case 408 To 463
            Cells(i, j + 17) = Cells(i, "C") * Cells(i, j)
        Case 475 To 490
            Cells(i, j + 17) = Cells(i, "C") * Cells(i, j)
        Case 502 To 514
            Cells(i, j + 17) = Cells(i, "C") * Cells(i, j)
        Case 526 To 527
            Cells(i, j + 17) = Cells(i, "C") * Cells(i, j)
        Case 539 To 542
            Cells(i, j + 17) = Cells(i, "C") * Cells(i, j)
        Case Else
        End Select

'ETAPE 3:
        If i = 93 Or i = 165 Or i = 184 Or i = 200 Or i = 266 Or i = 324 Or i = 360 Or i = 381 Or i = 399 Or i = 466 Or i = 493 Or i = 517 Or i = 530 Or i = 545 Then Cells(i, j) = Cells(i, j + 17) / Cells(i, "C")

    Next j

'ETAPE 4:
    Select Case i
        Case 8 To 90
            X = Cells(93, "D").Value
            Y = Cells(93, "E").Value
            Z = Cells(93, "F").Value

            Cells(i, "N") = Cells(i, "C") * ((Cells(i, "E") - Y) ^ 2 + (Cells(i, "F") - Z) ^ 2) * 0.000001
            Cells(i, "O") = Cells(i, "C") * ((Cells(i, "D") - X) ^ 2 + (Cells(i, "F") - Z) ^ 2) * 0.000001
            Cells(i, "P") = Cells(i, "C") * ((Cells(i, "D") - X) ^ 2 + (Cells(i, "E") - Y) ^ 2) * 0.000001
            Cells(i, "Q") = Cells(i, "C") * (Cells(i, "D") - X) * (Cells(i, "E") - Y) * 0.000001
            Cells(i, "R") = Cells(i, "C") * (Cells(i, "E") - Y) * (Cells(i, "F") - Z) * 0.000001
            Cells(i, "S") = Cells(i, "C") * (Cells(i, "D") - Y) * (Cells(i, "F") - Z) * 0.000001

        Case 104 To 162
            X = Cells(165, "D").Value
            Y = Cells(165, "E").Value
            Z = Cells(165, "F").Value

            Cells(i, "N") = Cells(i, "C") * ((Cells(i, "E") - Y) ^ 2 + (Cells(i, "F") - Z) ^ 2) * 0.000001
            Cells(i, "O") = Cells(i, "C") * ((Cells(i, "D") - X) ^ 2 + (Cells(i, "F") - Z) ^ 2) * 0.000001
            Cells(i, "P") = Cells(i, "C") * ((Cells(i, "D") - X) ^ 2 + (Cells(i, "E") - Y) ^ 2) * 0.000001
            Cells(i, "Q") = Cells(i, "C") * (Cells(i, "D") - X) * (Cells(i, "E") - Y) * 0.000001
            Cells(i, "R") = Cells(i, "C") * (Cells(i, "E") - Y) * (Cells(i, "F") - Z) * 0.000001
            Cells(i, "S") = Cells(i, "C") * (Cells(i, "D") - Y) * (Cells(i, "F") - Z) * 0.000001

        Case 174 To 181
            X = Cells(184, "D").Value
            Y = Cells(184, "E").Value
            Z = Cells(184, "F").Value

            Cells(i, "N") = Cells(i, "C") * ((Cells(i, "E") - Y) ^ 2 + (Cells(i, "F") - Z) ^ 2) * 0.000001
            Cells(i, "O") = Cells(i, "C") * ((Cells(i, "D") - X) ^ 2 + (Cells(i, "F") - Z) ^ 2) * 0.000001
            Cells(i, "P") = Cells(i, "C") * ((Cells(i, "D") - X) ^ 2 + (Cells(i, "E") - Y) ^ 2) * 0.000001
            Cells(i, "Q") = Cells(i, "C") * (Cells(i, "D") - X) * (Cells(i, "E") - Y) * 0.000001
            Cells(i, "R") = Cells(i, "C") * (Cells(i, "E") - Y) * (Cells(i, "F") - Z) * 0.000001
            Cells(i, "S") = Cells(i, "C") * (Cells(i, "D") - Y) * (Cells(i, "F") - Z) * 0.000001

        Case 193 To 197
            X = Cells(200, "D").Value
            Y = Cells(200, "E").Value
            Z = Cells(200, "F").Value

            Cells(i, "N") = Cells(i, "C") * ((Cells(i, "E") - Y) ^ 2 + (Cells(i, "F") - Z) ^ 2) * 0.000001
            Cells(i, "O") = Cells(i, "C") * ((Cells(i, "D") - X) ^ 2 + (Cells(i, "F") - Z) ^ 2) * 0.000001
            Cells(i, "P") = Cells(i, "C") * ((Cells(i, "D") - X) ^ 2 + (Cells(i, "E") - Y) ^ 2) * 0.000001
            Cells(i, "Q") = Cells(i, "C") * (Cells(i, "D") - X) * (Cells(i, "E") - Y) * 0.000001
            Cells(i, "R") = Cells(i, "C") * (Cells(i, "E") - Y) * (Cells(i, "F") - Z) * 0.000001
            Cells(i, "S") = Cells(i, "C") * (Cells(i, "D") - Y) * (Cells(i, "F") - Z) * 0.000001

        Case 209 To 263
            X = Cells(266, "D").Value
            Y = Cells(266, "E").Value
            Z = Cells(266, "F").Value

            Cells(i, "N") = Cells(i, "C") * ((Cells(i, "E") - Y) ^ 2 + (Cells(i, "F") - Z) ^ 2) * 0.000001
            Cells(i, "O") = Cells(i, "C") * ((Cells(i, "D") - X) ^ 2 + (Cells(i, "F") - Z) ^ 2) * 0.000001
            Cells(i, "P") = Cells(i, "C") * ((Cells(i, "D") - X) ^ 2 + (Cells(i, "E") - Y) ^ 2) * 0.000001
            Cells(i, "Q") = Cells(i, "C") * (Cells(i, "D") - X) * (Cells(i, "E") - Y) * 0.000001
            Cells(i, "R") = Cells(i, "C") * (Cells(i, "E") - Y) * (Cells(i, "F") - Z) * 0.000001
            Cells(i, "S") = Cells(i, "C") * (Cells(i, "D") - Y) * (Cells(i, "F") - Z) * 0.000001

        Case 275 To 321
            X = Cells(324, "D").Value
            Y = Cells(324, "E").Value
            Z = Cells(324, "F").Value

            Cells(i, "N") = Cells(i, "C") * ((Cells(i, "E") - Y) ^ 2 + (Cells(i, "F") - Z) ^ 2) * 0.000001
            Cells(i, "O") = Cells(i, "C") * ((Cells(i, "D") - X) ^ 2 + (Cells(i, "F") - Z) ^ 2) * 0.000001
            Cells(i, "P") = Cells(i, "C") * ((Cells(i, "D") - X) ^ 2 + (Cells(i, "E") - Y) ^ 2) * 0.000001
            Cells(i, "Q") = Cells(i, "C") * (Cells(i, "D") - X) * (Cells(i, "E") - Y) * 0.000001
            Cells(i, "R") = Cells(i, "C") * (Cells(i, "E") - Y) * (Cells(i, "F") - Z) * 0.000001
            Cells(i, "S") = Cells(i, "C") * (Cells(i, "D") - Y) * (Cells(i, "F") - Z) * 0.000001

        Case 333 To 357
            X = Cells(360, "D").Value
            Y = Cells(360, "E").Value
            Z = Cells(360, "F").Value

            Cells(i, "N") = Cells(i, "C") * ((Cells(i, "E") - Y) ^ 2 + (Cells(i, "F") - Z) ^ 2) * 0.000001
            Cells(i, "O") = Cells(i, "C") * ((Cells(i, "D") - X) ^ 2 + (Cells(i, "F") - Z) ^ 2) * 0.000001
            Cells(i, "P") = Cells(i, "C") * ((Cells(i, "D") - X) ^ 2 + (Cells(i, "E") - Y) ^ 2) * 0.000001
            Cells(i, "Q") = Cells(i, "C") * (Cells(i, "D") - X) * (Cells(i, "E") - Y) * 0.000001
            Cells(i, "R") = Cells(i, "C") * (Cells(i, "E") - Y) * (Cells(i, "F") - Z) * 0.000001
            Cells(i, "S") = Cells(i, "C") * (Cells(i, "D") - Y) * (Cells(i, "F") - Z) * 0.000001

        Case 369 To 378
            X = Cells(381, "D").Value
            Y = Cells(381, "E").Value
            Z = Cells(381, "F").Value

            Cells(i, "N") = Cells(i, "C") * ((Cells(i, "E") - Y) ^ 2 + (Cells(i, "F") - Z) ^ 2) * 0.000001
            Cells(i, "O") = Cells(i, "C") * ((Cells(i, "D") - X) ^ 2 + (Cells(i, "F") - Z) ^ 2) * 0.000001
            Cells(i, "P") = Cells(i, "C") * ((Cells(i, "D") - X) ^ 2 + (Cells(i, "E") - Y) ^ 2) * 0.000001
            Cells(i, "Q") = Cells(i, "C") * (Cells(i, "D") - X) * (Cells(i, "E") - Y) * 0.000001
            Cells(i, "R") = Cells(i, "C") * (Cells(i, "E") - Y) * (Cells(i, "F") - Z) * 0.000001
            Cells(i, "S") = Cells(i, "C") * (Cells(i, "D") - Y) * (Cells(i, "F") - Z) * 0.000001

        Case 390 To 396
            X = Cells(399, "D").Value
            Y = Cells(399, "E").Value
            Z = Cells(399, "F").Value

            Cells(i, "N") = Cells(i, "C") * ((Cells(i, "E") - Y) ^ 2 + (Cells(i, "F") - Z) ^ 2) * 0.000001
            Cells(i, "O") = Cells(i, "C") * ((Cells(i, "D") - X) ^ 2 + (Cells(i, "F") - Z) ^ 2) * 0.000001
            Cells(i, "P") = Cells(i, "C") * ((Cells(i, "D") - X) ^ 2 + (Cells(i, "E") - Y) ^ 2) * 0.000001
            Cells(i, "Q") = Cells(i, "C") * (Cells(i, "D") - X) * (Cells(i, "E") - Y) * 0.000001
            Cells(i, "R") = Cells(i, "C") * (Cells(i, "E") - Y) * (Cells(i, "F") - Z) * 0.000001
            Cells(i, "S") = Cells(i, "C") * (Cells(i, "D") - Y) * (Cells(i, "F") - Z) * 0.000001

        Case 408 To 463
            X = Cells(466, "D").Value
            Y = Cells(466, "E").Value
            Z = Cells(466, "F").Value

            Cells(i, "N") = Cells(i, "C") * ((Cells(i, "E") - Y) ^ 2 + (Cells(i, "F") - Z) ^ 2) * 0.000001
            Cells(i, "O") = Cells(i, "C") * ((Cells(i, "D") - X) ^ 2 + (Cells(i, "F") - Z) ^ 2) * 0.000001
            Cells(i, "P") = Cells(i, "C") * ((Cells(i, "D") - X) ^ 2 + (Cells(i, "E") - Y) ^ 2) * 0.000001
            Cells(i, "Q") = Cells(i, "C") * (Cells(i, "D") - X) * (Cells(i, "E") - Y) * 0.000001
            Cells(i, "R") = Cells(i, "C") * (Cells(i, "E") - Y) * (Cells(i, "F") - Z) * 0.000001
            Cells(i, "S") = Cells(i, "C") * (Cells(i, "D") - Y) * (Cells(i, "F") - Z) * 0.000001

        Case 475 To 490
            X = Cells(493, "D").Value
            Y = Cells(493, "E").Value
            Z = Cells(493, "F").Value

            Cells(i, "N") = Cells(i, "C") * ((Cells(i, "E") - Y) ^ 2 + (Cells(i, "F") - Z) ^ 2) * 0.000001
            Cells(i, "O") = Cells(i, "C") * ((Cells(i, "D") - X) ^ 2 + (Cells(i, "F") - Z) ^ 2) * 0.000001
            Cells(i, "P") = Cells(i, "C") * ((Cells(i, "D") - X) ^ 2 + (Cells(i, "E") - Y) ^ 2) * 0.000001
            Cells(i, "Q") = Cells(i, "C") * (Cells(i, "D") - X) * (Cells(i, "E") - Y) * 0.000001
            Cells(i, "R") = Cells(i, "C") * (Cells(i, "E") - Y) * (Cells(i, "F") - Z) * 0.000001
            Cells(i, "S") = Cells(i, "C") * (Cells(i, "D") - Y) * (Cells(i, "F") - Z) * 0.000001

        Case 502 To 514
            X = Cells(517, "D").Value
            Y = Cells(517, "E").Value
            Z = Cells(517, "F").Value

            Cells(i, "N") = Cells(i, "C") * ((Cells(i, "E") - Y) ^ 2 + (Cells(i, "F") - Z) ^ 2) * 0.000001
            Cells(i, "O") = Cells(i, "C") * ((Cells(i, "D") - X) ^ 2 + (Cells(i, "F") - Z) ^ 2) * 0.000001
            Cells(i, "P") = Cells(i, "C") * ((Cells(i, "D") - X) ^ 2 + (Cells(i, "E") - Y) ^ 2) * 0.000001
            Cells(i, "Q") = Cells(i, "C") * (Cells(i, "D") - X) * (Cells(i, "E") - Y) * 0.000001
            Cells(i, "R") = Cells(i, "C") * (Cells(i, "E") - Y) * (Cells(i, "F") - Z) * 0.000001
            Cells(i, "S") = Cells(i, "C") * (Cells(i, "D") - Y) * (Cells(i, "F") - Z) * 0.000001

        Case 526 To 527
            X = Cells(530, "D").Value
            Y = Cells(530, "E").Value
            Z = Cells(530, "F").Value

            Cells(i, "N") = Cells(i, "C") * ((Cells(i, "E") - Y) ^ 2 + (Cells(i, "F") - Z) ^ 2) * 0.000001
            Cells(i, "O") = Cells(i, "C") * ((Cells(i, "D") - X) ^ 2 + (Cells(i, "F") - Z) ^ 2) * 0.000001
            Cells(i, "P") = Cells(i, "C") * ((Cells(i, "D") - X) ^ 2 + (Cells(i, "E") - Y) ^ 2) * 0.000001
            Cells(i, "Q") = Cells(i, "C") * (Cells(i, "D") - X) * (Cells(i, "E") - Y) * 0.000001
            Cells(i, "R") = Cells(i, "C") * (Cells(i, "E") - Y) * (Cells(i, "F") - Z) * 0.000001
            Cells(i, "S") = Cells(i, "C") * (Cells(i, "D") - Y) * (Cells(i, "F") - Z) * 0.000001

        Case 539 To 542
            X = Cells(545, "D").Value
            Y = Cells(545, "E").Value
            Z = Cells(545, "F").Value

            Cells(i, "N") = Cells(i, "C") * ((Cells(i, "E") - Y) ^ 2 + (Cells(i, "F") - Z) ^ 2) * 0.000001
            Cells(i, "O") = Cells(i, "C") * ((Cells(i, "D") - X) ^ 2 + (Cells(i, "F") - Z) ^ 2) * 0.000001
            Cells(i, "P") = Cells(i, "C") * ((Cells(i, "D") - X) ^ 2 + (Cells(i, "E") - Y) ^ 2) * 0.000001
            Cells(i, "Q") = Cells(i, "C") * (Cells(i, "D") - X) * (Cells(i, "E") - Y) * 0.000001
            Cells(i, "R") = Cells(i, "C") * (Cells(i, "E") - Y) * (Cells(i, "F") - Z) * 0.000001
            Cells(i, "S") = Cells(i, "C") * (Cells(i, "D") - Y) * (Cells(i, "F") - Z) * 0.000001

        Case Else
    End Select

    For j = 1 To 26
         If j >= 7 And j <= 23 Then ' Somme colonne G à W pour les ligne TOTAL
            Cells(93, j) = Application.Sum(Range(Sheets("MCI Launch").Cells(8, j), Sheets("MCI Launch").Cells(90, j)))
            Cells(165, j) = Application.Sum(Range(Sheets("MCI Launch").Cells(102, j), Sheets("MCI Launch").Cells(162, j)))
            Cells(184, j) = Application.Sum(Range(Sheets("MCI Launch").Cells(174, j), Sheets("MCI Launch").Cells(181, j)))
            Cells(200, j) = Application.Sum(Range(Sheets("MCI Launch").Cells(193, j), Sheets("MCI Launch").Cells(197, j)))
            Cells(266, j) = Application.Sum(Range(Sheets("MCI Launch").Cells(209, j), Sheets("MCI Launch").Cells(263, j)))
            Cells(324, j) = Application.Sum(Range(Sheets("MCI Launch").Cells(275, j), Sheets("MCI Launch").Cells(321, j)))
            Cells(360, j) = Application.Sum(Range(Sheets("MCI Launch").Cells(333, j), Sheets("MCI Launch").Cells(357, j)))
            Cells(381, j) = Application.Sum(Range(Sheets("MCI Launch").Cells(369, j), Sheets("MCI Launch").Cells(378, j)))
            Cells(399, j) = Application.Sum(Range(Sheets("MCI Launch").Cells(390, j), Sheets("MCI Launch").Cells(396, j)))
            Cells(466, j) = Application.Sum(Range(Sheets("MCI Launch").Cells(408, j), Sheets("MCI Launch").Cells(463, j)))
            Cells(493, j) = Application.Sum(Range(Sheets("MCI Launch").Cells(475, j), Sheets("MCI Launch").Cells(490, j)))
            Cells(517, j) = Application.Sum(Range(Sheets("MCI Launch").Cells(502, j), Sheets("MCI Launch").Cells(514, j)))
            Cells(530, j) = Application.Sum(Range(Sheets("MCI Launch").Cells(526, j), Sheets("MCI Launch").Cells(527, j)))
            Cells(545, j) = Application.Sum(Range(Sheets("MCI Launch").Cells(539, j), Sheets("MCI Launch").Cells(542, j)))
        End If

        If j >= 3 And j <= 6 Then
            Cells(554, j) = Cells(552, j + 17) / Cells(554, "C") 'Calcul de Cdg Total Sat
            If i = 95 Or i = 167 Or i = 186 Or i = 202 Or i = 268 Or i = 326 Or i = 362 Or i = 383 Or i = 401 Or i = 468 Or i = 495 Or i = 519 Or i = 532 Or i = 547 Then
                Cells(i, j) = Cells(i - 2, j).Value ' Remplissage colonne D,E,F pour les lignes résultats SS
            End If
        End If

        If j >= 7 And j <= 12 Then ' Calcul des colonnes G à L pour les lignes résultat SS
            If i = 95 Or i = 167 Or i = 186 Or i = 202 Or i = 268 Or i = 326 Or i = 362 Or i = 383 Or i = 401 Or i = 468 Or i = 495 Or i = 519 Or i = 532 Or i = 547 Then
                Cells(i, j) = Cells(i - 2, j) + Cells(i - 2, j + 7)
            End If
        End If

         'XG,YG,ZG
            Range("C558") = Range("D554").Value
            Range("C559") = Range("E554").Value
            Range("C560") = Range("F554").Value

        If j = 21 Then 'Calcul des colonne U à Z pour les lignes résultat SS
            If i = 95 Or i = 167 Or i = 186 Or i = 202 Or i = 268 Or i = 326 Or i = 362 Or i = 383 Or i = 401 Or i = 468 Or i = 495 Or i = 519 Or i = 532 Or i = 547 Then 'Somme des masses et cdg (Colonnes C à F)
                X = Range("C558").Value
                Y = Range("C559").Value
                Z = Range("C560").Value
                Cells(i, j) = Cells(i, "C") * ((Cells(i, "E").Value - Y) ^ 2 + (Cells(i, "F").Value - Z) ^ 2) * 0.000001
                Cells(i, j + 1) = Cells(i, "C").Value * ((Cells(i, "D").Value - X) ^ 2 + (Cells(i, "F").Value - Z) ^ 2) * 0.000001
                Cells(i, j + 2) = Cells(i, "C").Value * ((Cells(i, "D").Value - X) ^ 2 + (Cells(i, "E").Value - Y) ^ 2) * 0.000001
                Cells(i, j + 3) = Cells(i, "C").Value * (Cells(i, "D").Value - X) * (Cells(i, "E").Value - Y) * 0.000001
                Cells(i, j + 4) = Cells(i, "C").Value * (Cells(i, "E").Value - Y) * (Cells(i, "F").Value - Z) * 0.000001
                Cells(i, j + 5) = Cells(i, "C").Value * (Cells(i, "D").Value - X) * (Cells(i, "F").Value - Z) * 0.000001
            End If
        End If

        If j >= 14 And j <= 19 Then ' Calcul des colonnes N à S pour les lignes résultat SS
            If i = 95 Or i = 167 Or i = 186 Or i = 202 Or i = 268 Or i = 326 Or i = 362 Or i = 383 Or i = 401 Or i = 468 Or i = 495 Or i = 519 Or i = 532 Or i = 547 Then
                Cells(i, j) = Cells(i, j - 7) + Cells(i, j + 7)
            End If
        End If

        If j = 3 Then ' Calcul masse totale sat
                    Cells(554, j) = Range("C95") + Range("C167") + Range("C186") + Range("C202") + Range("C268") + Range("C326") + Range("C362") + Range("C383") + Range("C401") + Range("C468") + Range("C495") + Range("C519") + Range("C532") + Range("C547")
        End If

        If j >= 21 And j <= 23 Then 'Calcul total et sous système invariant Sat M*Coord
                Cells(552, j) = Cells(493, j) + Cells(517, j) + Cells(530, j) + Cells(545, j) + Cells(556, j)
                Cells(556, j) = Cells(93, j) + Cells(165, j) + Cells(184, j) + Cells(200, j) + Cells(266, j) + Cells(324, j) + Cells(360, j) + Cells(381, j) + Cells(399, j) + Cells(466, j)
        End If

        If j >= 14 And j <= 19 Then ' Calcul Inertie et produit inertie Totales
                    Cells(554, j - 7) = Cells(95, j) + Cells(167, j) + Cells(186, j) + Cells(202, j) + Cells(268, j) + Cells(326, j) + Cells(362, j) + Cells(383, j) + Cells(401, j) + Cells(468, j) + Cells(495, j) + Cells(519, j) + Cells(532, j) + Cells(547, j)
        End If
    Next j
Next i

'Masse séche
Range("C562") = Range("C554") - Range("C547")

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculate

End Sub

Je vous remercie par avance pour votre aide.

Le 04/06/2013 à 19:57

Bonjour Orchyd et bienvenue sur ce forum

Malheureusement, sans fichier source c'est trop compliqué pour pouvoir optimiser, à mon gout

A+

Le 05/06/2013 à 10:10

J'ai réussi à épurer le fichier Excel afin de ne laisser que la structure du fichier.

Pour expliquer rapidement le fichier. A chaque fois q'une case est modifiée l'ensemble des cases grisées est recalculées.

Si vous avez besoin d'autres précisions n'hésitez pas.

Merci à tous pour votre aide!

Le 05/06/2013 à 10:20

Bonjour Orchyd,

Vu la complexité de la chose, optimiser le code me parait trop ardu

En revanche, je ne ferais pas une mise à jour à chaque changement de valeur,

mais je mettrais un bouton sur lequel cliquer pour le faire

A+

Le 05/06/2013 à 10:38

Merci Bruno pour ton aide!

Malheureusement l'idée du bouton n'est pas possible!

Bonne journée

Le 05/06/2013 à 10:48

Bonjour

Juste une opinion

Si dans les cellules grises, tu places directement les formules

Et à la vue de celles-ci, le calcul devrait être instantané

Le 05/06/2013 à 10:52

C'était le cas avant! Mais des opérateurs ont fait pas mal de boulettes avec les formules même si les cases sont vérouillées. Du coup on m'a demandé d'automatiser le fichier.

Le 05/06/2013 à 11:21

Bonjour à tous,

Deux autres pistes :

1) A priori, chaque modification de donnée n’a de répercutions que sur une zone bien définie.

Tu peux donc limiter le traitement sur cette zone.

Exemple : si le changement affecte la zone C8:W90, pourquoi effectuer le traitement sur les autres sous-systèmes ?

2) L’utilisation de variables tableau permet des gains de temps significatif. Par contre, c’est du boulot pour retranscrire le code.

A+

Le 05/06/2013 à 11:25

Comme je l'ai dis audébut, je suis complètement novice. Je ne sais donc pas trop comment faire pour mettre en pratique les pistes que tu m'as données Frangy!

Le 05/06/2013 à 14:51

Un exemple avec une modification dans la plage C8:W90

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Tablo
Dim i As Long
Dim PlageTraitement As Range
Dim SommeMasse As Double
    'Tu recherches la zône impactée
    If Not Application.Intersect(Target, Range("C8:W90")) Is Nothing Then Set PlageTraitement = Range("C8:W90")
    'Ensuite, tu transfères les données dans un tableau
    Tablo = PlageTraitement
    'Ensuite, tu effectues les opérations dans ce tableau
    For i = 1 To UBound(Tablo, 1) 'Nb lignes
            'ETAPE 1 :  SOMME DES MASSES
            SommeMasse = SommeMasse + Tablo(i, 1)
            'Etc ....
    Next i
    MsgBox "SOMME DES MASSES : " & SommeMasse
End Sub

A+

Le 05/06/2013 à 15:22

Merci pour ton aide! Mais,je suis désolé je n'arrive pas à appliquer ton code au mien. Je suis vraiment nulle de chez nulle en programmation...

Le 05/06/2013 à 19:06

Bonjour

Serait-il possible d'avoir le fichier avec les formules ?

Le 06/06/2013 à 09:15

Le Voici!

Le Voici!

Le 06/06/2013 à 10:04

Bonjour

Voilà où j'en suis : A tester

23orchyd-optimisation-v001.zip (78.85 Ko)
Le 06/06/2013 à 10:35

Merci beaucoup!!!! Vraiment =)

C'est exactement ce qu'il me fallait =)

Rechercher des sujets similaires à "optimiser code"