Macro très lente sur Excel 2010
Bonjour à tout le monde,
Merci de m'accueillir sur votre forum.
J'ai un souci avec une macro que j'ai trouvé sur votre forum. Ce code me permet d'extraire des données de plusieurs feuilles dans une autre. Le voici :
[Sub BtnExec_Clic1()
Dim RechNom As String, firstAddress As String
Dim i As Byte, derlign As Long, derLignTemp As Long
Dim c As Range
RechNom = Sheets("Recherche").[as1] 'adresse du nom que l'on recherche
If RechNom = "" Then MsgBox "Pas de nom saisi", vbInformation: Exit Sub
derlign = Sheets("Recherche").[E65536].End(xlUp).Row
If derlign > 5 Then Range("E5:I" & derlign).ClearContents 'on efface la liste des résultats avant de commencer la recherche
For i = 2 To Sheets.Count 'on boucle sur toutes les feuilles à partir de la 2è
derlign = Sheets(i).Range("A65536").End(xlUp).Row
With Sheets(i).Range("A2:I" & derlign)
Set c = .Find(RechNom, LookIn:=xlValues, LookAt:=xlWhole) 'on effectue la recherche avec la méthode Find
If Not c Is Nothing Then
firstAddress = c.Address
Do
With Sheets("Recherche")
derLignTemp = .Range("E65536").End(xlUp).Row + 1
'on inscrit les résultats de la recherche dans la feuille Recherche
.Range("E" & derLignTemp) = c.Value
.Range("F" & derLignTemp) = c.Offset(, 1)
.Range("G" & derLignTemp) = c.Offset(, 2)
.Range("H" & derLignTemp) = c.Offset(, 3)
.Range("I" & derLignTemp) = c.Offset(, 4)
End With
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
Next i
End SubJ'ai copié cette macro sur un classeur qui tourne sous Excel 2010 et il est très lent, pour m'afficher les données il le fait "au compte goutte". Par contre chez moi j'utilise Excel 2013 et il tourne à merveille.
Je vous remercie de bien vouloir me dire pourquoi c'est si lent et pour votre aide.
A noter que je suis pas une pro VBA et que je ne pourrais tester ce code que demain sur Excel 2010.
Sandrine
Bonjour,
Rien dans le code pour expliquer une quelconque lenteur. Rien à optimiser !
Merci pour cette réponse.... mais c'est bien ce qui me faisait peur ! d'où peut venir ce problème alors ? Si quelqu'un pouvait la tester sous 2010 afin de savoir si cela vient simplement de la configuration de notre excel ? Et si, une idée pouvait surgir afin de savoir comment remédier au problème, en sachant que je partage ce fichier et que je ne suis pas administrateur ?
Merci pour votre aide
Je viens de m'apercevoir d'un autre souci. Je veux faire une recherche que sur le nom de famille mais finalement, cela fait une recherche sur le nom de famille et le prénom. Si j'indique 'RICHARD", j'ai un résultat sur tout les RICHARD (nom de famille) mais également "Richard" en tant que prénom. Finalement, ce code ne doit pas correspondre à mes attentes ou je me trompe quelque part ?
Merci pour votre aide
Sheets(i).Range("A2:I" & derlign)Recherche dans tous les champs de toutes les feuilles (col. A à I).
Un code est fait pour un besoin spécifique. Le récupérer pour autre chose, c'est à ses risques et périls.
Oui c'est ce que j'ai fait et l'erreur c'est effectivement corrigée. Je ne comprends pas la réflexion "Le récupérer pour autre chose, c'est à ses risques et périls". Oui OK mais je pense aussi qu'un code que l'on publie est fait pour un auto-apprentissage, je ne me contente pas d'un copier coller, j'analyse et ne comprends malheureusement pas toujours tout du premier coup.... et ma question initiale n’était d'ailleurs pas à cause de cette erreur, mais surtout sur le fait que cela est très long à s'exécuter et essayer de comprendre pourquoi afin que si d'autres néophytes comme moi sont confrontés à ce problème sachent comment le résoudre.
Merci quand même pour votre aide.
Le code n'est pas en cause en ce qui concerne la lenteur, il faut donc la chercher ailleurs...
La question de l'"auto-apprentissage" est un vaste débat... Il est quasi-certain que la grande majorité des intervenants du forum sont devenus pratiquants en VBA par une large part d'auto-apprentissage, sinon exclusivement par ce moyen. On peut donc en discuter en pleine connaissance de cause.
S'agissant d'un code récupéré, l'auto-apprentissage conduit normalement à l'analyser, décortiquer ce qu'il fait, comparer avec ce qu'on veut faire, évaluer ce qu'il ferait sur le terrain où on veut le faire agir, réécrire pour qu'il devienne exactement adapté à l'usage qu'on veut en faire. Avec un peu de chance on est tombé sur un code bien écrit (ce qui est ton cas), ce qui aide fortement pour la syntaxe (puisqu'il était fonctionnel). Mais dans tous les cas vérification et adaptation demeurent indispensables, et on ne doit jamais perdre de vue si l'on se considère en situation d'auto-apprentissage que l'objectif est d'en sortir (d'avoir appris !) donc s'habituer sans attendre à l'usage multi-quotidien de l'Aide, aux recherches ciblées, non pas pour rechercher du code prêt à l'emploi, mais élucider des points de syntaxe qu'on ne maîtrise pas encore, et lorsqu'on est en phase de passage de la conception à la réalisation sur un projet, se documenter sur les ressources qu'on pense avoir à utiliser (là les cours et tutos sur des aspects importants de programmation prennent toute leur place...)
Donc, si ton code ne remplit pas exactement la fonction que tu veux lui faire remplir, il y a un petit travail à faire pour le réadapter et réécrire les morceaux qui ne conviennent pas. Il est évident que cherchant sur 9 colonnes, il va ramasser ce qu'il trouve sur les 9 sans distinction. Tu n'avais pas vu ça et ce n'est pas ce que tu veux. Tu veux une recherche mieux ciblée. Tu définis quel ciblage pour chacun des cas qui t'intéresse, comment tu dois le formuler pour que cela puisse s'appliquer, et tu passes à la réécriture des aspects concernant le ciblage de la plage de recherche... et rencontrant des difficultés que tu ne parviens pas à résoudre, tu poses les questions sur le forum où tu ne manqueras pas d'avoir des réponses.
Cordialement.
Bonjour,
'on inscrit les résultats de la recherche dans la feuille Recherche
.Range("E" & derLignTemp) = c.Value
.Range("F" & derLignTemp) = c.Offset(, 1)
.Range("G" & derLignTemp) = c.Offset(, 2)
.Range("H" & derLignTemp) = c.Offset(, 3)
.Range("I" & derLignTemp) = c.Offset(, 4)Tu pourrais copier/coller la plage, ça te fera 1 opération de lecture/écriture au lieu de 5.
Il n'y a pas de raison qu'il y ait une différence significative entre 2010 et 2013.
Au travail tu dois travailler avec un fichier sur le réseau c'est forcément beaucoup plus long, surtout cellule par cellule.
Travaille avec une copie sur ton C: ou bien tout charger dans une variable tableau, travailler avec et tout inscrire en une fois.
eric
Merci à eriiic pour cette réponse plus constructive qu'accusatrice.....
Effectivement au travail je travaille en réseau.
Donc il faudrait que je transforme ces 5 lignes de code en une seule ? si je comprends bien. Ceci peut marcher comme ceci ?
.Range(("E" & derLignTemp) = c.Value)),("F" & derLignTemp) = c.Offset(, 1)),(("G" & derLignTemp) = c.Offset(, 2)),(("H" & derLignTemp) = c.Offset(, 3)),(("I" & derLignTemp) = c.Offset(, 4))Par contre je ne comprends pas comment "tout charger dans une variable tableau, travailler avec et tout inscrire en une fois". C'est quoi une variable tableau ?
Une fois de plus merci pour votre aide.
Il n'y avait rien d'accusateur dans les propos de MFerrand.
Juste l'expression d'une évidence : ce qui est bon pour un contexte peut s'avérer mauvais voire faux dans un autre.
Pour t'expliquer comment travailler en mémoire il vaudrait mieux du concret : un fichier avec la structure exacte, une 20aine de lignes et ce que tu veux faire.
eric
Merci pour ton aide. J'ai mis mon fichier en PJ.
Le but est que grâce sur la feuille "Recherche", je puisse retrouver les informations présentes sur toutes les feuilles de mon classeur.
Lorsque je sélectionne un nom dans ma combobox, et que je clique sur Exécuter (par la suite je vais essayer de supprimer ce bouton) toutes les informations s'affiche dans le tableau.
Je n'arrive pas non plus a supprimer les lignes vides dans ma combobox.
Tu es obligé d'utiliser plusieurs feuilles pour tes données ?
Pour un traitement efficace tout doit être dans la même feuille. Tu peux très bien filtrer ensuite sur le fichier qui t'intéresses en cas de besoin.
Ensuite un simple filtre automatique où tu utilises Rechercher dans les colonnes qui t'intéressent fait la même chose.
Autant utiliser les fonctionnalités d'excel avant de penser macros. Là je ne vois pas la plus-value.
Je suis plus ou moins obligée d'avoir plusieurs feuilles, c'est une exportation de plusieurs fichiers (chaque fichier comporte environ 3000 lignes). Il faudrait créer une autre macro qui recopie toutes les lignes sur une seule feuille ?
Oui je pourrais le faire avec un filtre, mais là n'est pas l'idée. Je veux créer un outil de recherche de personnes.
Ok, mais si tu demandes Martin tu vas en avoir 300., donc tu vas ajouter une sélection pour le prénom etc. et tu te retrouves avec la même chose que si tu l'avais fais sur la BD, avec moins de lignes c'est tout.
Si tu veux absolument mettre du code regarde si un truc comme ça ne serait pas suffisant. Juste saisir une partie du nom en A2.
Au moins, une fois le fichier chargé, tu n'es plus tributaire du réseau.
Et tu peux t'ajouter une macro pour faire les mises à jour quand tu as un nouveau fichier
eric
PS: tes dates sont en texte
Je teste dès demain et te tiens au courant. Merci dans tous les cas.
Bonjour,
Effectivement pas mal l'idée, le seul truc qui me fait peur c'est qu'une main indélicate efface les lignes..... (il va être utilisé par environ 30 personnes).
Je vais essayer de l'adapter à mon fichier en essayant de créer une macro qui exporte toutes mes feuilles sur un même classeur. Et reviens vers toi si j'ai besoin d'aide ?
Merci beaucoup.
Sandrine
Bonjour,
Quand tu fais 'Enregistrer sous...' tu déroules 'Outils' (à coté du bouton Enregistrer) et dans 'options générales...' tu peux mettre un mot de passe pour la modification.
eric
J'ai un autre souci, je voudrais que sur toutes les feuilles de mon classeur en AC, pour toutes les lignes non vides soit inscrit le nom de l'onglet, j'ai écrit ceci mais ça fonctionne pas; Pourquoi
Sub NomOnglet()
Dim i As Integer, derLign As Long
Application.ScreenUpdating = False
For i = 2 To Sheets.Count
derLign = Sheets(i).Range("A65536").End(xlUp).Row
.Range("aC2").Value = ActiveWorkbook.ActiveSheet.Name
Next i
Application.ScreenUpdating = True
End SubMerci de m'éclairer
Sandrine
Je me réponds à moi même, j'ai réussi à le faire sur la feuille active, mais pas sur toutes les feuilles. Pourtant je croyais que
For i = 2 To Sheets.Count
derLign = Sheets(i).Range("A65536").End(xlUp).Rowsélectionnait toutes mes feuilles ?
Voici comment j'ai modifié pour l'afficher sur la feuille active
Sub NomOnglet()
Dim i As Integer, derLign As Long
For i = 2 To Sheets.Count
derLign = Sheets(i).Range("A65536").End(xlUp).Row
NomFeuille = ActiveWorkbook.ActiveSheet.Name
Range("aC2:ac" & derLign) = NomFeuille
Next i
End SubQuelqu'un peut m'aider SVP ?
Merci beaucoup
NomFeuille = ActiveWorkbook.Sheets(i).Name