trident80 | Bonjour,
Je cherche à optimiser une procédure vba sur un très gros fichier excel qui met plusieurs heures pour s'éxecuter.
J'ai dans ce fichier plusieurs feuilles relativement grosses, et je dois effectué plusieurs recherchev avec plusieurs conditions pour renseigner des colonnes dans l'onglet principal.
Le nombres de lignes à renseigner (entre 65000 et 100000 lignes) ainsi que le nombres de colonnes a renseigner (une vingtaine environ) rend cette procédure très longue (plusieurs heures )... Je souhaite donc optimiser mon code afin de gagner le plus de temps possible, j'ai effectué plusieurs recherche sur ce forum et sur d'autres et j'ai trouvé des pistes qui me sembles intéressante mais mon niveau en VBA ne me permet pas de savoir si ces pistes seront ou non adapter à mon cas, ni laquelle serait la plus rapide à s'exécuter.
Parmi les pistes que j'ai noté les deux qui me semble le plus adaptées seraient: "Les variables tableaux" et/ou "Les Dicos".
Mes questions:
Pensez-vous que ces méthodes sont adaptées à ma situation? Laquelle serait la plus efficace en temps de traitement? Pourquoi?
Existe t'il une autre méthode qui me ferait gagner plus de temps?
Selon la méthode la plus adaptée a ma situation, si vous avez des liens ou tutoriel afin d'apprendre et creuser le sujet je veux bien, sinon je chercherai...
Je vous joint ci-dessous une partie de "mon problème": une des 23 colonnes que je dois remplir. J'adapterai ensuite la solution retenue sur tout mon code, pour le test une seule colonne suffira bien!! Voici donc un exemple concret d'une des colonnes de l'onglet principal que je dois remplir et les deux méthodes que je connaissent actuellement pour y parvenir:
Fichier Excel (avec les deux sub ci-dessous intégrées): http://www.cjoint.com/c/FDcrmAtfCwY
Code 1 procedure Formule Excel:
Code :
- Sub BofBof()
- Dim Max_Ana As Long 'Déclaration variable ligne max
- Max_Ana = Sheets("Analyse" ).Cells(1048576, 2).End(xlUp).Row 'définition de la ligne max
- Application.Calculation = xlCalculationManual 'désactivation du calcul automatique
- Application.ScreenUpdating = False 'desactivation du rafraichissement
- Sheets("Analyse" ).Cells(2, 71).FormulaLocal = "=SIERREUR(SI(AZ2=""001S"";""X"";RECHERCHEV(M2;Rapport1!$A$1:$Z$3324; 9; FAUX));""Non trouvé"" )" 'copie de la formule dans la cellule BS2
- Sheets("Analyse" ).Cells(2, 71).Select 'selection de la cellule BS2
- Selection.AutoFill Destination:=Range("BS2:BS" & Max_Ana), Type:=xlFillDefault 'incrémentation de la formule jusqu'au bas de la colonne BS
- Calculate 'Calcul de la formule sur toute la colonne
- Sheets("Analyse" ).Range("BS2:BS" & Max_Ana).Copy 'Copier la colonne
- Sheets("Analyse" ).Range("BS2:BS" & Max_Ana).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False 'Coller les valeurs sans les formules
- Sheets("Analyse" ).Range("BS:BS" ).Select 'Selection de toute la colonne
- Selection.Replace What:="0", Replacement:="", LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 'Remplacer les 0 par vide
- Application.Calculation = xlCalculationAutomatic 'Activation du calcul automatique
- Application.ScreenUpdating = True 'Activation du rafraichissement
- End Sub
|
Code 2 procédure VBA:
Code :
- Sub encorebof()
- Dim i, j As Long 'lignes
- Dim Max_Ana, Max_Rap As Long 'lignes max
- Dim Ana13 As String 'reference de recherche
- Dim FlagOk As Boolean
- Max_Rap = Sheets("Rapport1" ).Range("A1048576" ).End(xlUp).Row 'Definition des lignes max
- Max_Ana = Sheets("Analyse" ).Range("B1048576" ).End(xlUp).Row
- Application.Calculation = xlCalculationManual 'désactivation du calcul automatique
- Application.ScreenUpdating = False 'desactivation du rafraichissement
-
- For i = 2 To Max_Ana
- If Sheets("Analyse" ).Cells(i, 52).Value = "001S" Then 'Si "001S" en colonne AZ de la feuille Analyse
- Sheets("Analyse" ).Cells(i, 71).Value = "X" 'Je met "X" en colonne BS de la feuille Analyse, je passe a la ligne suivante
- Else 'Si ce n'etait pas "001S" en AZ alors
- Ana13 = Sheets("Analyse" ).Cells(i, 13).Value 'je stocke la valeur de M dans ma variable Ana13
- FlagOk = False 'je met mon drapeau sur faux
- For j = 2 To Max_Rap 'J'entre dans la recherche de Ana13
- If Sheets("Rapport1" ).Cells(j, 1).Value = Ana13 Then 'Si je trouve la valeur de Ana13 dans la colonne A de ma feuille Rapport1
- Sheets("Analyse" ).Cells(i, 71).Value = Sheets("Rapport1" ).Cells(j, 9).Value 'Alors je copie ma valeur en colonne BS
- FlagOk = True 'mon drapeau passe sur vrai
- Exit For 'On sort de la recherche
- End If
- Next j
- If FlagOk = False Then 'Si le drapeau est toujours sur faux et que Ana13 n'a pas ete trouvee en colonne A de ma feuille Rapport1
- Sheets("Analyse" ).Cells(i, 71).Value = "Non trouvé" 'Alors je met "Non trouve" en BS de la feuille Analyse
- End If
- End If
- Next i
-
- Application.Calculation = xlCalculationAutomatic 'Activation du calcul automatique
- Application.ScreenUpdating = True 'Activation du rafraichissement
- End Sub
|
Je vous remerci d'avance pour vos suggestions, messages, remarques, critiques... Toute aide est la bienvenue! |