Macro problème avec formule concatenation
Bonjour à tous,
J'ai créé une macro en enregistrant pas à pas mes actions sous excel. Je débute dans la création de macro, je ne faisais qu'en utiliser jusque là....
je vous mets en pièce jointe le tableau à partir duquel je travaille. C'est une extraction d'un logiciel de gestion de présence.
J'ai deux colonnes à concatener, G et H pour en créer une nouvelle. Il faut que je fasse cela aussi avec les colonnes H et I, ensuite que je copie/colle les valeurs pour m'affranchir des formule, puis supprimer les colonnes G, H et I. Cela doit être répété pour les 4 jours de la semaine.
Ensuite, il y a des colonnes à renommer et de la mise en forme.
Le soucis, c'est que lorsque je j'essaye de faire fonctionner ma macro, les copier/coller et la concatenation ne fonctionnent pas sur les bonnes colonnes.
je débute en macro, pouvez vous m'aider ???
voilà le code:
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft
ActiveWindow.SmallScroll ToRight:=1
ActiveWindow.ScrollColumn = 5
Columns("H:H").Select
Range("H2").Activate
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("H3").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-2],RC[-1])"
Range("H4").Select
ActiveWindow.SmallScroll Down:=-12
Range("H3").Select
ActiveWindow.SmallScroll Down:=-21
Selection.AutoFill Destination:=Range("H3:H91"), Type:=xlFillDefault
Range("H3:H91").Select
ActiveWindow.SmallScroll Down:=-108
Range("H2").Select
ActiveCell.FormulaR1C1 = "S1"
Columns("H:H").Select
Range("H2").Activate
Selection.Copy
ActiveWindow.SmallScroll ToRight:=4
Columns("M:M").Select
Range("M2").Activate
Selection.Insert Shift:=xlToRight
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.SmallScroll ToRight:=4
Columns("R:R").Select
Range("R2").Activate
Selection.Insert Shift:=xlToRight
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.SmallScroll ToRight:=5
Columns("W:W").Select
Range("W2").Activate
Selection.Insert Shift:=xlToRight
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
Columns("J:J").Select
Range("J2").Activate
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("J2").Select
ActiveCell.FormulaR1C1 = "S2"
Range("J3").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-3],RC[-1])"
Range("J3").Select
Selection.AutoFill Destination:=Range("J3:J91"), Type:=xlFillDefault
Range("J3:J91").Select
ActiveWindow.SmallScroll Down:=-96
Columns("J:J").Select
Selection.Copy
ActiveWindow.SmallScroll ToRight:=6
Columns("P:P").Select
Range("P2").Activate
Selection.Insert Shift:=xlToRight
Range("P4").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-3],RC[-1])"
Columns("P:P").Select
Selection.Copy
ActiveWindow.SmallScroll ToRight:=5
Columns("V:V").Select
Range("V2").Activate
Selection.Insert Shift:=xlToRight
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.SmallScroll ToRight:=7
Columns("AB:AB").Select
Selection.Insert Shift:=xlToRight
Columns("E:AB").Select
Range("AB1").Activate
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("H4").Select
Application.CutCopyMode = False
Columns("F:G").Select
Range("F2").Activate
Selection.Delete Shift:=xlToLeft
Columns("G:G").Select
Range("G2").Activate
Selection.Delete Shift:=xlToLeft
Columns("I:J").Select
Range("I2").Activate
Selection.Delete Shift:=xlToLeft
ActiveWindow.SmallScroll ToRight:=3
Columns("J:J").Select
Range("J2").Activate
Selection.Delete Shift:=xlToLeft
Columns("L:M").Select
Range("L2").Activate
Selection.Delete Shift:=xlToLeft
ActiveWindow.SmallScroll ToRight:=3
Columns("M:M").Select
Range("M2").Activate
Selection.Delete Shift:=xlToLeft
Columns("O:P").Select
Range("O2").Activate
Selection.Delete Shift:=xlToLeft
ActiveWindow.SmallScroll ToRight:=2
Columns("P:P").Select
Range("P2").Activate
Selection.Delete Shift:=xlToLeft
Columns("Q:Q").Select
Selection.Delete Shift:=xlToLeft
Range("O2:P3").Select
Range("P2").Activate
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.SmallScroll Down:=84
Range("N1:P91").Select
Range("P91").Activate
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.149998474074526
.PatternTintAndShade = 0
End With
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.SmallScroll Down:=87
Range("H1:J91").Select
Range("J91").Activate
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.149998474074526
.PatternTintAndShade = 0
End With
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
Range("E1:G1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Selection.UnMerge
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("H1:J1").Select
Range("J1").Activate
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Selection.UnMerge
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
ActiveWindow.SmallScroll ToRight:=4
Range("K1:M1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Selection.UnMerge
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
ActiveWindow.SmallScroll ToRight:=3
Range("N1:P1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Selection.UnMerge
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Columns("A:A").Select
With Selection
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.ColumnWidth = 30.86
Range("F3").Select
Cells.Replace What:="Mme MONNIER", Replacement:="Mme BARTHE MONNIER", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
False, ReplaceFormat:=False
Bonjour,
L'enregistreur de macro reproduit "bêtement" chacune de tes actions, ni plus, ni moins. Il est donc utile pour connaître la syntaxe de certaines instructions mais produit un code rarement intéressant ou exploitable en l'état. Quelques exemples :
- 1. Select & Activate : a proscrire !
En tant qu'utilisateur, on déplace le curseur pour sélectionner chaque cellule ou plage sur laquelle on travaille. Par exemple si on veux dire à Excel d'affecter la valeur A1 de la feuille 1 en A1 de la feuille 2, l'enregistreur écrira :
Sheets("Feuil2").Select
ActiveCell.FormulaR1C1 = "=Feuil1!RC"
Range("A2").Select
Mais VBA lui n'a pas besoin de "sélectionner" chaque cellule ni de se trouver sur une feuille pour y manipuler des éléments, du moment que les références sont bien précisées. En clair, on peut écrire :
Sheets("Feuil2").Range("A1").FormulaR1C1 = "=Feuil1!RC"
- 2. Déplacements du curseur
Tes données n'étant pas intégralement affichées à l'écran, tu as du utiliser la barre de défilement pour descendre dans ton tableur. L'enregistreur à donc enregistré chaque déplacement (ça sert à rien, mais il persiste à tout faire comme on lui montre !).
On peut donc supprimer les instructions telles que :
ActiveWindow.SmallScroll ToRight:=1
ActiveWindow.ScrollColumn = 5
- 3. Références fixes à rendre variables
Au moment où l'enregistreur a fonctionné, tu as étendu une formule de H3 à H91. Du coup, l'enregistreur à écrit :
...Range("H3:H91")...
Sauf que si ton tableau évolue, cette plage deviendra obsolète... D'où l'intérêt d'ajouter des variables dans le code :
DerniereLigne = Range("H60000").End(xlUp).Row 'Cherche le numéro de la première ligne non-vide en remontant depuis la cellule H60000
...Range("H3:H" & DerniereLigne)...
Voilà, en espérant qu'une meilleure compréhension de ces 3 points t'aidera déjà à progresser et faire évoluer ton code.
Bonsoir,
Sub RéorgListe()
Dim LP, i%, k%, n%
With Worksheets(" Listes des pointages")
LP = .Range("A1").CurrentRegion.Value
n = UBound(LP) - 4
For k = 8 To 20 Step 4
For i = 3 To n
LP(i, k) = LP(i, k - 1) & LP(i, k) & LP(i, k + 1)
Next i
For i = 1 To 3 Step 2
LP(n + i, k) = LP(n + i, k - 1) + LP(n + i, k) + LP(n + i, k + 1)
Next i
Next k
Application.ScreenUpdating = False
.Range("A1").CurrentRegion.Value = LP
For k = 21 To 7 Step -2
.Columns(k).Delete
Next k
End With
End Sub
Tu la lances à partir de la boîte de dialogue macro.
NB- Débuter un nom de feuille par une espace est à éviter, autant que possible !
Cordialement.
edit : Salut Pedro
Hello Pedro !
J'apprécie fort que tu prennes le relais de mes diatribes habituelles !
Il faut qu'ehlana se rende compte que pour progresser en VBA il faut fuir l'enregistreur dès le départ (et ne l'utiliser au cas par cas que pour détecter tel ou tel élément de syntaxe sur lequel on bute...), car le code propre à VBA qui permet de programmer des actions efficaces ne sera jamais enregistré, et que l'on n'a aucune chance d'apprendre en enregistrant.
ehlana: Le code de mon post précédent procède par extraction de ton planning en tableau, globalement, de façon à travailler plus rapidement sur ce tableau, en dehors de la feuille. Sur les 3 colonnes concernées de chacun des jours, il opère la concaténation et place le résultat dans la colonne centrale (dont l'en-tête couvre la plage horaire complète et n'est donc pas à modifier), et rétablit également les totaux dans le bas du tableau. Puis il réaffecte ce tableau à la feuille, sans copie ni collage, ce que tu ne peux faire en manuel, et que donc tu ne peux enregistrer. Il ne reste plus qu'à supprimer les 2 colonnes de chaque jour devenues superflues.
Cordialement.
Bonjour MFerrand,
tu a écrit :Il faut qu'ehlana se rende compte que pour progresser en VBA il faut fuir l'enregistreur dès le départ
oui, c'est comme un pilote d'avion de ligne en apprentissage : au bout d'un moment, faut bien qu'il déconnecte le pilote automatique, même si c'est à ses risques et périls !
dhany
Pas de panique, je n'ai suffisamment de motivation qu'une fois par jour pour écrire autant ! Quant à la mise en forme, je ne chercherais même pas à détrôner le tenant du titre !
Merci pour toutes ces réponses !
Comme je vous disais je débute en macro
Du coup, je vais lire tous vos posts mais à la première lecture, j’avoue que je suis loin d'avoir tout compris.
Si j'ai bien compris vous pensez qu'il faut tout réécrire et virer complètement ce que j'ai enregistré ? .... pas simpl, mais ça se tente.
Je reviens vers vous quand j'ai avancé...
Encore merci
Bonjour,
Si j'ai bien compris vous pensez qu'il faut tout réécrire et virer complètement ce que j'ai enregistré ?
Exactement !
Dans l'immédiat, je pense que tu devrais, après avoir testé le code dans le classeur que je t'ai retourné, vérifier si cela correspond bien ou non à ce que tu dois réaliser, si cela colle intégralement très bien, sinon dire en quoi cela ne va pas et ce qu'il conviendrait de rectifier. Ensuite, une fois ajustée sur le modèle, faire le tour des utilisations que tu prévois de cette procédure pour déterminer si des adaptations doivent y être apportées.
J'ai indiqué dans un post précédent la méthodologie sur laquelle reposait la procédure, ce qui peut te permettrait de retrouver les détails de la réalisation dans le code en l'examinant. Mais reviens alors pour en discuter de façon à bien voir le comment faire et comprendre tous les rouages... Et tu pourras aboutir au résultat souhaité en maîtrisant mieux la façon de faire.
Cordialement.
OK,
Alors, j'ai testé, il manque des choses, mais avant tout, comment je fais pour faire le ménage dans toutes les macros que j'avais pu créer avec les enregistrements, histoire de partir sur un truc clean ? J'ai ai des tas....
Il faut les reprendre manuellement, en retirant le superflu, en adaptant ajoutant des instructions pour que la macro ne soit pas obsolète en cas d'ajout ou de suppression de données...
Tu peux supprimer, sans regrets !
Tu peux toujours garder une procédure enregistrée pour qu'on puisse à titre d'exercice l'analyser ligne par ligne et voir comment cela aurait pu être écrit autrement. Ça, ce peut être un exercice didactique pour mieux se familiariser avec VBA.
Cordialement.
@MFerrand
tu as peut-être une foultitude de Modules standard dans ton VBE : tu n'en gardes qu'un.
y'a encore plus simple : appliquer la solution jmd = 0% VBA ➯ faut tout virer !
@ehlana : c'est une blague, ne le fais pas !
Tu peux supprimer, sans regrets !
voilà une phrase qui va plaire à jmd !
jmd = le spécialiste du nettoyage par le vide ! (dès qu'il s'agit de VBA)
solution « nettoyer au Kärcher » (style Sarkozy & jmd
dhany
Ne mélangeons pas tout Dhany !
Je prône une réduction drastique du nombre de modules standard car dans la très grande majorité des cas un seul suffit, que leur multiplication ne fait qu'alourdir le classeur d'un côté et ralentir le travail sur le code de l'autre, et qu'on n'en ajoute pas au petit bonheur selon l'humeur du moment mais selon justification tenant à la nature du code et à son utilisation.
Supprimer tout ce qui ne sert pas à un moment donné, c'est se garantir qu'on ne va pas traîner ad-eternam des monceaux d'inutilités. On peut mettre de côté ce qui est susceptible de servir ailleurs... mais ce n'est pas le cas du code enregistré !
J'ai d'ailleurs suggéré d'en conserver un morceau pour qu'on puisse le démonter en démontrant que pour obtenir un volume de code plus réduit et un code plus efficace (rapide à l'exécution), il convient de l'écrire autrement et faire appel à des composants insusceptibles d'être enregistrés.
Bonne journée.
j'approuve totalement tout ce que tu as écrit !
dhany
Merci ! Ça m'aurait déçu que tu n'approuves pas au moins l'essentiel de mon propos...
Au fait ! (je ne sais plus très bien quand) j'ai pris l'engagement [formel !] auprès de jmd de me mettre aux power... et consorts... lorsque je serai en mesure de les lancer en VBA !
... lorsque je serai en mesure de les lancer en VBA !
mdr !!!
J'ai d'ailleurs suggéré d'en conserver un morceau pour qu'on puisse le démonter en démontrant que pour obtenir un volume de code plus réduit et un code plus efficace (rapide à l'exécution), il convient de l'écrire autrement et faire appel à des composants insusceptibles d'être enregistrés.
Bonne journée.
Merci MFerrand ! Si tu veux bien repartir du code d'enregistrement que j'ai fait au départ, moi j'aimerai bien. J'ai lu attentivement le code proposé plus haut, j'ai compris en gros en lisant les cours de ce site, mais de là à pouvoir écrire un truc seule en partant de zéro....
Ou alors, je peux décrire ce que je souhaite faire pas à pas, et on traduit ça en VBA ?
Aujourd'hui j'ai eu ma dose de code, mais OK pour demain...
Cordialement.
Bonjour,
Démarrage en douceur pour commencer :
Sub Macro()
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft
Voilà le début de ton code. Quelques remarques et conseils préalables pour commencer :
- Une procédure (type Sub au cas particulier) commence par Sub et se termine par End Sub. Cette instruction constitue la déclaration de procédure, en citant le code d'une procédure il convient de ne pas les omettre, sans quoi le lecteur n'est pas assuré que la procédure est complète...
- Le code cité dans un post est à mettre sous balises Code (le bouton
</>
), cela le rend plus lisible et cela en conserve l'indentation. - Bien sûr le code écrit est toujours à indenter, s'il ne l'est pas cela n'empêche pas VBA de fonctionner, mais c'est celui ou celle qui travaille sur le code qui aura plus de difficulté à le faire, ne verra pas d'emblée des erreurs flagrantes, etc. et perdra beaucoup de temps...
Revenons au code : première ligne après Sub, Columns
précédé par rien... ce qui met tout de suite l'accent sur un premier défaut du code enregistré : les expressions ne sont jamais qualifiées (ou dit autrement sont dépourvues de qualificateur d'objet). Cela signifie qu'on ignore à quoi se rattache la ou les colonnes visées, on ne sait sur quelle feuille ça se trouve, l'expression ne réfère pas à son objet parent.
Il s'ensuit que VBA devra systématiquement remonter à l'objet Application, chercher quel est le classeur actif, et la feuille active dans le classeur actif pour tenter d'y rattacher la plage indiquée, ce qui sera toujours nécessairement plus lent que si on le lui avait dit dès le départ.
L'enregistreur ne fait qu'enregistrer les manipulations de l'utilisateur, soit ses sélections successives, et procède de sélection en sélection pour appliquer les commandes... Ces sélections, qui ne visent à rien d'autres qu'à pouvoir leur appliquer des commandes successives, deviennent des instructions parasites et chronophages. Cela revient à enchaîner VBA en lui faisant traîner un boulet qui ne fait que le ralentir.
On va donc qualifier notre expression en la référant à la feuille active, justifié si on entend lancer la macro avec un bouton situé sur la feuille, justifié aussi si on entend appliquer le même code à des feuilles diverses (après avoir activé la feuille à traiter), sinon en désignant la feuille à laquelle on réfère par son nom.
La macro ayant pour but une intervention unique, répétée sur plusieurs colonnes, c'est ce dernier cas qui paraît le plus logique.
L'enregistreur prend en compte la colonne en l'identifiant comme plage ("C:C") : il n'y qu'une colonne qui a été sélectionnée on pourra donc se contenter de "C" pour la désigner (voire 3, qui est son numéro de colonne).
Et on va éviter de la sélectionner, l'objectif étant de la supprimer, on le fera directement. Et comme on supprimer la colonne entière, l'on n'a pas à se soucier de la façon dont Excel va combler le vide créé, il le fera automatiquement en décalant les colonnes à droite vers la gauche, il est donc inutile de le lui dire.
En réfléchissant un peu (ce que l'enregistreur ne peut faire !
Notre code va donc démarrer ainsi :
Sub Macro()
With Worksheets(" Listes des pointages")
.Columns("C").Delete
'...
End With
NB- J'enregistre le fait que tu supprimes la colonne Etablissement, en laissant de côté la question de savoir s'il était opportun de la supprimer dès le départ ou de le faire ultérieurement...
Comme j'ai dit on démarre doucement
@+