optimisation de macros

[daboss85]

Bonjour à tous,

Je travail actuellement sur une macro permettant de récupérer des informations d'un onglet vers un autre pour enrichir une table de données. ci dessous le code que j'utilise :

While Feuil1.Cells(i, 2).Value <> ""
'remplissage de territoire
Feuil1.Cells(i, 3).FormulaR1C1 = "=VLOOKUP(RC[-1],données,2,FALSE)"
If WorksheetFunction.IsNA(Feuil1.Cells(i, 3)) = True Then
Rows(i).Delete
Else
Feuil1.Cells(i, 1).Value = "44"
Feuil1.Cells(i, 1).NumberFormat = "@"
Feuil1.Cells(i, 4).FormulaR1C1 = "=VLOOKUP(RC[-2],données,3,FALSE)"
Feuil1.Cells(i, 15).FormulaR1C1 = "=VLOOKUP(RC[-13],données,14,FALSE)"
Feuil1.Cells(i, 16).FormulaR1C1 = "=VLOOKUP(RC[-14],données,15,FALSE)"
Feuil1.Cells(i, 16).NumberFormat = "m/d/yyyy"
i = i + 1
End If
Wend

En gros, tant qu'il trouve une ligne avec de l'info, il recherche les données dans la table données, si pas d'info supression, si info récupération d'autres données.

Cette macro marche très bien pour des petit volume de données mais quand eje passe sur des tables plus importantes il mets un peu plus de 25 min pour éxécuter le programme. Quelqu'un connaitrait t'il un moyen d'effectuer la mise en place des recherchev en automatique sur toute une colonne.
Ou d'effectuer une suppression en masse sans passer les élément les un après les autres.

J'espère être suffisament clair.
Merci d'avance pour vos retours

Cordialement,

 

[mutzik]

Re : optimisation de macros

bonjour,

utiliser une variable pour voir si l'info existe et non pas l'écrire dans une cellule et effacer la ligne par la suite
utiliser l'instruction find pour efféctuer ta recherche
cela devrait considérablement te raccourcir ton temps d'exécution

 

[daboss85]

Re : optimisation de macros

Merci pour ces éléments, j'ai changé par une variable mais ça ne change pas trop.

je me suis aperçu que c'est la suppression qui prends du temps. Quel serait la méthode la plus rapide pour supprimer toutes les lignes qui ont NA dans la colone C ?

 

[Gael]

Re : optimisation de macros

Bonjour Daboss85, bonjour Mutzik,

Le calcul automatique est souvent conservé et consomme beaucoup de ressources lorsque l'on modifie une feuille de calcul. As-tu pensé à mettre dans ta procédure:

Sub test()
 
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
 
Ton code
 
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
 
End sub

Sinon, pour supprimer les lignes en vrac, 2 solutions:

1 - Ajouter chaque ligne trouvée dans une variable string:

Ligsup=ligsup & "," & lig & ":" & lig
et en final:
Range(Ligsup).select
Selection.Delete Shift:=xlUp

2 - A la fin des modifs
Recalculer la feuille
Trier la feuille sur la colonne contenant l'info (par ex: colonne contenant le #NA)
supprimer en bloc toutes ces lignes en recherchant la première et dernière ligne à supprimer.

@+

Gael

 

[vbacrumble]

Re : optimisation de macros

Bonjour tous


Essayes avec cette macro

Sub macrotest()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
With Feuil1
    For i = 1 To 5000[COLOR="Green"] 'ici mettre le nombre de ligne[/COLOR]
        While .Cells(i, 2).Value <> ""
        'remplissage de territoire
        .Cells(i, 3).FormulaR1C1 = "=VLOOKUP(RC[-1],données,2,FALSE)"
        If WorksheetFunction.IsNA(.Cells(i, 3)) = True Then
        Rows(i).Delete
        Else
            .Cells(i, 1).Value = _
                "44"
            .Cells(i, 1).NumberFormat = _
                "@"
            .Cells(i, 4).FormulaR1C1 = _
                "=VLOOKUP(RC[-2],données,3,FALSE)"
            .Cells(i, 15).FormulaR1C1 = _
                "=VLOOKUP(RC[-13],données,14,FALSE)"
            .Cells(i, 16).FormulaR1C1 = _
                "=VLOOKUP(RC[-14],données,15,FALSE)"
            .Cells(i, 16).NumberFormat = _
                "m/d/yyyy"
            i = i + 1
        End If
        Wend
    Next
End With
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

 

[daboss85]

Re : optimisation de macros

Merci beaucoup,

Le calcul automatique suffit pour passer d'une heure de traitement à une minute. Pour le moment je crois que ça vas me suffir... mais je creuserais un autre jour les deux propositions pour gagner quelques secondes de plus.

Encore merci
bonne journée