Répétition de code VBA
Bonjour le forum,
Je vous expose mon "petit" problème.
J'ai un code VBA très simple, fait avec l'enregistreur de macro, le voici :
Range("K8").Select
ActiveCell.FormulaR1C1 = "Spare Parts"
Range("K9").Select
ActiveCell.FormulaR1C1 = "Downstream technology solutions"
Range("K10").Select
Sheets("Chiffres 2017").Select
Range("E6:G7").Select
Selection.Copy
Sheets("Extract Amalia").Select
Range("C192").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Chiffres 2017").Select
Range("E23:F24").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Extract Amalia").Select
Range("C194").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Le "problème" est que j'ai ce code environ une centaine de fois dans mon module (même deux puisque tout mon code ne tiens pas, j'ai donc du dire en fin du module 1, de déclencher le module 2).
Mon module marche il n'y a pas de soucis (c'est pour faire une extraction)
Mais l'extraction prend genre 5 minutes complètes...
Et je pense qu'il y'a plus simple que de répéter un code comme un bourrin.
Si vous pouviez m'aidez
Cordialement,
Lultras
Bonjour,
Diagnostic rapide : poubelle (tout)
Tu décris exactement le problème, fournit un modèle, et il deviendra possible de l'examiner...
Ne pas fournir ce genre de code surtout !
Salut,
Malheureusement je ne peux pas fournir de modèle pour cause confidentiel...
Mais pourriez-vous me dire pourquoi je dois me débarrasser de ce code ?
y'a t-il une méthode plus simple ?
Cordialement,
Lultras
Malheureusement je ne peux pas fournir de modèle pour cause confidentiel...
Il est toujours possible de fabriquer un modèle adéquat !
Voici un exemple avec le code suivant :
Range("J2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Service 2"
Range("J3").Select
ActiveCell.FormulaR1C1 = "Region 1"
Range("J4").Select
ActiveCell.FormulaR1C1 = "DTS"
Range("L20:L21").Select
Selection.Copy
Range("P5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("R15").Select
L'idée, c'est de faire : service 1 avec région 1 puis 2 puis 3 etc... EN DTS
Puis la même chose mais en TMS.
Ensuite faire les même répétition mais en changeant de service
J'espère que cela vous suffira...
Cordialement,
Lultras
Si on ne t'a pas encore dit de mettre sous balises Code le code cité dans un post, c'est fait !
Lorsqu'une citation est une procédure, on peut le réputer incomplet s'il ne commence pas par Sub et ne se termine pas par End Sub...
En ce qui me concerne, un Select à la première ligne, fait que je ne vais de toute façon pas plus loin dans la lecture...
Et ayant exprimé que j'étais éventuellement prêt à examiner le problème, posé sur un support adéquat, en faisant abstraction de tout code défectueux, je n'ai aucune raison de modifier ce point de vue car c'est la façon la pus simple et la plus rapide pour cerner la question et concevoir des solutions.
Petite precision, je ne connait rien au VBA, plein de termes cité ci-dessus sont incompréhensible, d'où ma demande sur le forum, les choses sont déjà assez compliqué...
Vous avez demandé un support le voici.
Si vous ne pouvez pas fournir d'aide merci d'aller droit au but et de le dire en premier lieu.
bonne journée.
Sub Macro1()
'
' Macro4 Macro
'
'
Range("J2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Service 2"
Range("J3").Select
ActiveCell.FormulaR1C1 = "Region 1"
Range("J4").Select
ActiveCell.FormulaR1C1 = "DTS"
Range("L20:L21").Select
Selection.Copy
Range("P5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("R15").Select
End Sub
Vous avez demandé un support le voici.
Un support est un classeur conforme à la question posée, et permettant de l'étudier et tester les solutions... Cela a été clairement dit !
Vous dites qu'il n'est pas conforme... mais en quoi ?
Le code est le même mais sur un autre classeur.
Le code VBA est dans le classeur et entre crochet sur le forum.
Orienter moi sinon nous n'avancerons jamais.
Désolé ! Pas vu ton classeur...
C'est un peu succinct ! Questions :
Le tableau se balade toujours ainsi au milieu d'une page ?
Pas de ligne d'en-tête ?
Est-ce une extraction qui est souhaitée, en fonction des critères sélectionnés par liste déroulante ?
Apparemment un simple filtre auto suffirait pour afficher... (s'il s'agit simplement de consulter)
Si pour travailler dessus ensuite, il faut définir où disposer l'extraction.
Sous réserve d'arranger un peu les données, pas de difficulté pour obtenir n'importe quel résultat (à préciser).
Enfaite le but est de faire toutes les possibilités, services 1 avec région 1 puis 2 puis 3 ET en DTS, refaire la même chose en TMS puis ensuite refaire le même schémas pour les 4 autres services.
Dans mon fichier d'origine j'ai une base de données que je traite par des formules dans une autre feuil suivant des critères comme dans le classeur ici.
Ensuite mon code VBA me permet juste d'exercer toutes les possibilités, de copier certains resultats de mes formules puis de les collers dans une feuil spéciale qui par des formules va se charger de faire des comparaisons entre régions / services etc...
J'ai travaillé sur ta feuille, ça te modélisera quelques unes des possibilités... Il est bien évident que les différents éléments de la feuille pourraient tous se trouver sur des feuilles différentes. Une façon d'en tenir compte pour faciliter les adaptations du code consiste à nommer des plages... Les plages nommées étant des objets directement accessibles, on n'a ainsi quasiment plus à se préoccuper des feuilles où elles se trouvent.
J'ai réorganisé la feuille pour travailler un peu plus rationnellement.
La base de données commence à A1 ! J'ai déplacé les plages de listes déroulantes vers les colonnes O P Q, les cellules sous listes déroulantes sont en I J K. Je m'en sers comme zone de critères pour un filtre avancé, la zone d'affichage des données extraites ainsi est à la verticale à partir de la ligne 7.
Deux boutons : un qui permet de faire une extraction selon les critères sélectionnés dans les listes déroulantes, l'autre qui lance une procédure de listage des différentes extractions que l'on peut faire, sur une autre feuille.
Il y a 6 plages nommées : la base (nom dynamique, qui s'adapte aux variations ; l'en-tête est incluse, car elle est utilisée pour le filtrage), la zone de critères,, les 3 listes de critères et la première ligne de postionnement de l'extraction. A voir dans le gestionnaire de noms. (NB: les noms Critères et Extraire, niveau feuille sont mis automatiquement par Excel dès lors que l'on procède à un filtrage avancé, il vaut mieux ne pas les utiliser ailleurs pour éviter des confusions et des risques d'erreurs.)
Le bouton Extraction lance une procédure d'extraction des données selon les critères définis (par sélection dans les listes déroulantes).
Sub Extraction()
Application.ScreenUpdating = False
[Extrac].CurrentRegion.ClearContents
[Base].AdvancedFilter xlFilterCopy, [Crit], [Extrac]
End Sub
Comme on le voit, c'est pas volumineux
Cela fait partie des fonctionnalités d'Excel qui sont plus simples à utiliser en VBA qu'à opérer manuellement sur la feuille. On doit même pouvoir arriver à écrire la macro, la lancer plus rapidement que la procédure manuelle...
Je dois m'interrompre un petit moment, je reviens pour la suite...
Ce type de procédure s'imposait presque, dans le contexte car parfaitement adapté aux éléments mis en place...
Du coup, elle est réutilisée pour le listage, alors que d'autres méthodes sont possibles... Tant que celle-ci s'avère suffisamment rapide, il n'y a pas de raison importante d'en changer, si elle se révélait prendre un peu trop de temps (base importante), on pourrait essayer dans un premier temps de l'accélérer en différant l'affectation des extractions successives pour ne les affecter que globalement à la fin, et dans un second opérer par tableau à partir d'un tri de la base qui la mettrait dans l'ordre adéquat...
Le bouton Listage lance la procédure ci-dessous :
Sub ListerParCritères()
Dim a%, b%, c%, ni%, n%, ws As Worksheet
'Set ws = Worksheets.Add(after:=ActiveSheet)
Set ws = ActiveSheet.Next
n = ws.Cells(.Rows.Count, 1).End(xlUp).Row
ws.Range("A1:C" & n).ClearContents
ws.Range("A1:C1").Value = [Base].Resize(1).Value: n = 3
Application.ScreenUpdating = False
For a = 1 To [Crit1].Rows.Count
For b = 1 To [Crit2].Rows.Count
For c = 1 To [Crit3].Rows.Count
With [Crit]
.Cells(2, 1) = [Crit1].Cells(a, 1)
.Cells(2, 3) = [Crit2].Cells(b, 1)
.Cells(2, 2) = [Crit3].Cells(c, 1)
End With
Extraction
With [Extrac].CurrentRegion
ni = .Rows.Count - 1
If ni > 0 Then
ws.Cells(n, 1).Resize(ni, 3).Value = .Offset(1) _
.Resize(ni).Value
n = n + ni + 1
End If
End With
Next c
Next b
Next a
ws.Activate
End Sub
Cela reste de taille très très raisonnable
On est toutefois assez loin de ce code qui t'oblige à utiliser deux modules, évoqué dans tes propos initiant le sujet... j'avoue ne pas avoir bien compris de quoi il retournait... Quoi qu'il en soit, en VBA on évite toujours des répétitions indéfinies, boucles et variables le permettent généralement, et c'est dès la première répétition qu'il faut réagir et se dire que c'est une fois de trop...
C'est un peu pareil avec les formules excel, je suis toujours effaré quand je vois une formule qui arrive à occuper une dizaine de lignes ! En ce qui me concerne, si je me vois dépasser la 3e ligne, je m'arrête, c'est que la méthode n'est pas bonne, et qu'il faut réfléchir pour en trouver une meilleure et plus économique, et si pas trouvé dans Excel passage à VBA...
La méthode de la procédure est simple, 3 boucles imbriquées qui procèdent successivement à toutes les combinaisons des 3 critères, à chaque combinaison une extraction, que l'on affecte à sa position finale sur une autre feuille. Il faut noter que l'on ne procède pas par copier-coller mais par affectation de valeurs.
Quasiment tous les demandeurs ont comme réflexe de chercher à faire des copies et du collage... Mauvais réflexe en VBA, très mauvais même, car on continue de penser qu'on opère manuellement dans Excel, alors que VBA dispose d'autres moyens qui permettent justement de faire autrement, et généralement plus vite ! Avant de se lancer dans un copier-coller, il faut donc toujours se demander si l'on ne peut pas faire autrement (on peut toujours en général) et peser les avantages et inconvénients de chaque méthode et choisir en fonction.
Une ligne de code est invalidée en début de procédure (apostrophe placée devant), elle pemet de lister sur une nouvelle feuille insérée par la procédure. Si on utilise cette option, il faut enlever l'apostrophe, mais par contre en mettre devant les 3 lignes qui suivent (qui procèdent à l'effacement de la feuille suivante pour y réinscrire une nouvelle liste).
Cordialement.
Tout d’abord, merci beaucoup pour tes explications, c'est vrai que en comparant ton code et mon code, il n'y a pas photos... ^^
Si j'ai utilisé 2 modules c'est tout simplement car le premier module était déjà plein... ici je te laisse imaginer les répétitions et la taille de mon code...
Je te répond que ce matin je n'avais pas accès à mon PC hier soir, mais j'ai testé ce matin ton fichier avec ton code, et Wao, le bouton extraction est vraiment très intéressant, je me demandais (sans vouloir être lourd), si on pouvais sortir 3/4 colonne de la base de donnée dans une feuil ? Ici on sort a/b/c, peut-on rajouter E/F/G/H, par exemple pour que 3 formule calcul mes dossier envoyer à temps, le montant des dossiers etc...
Ton extraction est vraiment efficace, rapide et je pense que pour le "peu" de possibilités que j'ai avec mon fichier de base, on pourrai vite obtenir un truc simple et rapide.
Quoi qu'il en sois, merci beaucoup pour ton aide, c'est vraiment un travaille propre !
Lultras
Bonjour,
Je pencherais plutôt pour un problème de longueur de procédure (il y a effectivement une limite à ce niveau), car s'agissant du module lui-même, je n'ai jamais atteint de saturation et j'en de bien chargés...
Il est possible d'adapter à plus de colonnes, mais ce qui va allonger la durée d'exécution c'est le nombre de combinaisons de critères. Dans le modèle les 3 séries de critères conduisent à 90 combinaisons. Pour l'extraction individuelle, cela ne change rien si on augmente, pour les lister toutes, il sera sans doute préférable de passer à une autre méthode selon le nombre de combinaisons...
Dans la mesure où un tri exhaustif, sur tous les critères, conduirait à ordonner la base identiquement à la liste qu'on compose, on gagnerait certainement du temps en éliminant l'extraction par combinaison de critères.
Par exemple ne serait il pas plus simple d'inclure les formules dans chaque possibilité, et d'extraire uniquement les résultat ?
Par exemple, si service 1 avec région 1 et DTS, faire calcul 1 et 2 et 3 et les placer ici.
Refaire ça, pour service 1 région 2 DTS etc... ?
Pour info les colonnes que je voulais rajouter ne sont pas des critères, mais juste mes plages pour calcul
Par exemple ne serait il pas plus simple d'inclure les formules dans chaque possibilité
Bonjour désolé pour ma réponse tardive !
Quand je dit formule c'est quelque chose de plutôt complexe avec plusieurs lignes...mais en y réfléchissant bien, dans mon fichier j'ai déjà les formules et les résultats qui vont avec. Ma macro copie et colle les résultat après chaque possibilité de sélection
Après je ne suis pas expert mais si vous me dites qu'il faut faire de tels ou tels façons, je veux bien vous croire
lultras
Comme dans le fichier, pas de formule, je ne vois pas très bien de quoi tu parles... Voilà toujours une autre façon de procéder au même listage que précédemmant :
Sub Lister2()
Dim n%, i%, ws As Worksheet
Set ws = ActiveSheet.Next
n = ws.Cells(Rows.Count, 1).End(xlUp).Row
ws.Range("A1:C" & n).ClearContents
With [Base]
.Sort key1:=.Cells(1, 1), order1:=xlAscending, key2:=.Cells(1, 3), order2:=xlAscending, _
key3:=.Cells(1, 2), order3:=xlAscending, Header:=xlYes
n = .Rows.Count
ws.Range("A1").Resize(n, 3).Value = .Value
End With
With ws
For i = n To 2 Step -1
If .Cells(i - 1, 1) <> .Cells(i, 1) Or .Cells(i - 1, 2) <> .Cells(i, 2) Or _
.Cells(i - 1, 3) <> .Cells(i, 3) Then .Cells(i, 1).Resize(, 3).Insert xlShiftDown
Next i
.Activate
End With
End Sub
Cordialement.