Optimiser une macro

[ALKER]

Bonjour tt le monde,

J'ai créé une macro qui fait une recherche verticale sur deux tableaux de deux feuilles.

Mon problème est que dans les tableaux j'ai énormément de ligne, presque le maximum sur Excel 2003, donc le programme peut mettre plus de deux heures pour finir.

J'aimerai savoir si on peut l'optimiser le programme ? Je pense que c'est les deux boucle For qui ralentie le programme. Comme je ne m'y connais pas en programmation je ne sais pas quoi utiliser.La macro est ci-dessous.

Avez vous des idées SVP?

Merci

Sub rechv()

Worksheets("Default").Activate
Dim ligne1 As Long
ligne1 = Columns("A:A").Find("*", Range("A1"), , , xlByRows, xlPrevious).Row


Dim ligne2 As Long
ligne2 = Columns("A:A").Find("*", Range("A1"), , , xlByRows, xlPrevious).Row

For i = 2 To ligne1

For j = 2 To ligne2

If Cells(i, 1) = Worksheets("Feuil2").Cells(j, 1) Then

Cells(i, 2) = Worksheets("Feuil2").Cells(j, 10)
Exit For

End If
Next j
Next i

End Sub

 

[gwenlorin]

Re : Optimiser une macro

Bonjour,

pour accélerer l'exécution tu peux ajouter en début de procédure :

Application.screenupdating = false

et en fin

Application.screenupdating = True

Ca bloque la mise à jour de l'affichage sur Excel.

sinon pour ta recherche de valeur, plutôt que de balayer toutes les cellules (surtout si grand nombre) je te conseille une recherche plus directe avec Find :

ci-dessous un exemple issu du site Ce site n'existe plus

'recherche un mot(ou un groupe de mots) ou une valeur
'dans l'ensemble des feuilles du classeur actif
'signale l'emplacement des valeurs trouvées par un message
'recherche la correspondance exacte avec la chaine ou valeur demandée

Sub SearchAllSheets()
'JE McGimpsey, mpep

Dim strSearchString As String
Dim ws As Worksheet
Dim foundCell As Range
Dim returnValue As Variant
Dim loopAddr As String
Dim countTot As Long
Dim counter As Long

  strSearchString = InputBox(Prompt:= _
    "Enter a title or other value to search for.", _
    Title:="Search Workbook")
        
  For Each ws In Worksheets
    countTot = countTot + Application.CountIf( _
                ws.UsedRange, "=" & strSearchString)
  Next ws
  
  If countTot = 0 Then
    MsgBox strSearchString & " not found."
  Else
   counter = 0
    For Each ws In Worksheets
      With ws
        .Activate
        Set foundCell = .Cells.Find( _
            What:=strSearchString, _
            LookIn:=xlValues, _
            LookAt:=xlPart)
        If Not foundCell Is Nothing Then
          loopAddr = foundCell.Address
          Do
            counter = counter + 1
            foundCell.Activate
            returnValue = MsgBox("Found '" & strSearchString & _
                  "' at " & foundCell.Address & vbNewLine & _
                  "(" & counter & " of " & countTot & ")", _
                  vbOKCancel)
            If returnValue = vbCancel Then Exit For
            Set foundCell = .Cells.FindNext( _
                  After:=foundCell)
          Loop While Not foundCell Is Nothing And _
                  foundCell.Address <> loopAddr
        End If
      End With
    Next ws
  End If

End Sub 'SearchAllSheets

 

[Ubot303]

Re : Optimiser une macro

Bonjour à toi,

L'optimisation d'une telle routine passe par la remise en cause de la routine en elle-même 😉

Je m'explique tu fais une recherche sur 2 tableaux avec une double boucle... pour faire quoi exactement?

Si c'est retraiter tous les caractères *, l'option edition -> remplacer est ton amie.

Au vu de ton code, tu prend la cells(i,1) puis tu parcours le 2eme tableau.
Si tu trouves une correspondance, tu notes en colonne 2 et tu sors de la boucle.

Personnellement, je ne passerai pas par une macro pour traiter ce genre de problématique sur un gros fichier. Mais plutot par une recherchev sur chacun des tableaux pour un contrôle de double cohérence.
Excel est plus optimisé pour ca 😉

Cordialement

 

[ALKER]

Re : Optimiser une macro

Bonjours

Il me le faut en macro car ici ce n'est qu'une toutes petite partie de la macro.

Le but du programme c'est si cells(i,1)= cells(j,1) du 2eme tableau.
Alors je récupère plusieurs données du tableau 2 que insert dans le tableau 1.

Concrètement, dans un tableau j'ai des données financière (prix de la commande pays client...) dans l'autre j'ai des données technique sur la commande . Je dois récupérer le prix, le pays et le nom du client pour les introduire dans le tableau technique. Grâce a la référence de la commande.

Pouvez vous m'expliquer la fonction Find car je ne voie pas comment l'utiliser?

merci

 

[pierrejean]

Re : Optimiser une macro

bonjour a tous

Il est probable que l'on puisse accelerer quelque peu la macro en utilisant des tableaux
Encore faudrait-il avoir une idée de ce qu'ils sont et des transferts a effectuer
Un fichier exemple nous en dirait infiniment plus (quelques lignes quelques colonnes et des indications non confidentielles)

 

[ALKER]

Re : Optimiser une macro

Voila l'Excel.
J'espère que vous allez mieux comprendre.
merci

 

[gwenlorin]

Re : Optimiser une macro

Re,

je rejoins Ubot303. Pour ta demande un simple recherchev suffit
ci-joint le fichier modifié

 

[pierrejean]

Re : Optimiser une macro

Re

Les 3 colonnes en jaune sont au nombre de 4 !
La macro fait reference a un feuille defaut qui n'existe pas !
La precipitation n'accelere jamais le processus
Peux-tu nous preciser si le but est bien
de recuperer en Feuil2 les Name Country ...et les positionner dans la feuil1
Si oui ou mettre Ordering date et Net euro ?

 

[ALKER]

Re : Optimiser une macro

oui c'est ça PierreJean.
Il n'y a pas de feuille défaut car pour l'exemple j'ai créé un nouveau excel, désolé

Ordering date correspond a crééle et Net euro c'est ValEuro.
je travail tellement dessus que je ne m'étais pas rendu compte que les nom des colonne n'était pas les même.
encore désolé.

 

[pierrejean]

Re : Optimiser une macro

Re

En supposant avoir bien compris

tester la macro test1 (dans Module1: module standard preferable au module Thisworkbook)
Mais je pense egalement comme gwenlorin et ubot303 que des formules feraient aussi bien

 

[Ubot303]

Re : Optimiser une macro

Dans 35% des cas, nous avons tous tendance à aller vers la macro.
J'insiste sur le fait que la recherchev est une fonction de base de notre tableur adoré.
Je viens de faire un test sur un plage de données de plusieurs dizaines de milliers de lignes, la vitesse d'une double boucle For imbriqué est d'environ 15 fois moins que celle de la réactualisation d'une formule.
Tu peux surement demarrer tes autres traitements une fois la recherchev actualisée...
Au pire tu peux recalculer ta feuille et ta recherchev au lancement de ta macro puis y inclure les traitements ensuite.

Cordialement 😉

 

[ALKER]

Re : Optimiser une macro

Merci pierrejean,
C'est bien ça que je veux.
Oui je c que c aussi rapide de faire =recherchev() mais on m'a demandé de tt automatisé! je ne suis qu'un stagiaire.
Merci à tout le monde pour votre aide.