Optimisation boucle

Le 05/12/2019 à 18:01

Bonjour à tous,

Débutante en VBA et n'ayant pas trouvé de solution à mon problème, je me permets de vous solliciter !

Je m'explique :

Mon code permet de copier-coller les données d'un onglet "Source" jusqu'à un onglet "Destination", en fonction de références (en colonnes F et M).

Une fois ces données copiées, un traitement est réalisé (si la cellule de "Source" commence par "01." alors celle de destination vaut "...", etc.).

Mon code fonctionne.

MAIS, lorsque les données "sources" sont trop nombreuses : ça mouline jusqu'à planter.

Je suis bien consciente que mon code n'est pas optimisé (nombreux if et for each).

Mais vu toutes les conditions, je ne sais pas vraiment comment m'y prendre..

Je suis preneuse de tout conseil !

Et, je vous joins mon fichier pour plus de détails.

Je vous remercie et vous souhaite une belle soirée !

11macro-v5.xlsm (51.55 Ko)
Le 05/12/2019 à 18:07

Bonsoir,

Ce qui est long en VBA, ce sont les accès au tableur.

Pour minimiser les accès au tableur, il faut :

  • Lire dans un Array() un champ
  • Traiter cet Array()
  • Renvoyer dans le tableur l'Array() traité.

Boisgontier

Le 05/12/2019 à 21:45

Bonsoir Boisgontier,

Merci pour cette piste.

Malheureusement, je ne suis pas sûre de comprendre ce que je dois mettre dans l'array.

Est-ce l'entièreté de mes données sources ?

Le 09/12/2019 à 12:43

Bonjour à tous,

Je me permets de re-solliciter votre aide sur ce sujet.

Malgré mes recherches et essais, je n'arrive pas à optimiser mon code (qui utilise en partie des copier-collers).

Si quelqu'un pouvait m'éclairer, je suis toujours preneuse !

Merci et bel après-midi

Le 09/12/2019 à 14:48

Bonjour,

Je jette un oeil à votre fichier et je reviens avec un code révisé.

Le 09/12/2019 à 15:24

Bon...

Quelques remarques pour commencer :

  • Votre code est carrément tordu ! Vous avez cherché à faire (très) compliqué là où c'est simple
  • Il y a de nombreuses instructions inutilement répétées (du style, j'inscris une donnée dans une cellule, puis plus tard je vérifie si cette cellule n'est pas vide...)
  • Select et Activate sont inutiles, VBA n'a pas besoin de "manipuler" un élément pour le modifier
  • Copier/coller (copier A et coller en B) est plus gourmand en ressources qu'affecter directement une valeur (B = A)
  • Faire une boucle sur une plage d'une seule cellule n'a aucun intérêt ! Autant travailler directement dessus
  • Imbriquer une boucle dans une boucle démultiplie le nombre d'opérations
  • Multiplier les boucles sur une même plage est inutile, autant grouper toutes les actions dans une même boucle
  • La mise en forme peut se faire directement sur la totalité de la plage, plutôt que dans une boucle ligne par ligne

Pour éviter de vous lancer tête baissé dans des projets trop ambitieux, je vous conseille vivement de commencer par le commencement : se former progressivement à l'utilisation de VBA (via les cours sur ce site ou ailleurs). Comme tout langage informatique, ça ne s'improvise pas et VBA ne pardonne aucun écart de syntaxe (c'est cruel le codage !).

Voilà ce que je propose (non testé) :

Option Explicit
Option Base 1
Sub MacroTraitementDonnées()

Dim ligne As Long, i As Long, TabSource() As Variant, TabDestination() As Variant

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual 'Désactive le recalcul auto des formules Excel (vitesse +)

'Effacer les données de l'onglet "Destination", hors en-tête (s'il y avait déjà des données)
Sheets("Destination").Range("B2:ZZ" & Rows.Count).Clear 'Ou ClearContents ?
With ThisWorkbook.Sheets("Source")
    TabSource = .Range("A2:AH" & .Range("A" & Rows.Count).End(xlUp).Row).Value 'Chargement des données dans une variable tableau
End With
ReDim TabDestination(10000, 13)
ligne = 1
For i = 1 To UBound(TabSource)
    If TabSource(i, 13) = "AAA" And TabSource(i, 6) <> "KO" Then  'Ne prendre en compte que les lignes AAA (colonne M) non KO (colonne F)
        If TabSource(i, 12) <> "" Then TabDestination(ligne, 1) = TabSource(i, 12) Else TabDestination(ligne, 2) = "A insérer" 'Colonne L en colonne B
        TabDestination(ligne, 3) = TabSource(i, 15) 'colonne O en colonne D
        TabDestination(ligne, 4) = TabSource(i, 16) 'colonne P en colonne E
        TabDestination(ligne, 5) = TabSource(i, 26) / 1000 'colonne Z en colonne F (en km)
        Select Case Left(TabSource(i, 8), 2) * 1 'Isole le chiffre de gauche
            Case 1 'Si c'est 1, alors...
                TabDestination(ligne, 6) = 1 'Type A (col G)
                TabDestination(ligne, 9) = "Bla"
                TabDestination(ligne, 11) = "A commenter"
            Case 2
                TabDestination(ligne, 6) = 1 'Type A
                TabDestination(ligne, 9) = "BlaBla"
                TabDestination(ligne, 11) = "A commenter"
            Case 3
                TabDestination(ligne, 6) = 1 'Type A
                TabDestination(ligne, 9) = "BlaBlaBla"
                TabDestination(ligne, 11) = "A commenter"
            Case 4
                TabDestination(ligne, 8) = 1 'Type C (col I)
                TabDestination(ligne, 10) = "BlaBlaBla"
                TabDestination(ligne, 11) = "A commenter"
            Case 5
                TabDestination(ligne, 8) = 1 'Type C
                TabDestination(ligne, 10) = "BlaBlaBlaBla"
                TabDestination(ligne, 11) = "A commenter"
            Case 6
                TabDestination(ligne, 8) = 1 'Type C
                TabDestination(ligne, 10) = "BlaBlaBlaBlaBla"
                TabDestination(ligne, 11) = "A commenter"
            Case 7
                TabDestination(ligne, 8) = 1 'Type C
                TabDestination(ligne, 10) = "BlaBlaBlaBlaBlaBla"
                TabDestination(ligne, 11) = "A commenter"
            Case 8
                TabDestination(ligne, 8) = 1 'Type C
                TabDestination(ligne, 10) = "BlaBlaBlaBlaBlaBlaBla"
                TabDestination(ligne, 11) = "A commenter"
            Case 9
                TabDestination(ligne, 8) = 1 'Type C
                TabDestination(ligne, 10) = "BlaBlaBlaBlaBlaBlaBlaBla"
                TabDestination(ligne, 11) = "A commenter"
            Case 10
                TabDestination(ligne, 8) = 1 'Type C
                TabDestination(ligne, 10) = "BlaBlaBlaBlaBlaBlaBlaBlaBla"
                TabDestination(ligne, 11) = "A commenter"
            Case 11
                TabDestination(ligne, 7) = 1 'Type B (col H)
            Case Else
                TabDestination(ligne, 6) = 1 'Type A (par défaut)
                TabDestination(ligne, 11) = "Type A par défaut - A vérifier - Pas de libellé"
        End Select
        ligne = ligne + 1 'Ajout d'une ligne au tableau de destination
    End If
Next i
With ThisWorkbook.Sheets("Destination")
    .Range("B2:N" & 10000 + 1).Value = TabDestination 'Exporte le résultat (tableau destination en feuille correspondante)
    .Range("B" & ligne + 2 & ":N" & 10002).Delete 'Supprime les lignes superflues
    'Mise en forme
    .Range("B1:L" & ligne + 1).Borders.Value = 1
    .Range("B1:L" & ligne + 1).Font.Name = "Arial"
    .Range("B1:L" & ligne + 1).Font.Size = 8
    .Range("F2:F" & ligne + 1).NumberFormat = "#,##0.0"
    'Trier les données
    .Sort.SortFields.Clear
    .Sort.SortFields.Add Key:=Range("B2:B1048576"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .Sort.SortFields.Add Key:=Range("N2:N1048576"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .Sort.SortFields.Add Key:=Range("E2:E1048576"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .Sort.SortFields.Add Key:=Range("F2:F1048576"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .Sort.SetRange Range("A2:N1048576")
    .Sort.Header = xlGuess
    .Sort.MatchCase = False
    .Sort.Orientation = xlTopToBottom
    .Sort.SortMethod = xlPinYin
    .Sort.Apply
End With
Application.Calculation = xlCalculationAutomatic

End Sub

PS : la mise en forme des cas particulier n'est pas incluse dans cette macro, car facilement gérable au niveau d'Excel (par MFC : mise en forme conditionnelle).

Rechercher des sujets similaires à "optimisation boucle"