Problème de lenteur de macro

roidurif · 5 Avril 2009

Bonjour,

J'ai des problème de de lenteur pour cette macro lorsque j'ai tableau de 500 à 8000 lignes. Es t il possible de modifier cette macro pour que ce soit rapide??

Code:

Sub essai()
Dim tableau, cel As Range, i As Integer, x As Integer
tableau = Array(5, 6, 7, 28, 29, 34, 35, 49, 50, 53, 54, 55)
With Sheets("BDD")
lg = .Range("c65000").End(xlUp).Row
For x = 2 To lg
  For i = 0 To UBound(tableau)
  Set cel = .Range("C1:C" & lg).Find(.Range("c" & x), LookIn:=xlValues)
  If Not cel Is Nothing Then
    If IsEmpty(.Cells(x, tableau(i))) Then .Cells(x, tableau(i)) = .Cells(cel.Row, tableau(i))
  End If
  Next i
Next x
End With
 
End Sub

Merci d'avance

gfortin9 · 5 Avril 2009

Re : Problème de lenteur de macro

Allô!

Un début :

Code:

Sub essai()
Dim tableau, cel As Range, i As Integer, x As Integer
[COLOR="Red"]Application.ScreenUpdating = False[/COLOR]
tableau = Array(5, 6, 7, 28, 29, 34, 35, 49, 50, 53, 54, 55)
With Sheets("BDD")
......

Ça devrait aider.

GFortin

Gorfael · 6 Avril 2009

Re : Problème de lenteur de macro

Salut roidurif et le forum
Je pense qu'il faudrais travailler sur l'algorythme pour diminuer le temps de traitement.Qu'est supposé faire ta macro ?
A+

roidurif · 6 Avril 2009

Re : Problème de lenteur de macro

Bonjour

la macro fait cela :
Dans la colonne "C", il est referencé des references produit. Seulement, on peux avoir la meme reference produit saisie en plusieur fois (en double, voir triple ou plus), sans que les colonne 5, 6, 7, 28, 29, 34, 35, 49, 50, 53, 54, 55 soit remplis.

la macro va se réferer à la 1ere réference d'origine pour recopier les elements manquant et les rajouter en colonne 5, 6, 7, 28, 29, 34, 35, 49, 50, 53, 54, 55

Voila un peu le principe de fonctionnement pour cette macro. Mais malheureusement lorsque j'ai des fichiers de 500 à 8000 lignes, ça prend du tps.

En tout cas, j espere avoir été claire et je vous remercie de votre aide.

merci

Gael · 6 Avril 2009

Re : Problème de lenteur de macro

Bonsoir Roidurif, bonsoir Gorfael,

Sauf erreur de ma part, il me semble qu'il y a une erreur de logique dans ta macro.

Lorsque tu passes à la colonne suivante du tableau "Next i", tu refais la recherche avec .find alors qu'aucun paramètre n'a été modifié. C'est donc la même recherche qui donnera le même résultat et qui est inutile.

Il faut faire une nouvelle recherche seulement en changeant de ligne avec "Next x".

Essaye le code suivant où j'ai fait la modif:

Code:

Sub essai()
Dim tableau, cel As Range, i As Integer, x As Integer
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
tableau = Array(5, 6, 7, 28, 29, 34, 35, 49, 50, 53, 54, 55)
With Sheets("BDD")
lg = .Range("c65000").End(xlUp).Row
For x = 2 To lg
  Set cel = .Range("C1:C" & lg).Find(.Range("c" & x), LookIn:=xlValues)
  If Not cel Is Nothing Then
    For i = 0 To UBound(tableau)
    If IsEmpty(.Cells(x, tableau(i))) Then .Cells(x, tableau(i)) = .Cells(cel.Row, tableau(i))
    Next i
  End If
Next x
End With
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

@+

Gael

Gael · 6 Avril 2009

Re : Problème de lenteur de macro

Re, et bonsoir gfortin9 que j'ai oublié tout à l'heure 😱

J'ai fait une autre macro avec une logique complètement différente mais qui devrait marcher beaucoup plus vite. Je l'ai essayé dans ton exemple mais sur la base complète ce sera plus significatif.

Peux-tu tester le code suivant:

Code:

Sub test()
Dim tablo As Variant, tableau
Dim i As Integer, j As Integer
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
tablo = Sheets("BDD").Range("A2").CurrentRegion
Set data = New Collection
For i = 2 To UBound(tablo)
    On Error Resume Next
    data.Add i, tablo(i, 3)
Next i
tableau = Array(5, 6, 7, 28, 29, 34, 35, 49, 50, 53, 54, 55)
With Worksheets("BDD")
For i = 2 To UBound(tablo)
    For j = 0 To UBound(tableau)
    If IsEmpty(tablo(i, tableau(j))) Then .Cells(i, tableau(j)) = .Cells(data(tablo(i, 3)), tableau(j))
    Next j
Next i
End With
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

@+

Gael

tbft · 6 Avril 2009

Re : Problème de lenteur de macro

Bonjour roidurif, le fil

roidurif, serait il possible de me dire si ce code pedale assez vite,svp?

Code:

Option Explicit
Option Base 1


Sub remplir_ligne()
Dim table_dest As Variant, entree As Variant
Dim i As Integer, nb_i As Integer, j As Integer, nb_j As Integer
Dim config_calcul As Variant
  config_calcul = Application.Calculation
'  Application.Calculation = xlCalculationManual
  table_dest = Array(5, 6, 7, 28, 29, 34, 35, 49, 50, 53, 54, 55)
  entree = ActiveSheet.UsedRange.Value
  nb_i = UBound(entree, 1)
  nb_j = UBound(table_dest)
  For i = 2 To nb_i
    For j = 1 To nb_j
      If entree(i, 3) <> entree(i, table_dest(j)) Then
        ActiveSheet.Cells(i, table_dest(j)).Value = entree(i, 3)
      End If
    Next j
  Next i
  Application.Calculation = config_calcul
End Sub

je pense que je peux encore l'améliorer en diminuant le nombre d'écriture.
Mais avant de chercher plus loin je voudrais aussi savoir si il ne fait pas de connerie....

Bebere · 6 Avril 2009

Re : Problème de lenteur de macro

bonsoir Gael,Tbft,Roidurif
une autre proposition,le code est dans module 2
à bientôt

tbft · 6 Avril 2009

Re : Problème de lenteur de macro

Bonjour

j'ai essayé de réaliser une macro avec un seul accès en lecture pour récupérer les données, une double boucle de traitement, un accès en écriture pour remettre les résultats.

cela ne fonctionne que si il n'y a pas de formule dans la feuille..

Sub remplir_ligne_2()
Dim table_dest As Variant, entree As Variant
Dim i As Integer, nb_i As Integer, j As Integer, nb_j As Integer, nb_k As Integer
Dim config_calcul As Variant
config_calcul = Application.Calculation
' Application.Calculation = xlCalculationManual
table_dest = Array(5, 6, 7, 28, 29, 34, 35, 49, 50, 53, 54, 55)
entree = ActiveSheet.UsedRange
nb_i = UBound(entree, 1)
nb_k = UBound(entree, 2)
nb_j = UBound(table_dest)
For i = 2 To nb_i
For j = 1 To nb_j
If entree(i, 3) <> entree(i, table_dest(j)) Then
entree(i, table_dest(j)) = entree(i, 3)
End If
Next j
Next i
ActiveSheet.Range(Cells(1, 1), Cells(nb_i, nb_k)).Value = entree
Application.Calculation = config_calcul
End Sub

pouvez vous me dire ce que vous en penser, svp?

merci d'avance pour vos commentaires, il m'aide à progresser 🙄

roidurif · 6 Avril 2009

Re : Problème de lenteur de macro

Bonjour TBFT,

ça ne fait plus ce ke j'attendais, Là ça ne fait recopier que la colonne "C" pour la recoller au differente colonne (5, 6, 7, 28, 29, 34, 35, 49, 50, 53, 54, 55).

A vrai dire il faudrait reprendre mon precedent fichier avec ma macro pour voir le fonctionnement.

merci d avance

nolich · 7 Avril 2009

Re : Problème de lenteur de macro

Bonsoir roidurif, gfortin9, Gorfael, Gael, tbft et Bebere, bonsoir à toutes et à tous 🙂

roidurif à dit:
la macro fait cela :
Dans la colonne "C", il est referencé des references produit. Seulement, on peux avoir la meme reference produit saisie en plusieur fois (en double, voir triple ou plus), sans que les colonne 5, 6, 7, 28, 29, 34, 35, 49, 50, 53, 54, 55 soit remplis.

la macro va se réferer à la 1ere réference d'origine pour recopier les elements manquant et les rajouter en colonne 5, 6, 7, 28, 29, 34, 35, 49, 50, 53, 54, 55

Voila un peu le principe de fonctionnement pour cette macro. Mais malheureusement lorsque j'ai des fichiers de 500 à 8000 lignes, ça prend du tps.

En tenant compte de ce que tu as dit, et notamment les mots en gras-italiques, je te propose le fichier joint. Si les références identiques ne sont pas placées à la suite l'une de l'autre et que la première occurence d'une référence ne contient pas les éléments à copier, cela ne fonctionnera pas.

Mais je me suis basé sur ton fichier exemple et je prends la 1ère occurence comme référence et je regarde si la ligne suivante contient cette même référence (donc références BDD ordrées), et ainsi de suite.

Comme tbft, je suis parti sur des tableaux en ne faisant que 2 fois appel aux cellules de la feuille BDD. Cela semble fonctionner chez moi, mais teste-le quand même si tu n'as pas déjà trouvé ton bonheur.

Petit problème, les références de ta colonne C se remettent en format standard et ne sont plus en texte mais en nombre (il suffit de peu pour arranger ça), et il ne faut pas que tes cellules contiennent des formules.

Code:

[COLOR="Blue"]Sub[/COLOR] essai()
'
[COLOR="blue"]Dim[/COLOR] TabColonnes, Tablo
[COLOR="blue"]Dim[/COLOR] Cel [COLOR="blue"]As[/COLOR] Range, Ligne [COLOR="blue"]As Integer[/COLOR], Colonne [COLOR="blue"]As Integer[/COLOR]
'
  TabColonnes = Array(5, 6, 7, 28, 29, 34, 35, 49, 50, 53, 54, 55)
  Tablo = Sheets("BDD").Range("A1").CurrentRegion
  
  Ligne = 2
  [COLOR="blue"]Do While[/COLOR] Ligne < [COLOR="blue"]UBound[/COLOR](Tablo, 1)
  
    [COLOR="blue"]Do While[/COLOR] Tablo(Ligne + 1, 3) = Tablo(Ligne, 3)
      [COLOR="blue"]For[/COLOR] Colonne = 0 [COLOR="blue"]To UBound[/COLOR](TabColonnes)
        [COLOR="blue"]If[/COLOR] IsEmpty(Tablo(Ligne + 1, TabColonnes(Colonne))) [COLOR="blue"]Then[/COLOR]
          Tablo(Ligne + 1, TabColonnes(Colonne)) = Tablo(Ligne, TabColonnes(Colonne))
        [COLOR="blue"]End If[/COLOR]
      [COLOR="blue"]Next[/COLOR] Colonne
      Ligne = Ligne + 1
    [COLOR="blue"]Loop[/COLOR] [COLOR="Green"]' du Do While Tablo(Ligne + 1, 3) = Tablo(Ligne, 3)[/COLOR]
    
    Ligne = Ligne + 1
  [COLOR="blue"]Loop[/COLOR] [COLOR="green"]' du Do While Ligne <= UBound(Tablo, 1)[/COLOR]
  
  Sheets("BDD").Range("A1").CurrentRegion = Tablo
[COLOR="blue"]End Sub[/COLOR]

@+

roidurif · 7 Avril 2009

Re : Problème de lenteur de macro

Bonjour Gael, Gorfael,nolich,berbere,tbft

Merci pour vos réponses, après plusieurs tests de vos macros, les une et les autres sont biens et je m'aperçois que la plus simple et plus rapide reste celle de gael ci-dessous.

Par contre, Je rencontre des difficultés avec toutes vos macros, lorsque par exemple les formats des références ou prix sont différents de l'une et de l'autre. Par exemple une cellule en nombre stocké et l'autre autre format, ça ne fonctionne pas.

Parfois, les 2 formats sont identiques, il va recopier tous les éléments sauf le prix sans aucune compréhension.

Je ne sais pas comment m'y prendre, est ce qu'il faut lire dans la cellule et de recopier les éléments manquant, je c pas??

ci joint mon fichier

Merci de votre aide

Code:

Sub essai()
Dim tablo As Variant, tableau
Dim i As Integer, j As Integer
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
tablo = Sheets("BDD").Range("A2").CurrentRegion
Set data = New Collection
For i = 2 To UBound(tablo)
    On Error Resume Next
    data.Add i, tablo(i, 3)
Next i
tableau = Array(5, 6, 7, 28, 29, 34, 35, 49, 50, 53, 54, 55)
With Worksheets("BDD")
For i = 2 To UBound(tablo)
    For j = 0 To UBound(tableau)
    If IsEmpty(tablo(i, tableau(j))) Then .Cells(i, tableau(j)) = .Cells(data(tablo(i, 3)), tableau(j))
    Next j
Next i
End With
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Bebere · 7 Avril 2009

Re : Problème de lenteur de macro

bonjour
roidurif
essaye un copier/coller

If IsEmpty(tablo(i, tableau(j))) Then .Cells(data(tablo(i, 3)), tableau(j)).Copy Destination:=.Cells(i, tableau(j))

à bientôt

Gael · 7 Avril 2009

Re : Problème de lenteur de macro

Bonjour à tous,

Puisque ma macro a été élue 🙂🙂, ci-dessous les modifs à effectuer. Le mieux est de mettre toutes références en chaînes de caractères dans la macro (on ne touche pas aux données de la feuille Excel).

2 instructions à modifier:

Code:

data.Add i, [COLOR=red]CStr([/COLOR]tablo(i, 3)[COLOR=red])[/COLOR]

et

Code:

If IsEmpty(tablo(i, tableau(j))) Then .Cells(i, tableau(j)) = .Cells(data([COLOR=red]CStr([/COLOR]tablo(i, 3)[COLOR=red])[/COLOR]), tableau(j))

Dans ton exemple, les lignes des références en rouge ne sont pas modifiées puisque la première référence "20090033" trouvée ne contient aucune donnée, donc on ne recopie rien sur les autres lignes, de même pour "20090035".

La colonne D n'est pas non plus modifiée puisque le tableau de colonnes commence à 5 (colonne E).

@+

Gael

roidurif · 7 Avril 2009

Re : Problème de lenteur de macro

re,

Je viens de faire le test et ca ne prend pas les tarifs, malgré que j’ai convertit les nombre stocké en nombre manuellement, cela ne change rien.
Par contre, J’ai ressayé la macro de BERBERE, elle recopie toute les données malgré une lenteur quand c volumineux.
Je c plus, je transmet une autre version du tableau
Merci

Effectuez une recherche sur Excel Downloads...

Problème de lenteur de macro

roidurif

XLDnaute Occasionnel

gfortin9

XLDnaute Occasionnel

Gorfael

XLDnaute Barbatruc

roidurif

XLDnaute Occasionnel

Pièces jointes

Gael

XLDnaute Barbatruc

Gael

XLDnaute Barbatruc

tbft

XLDnaute Accro

Bebere

XLDnaute Barbatruc

Pièces jointes

tbft

XLDnaute Accro

roidurif

XLDnaute Occasionnel

nolich

XLDnaute Occasionnel

Pièces jointes

roidurif

XLDnaute Occasionnel

Pièces jointes

Bebere

XLDnaute Barbatruc

Gael

XLDnaute Barbatruc

roidurif

XLDnaute Occasionnel

Pièces jointes

Discussions similaires

Nous accordons de l'importance à votre vie privée