Macro problème avec formule concatenation
OK, ça marche !
J'ai mis à jour mon profil, je travaille sous excel 2010 Fr, l'extraction des données provient d'un logiciel qui peux extraire soit en 97 soit en 2007. Le fichier que j'avais utilisé devait être en 97-2003
Du coup, si je veux supprimer aussi la colonne B, est-ce que ça c'est correct ? Ou faut-il anticiper le fait que du coup la C va devenir B une fois la première supprimée ???
Sub Macro()
With Worksheets(" Listes des pointages")
.Columns("B").Delete
.Columns("C").Delete
'...
End With
Pour la suite, j'ai besoin, une fois la colonne B et C supprimée, de concaténer le contenu de E + F dans une nouvelle colonne (tirer la formule jusqu'en bas de chaque colonne) qu'on appellera "S1", puis concaténer F + G dans une nouvelle colonne qu'on appellera "S2", puis supprimer les colonnes sources E, F et G, sans perdre les contenus des deux nouvelles colonnes, ce qui se passe quand on laisse les formules. Dans ma macro enregistrée, j'avais copié/collé le contenu en collant uniquement les valeurs.
Est-ce qu'il faut anticiper les noms des colonnes en prenant en compte les nouveaux noms qu'elles auront ?
Et cela doit être reproduit sur chaque jour, donc, sur les 3 autres "lots" de colonnes suivants.
La partie de mon code qui décrit juste la 1ere concat c'est ça ?
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
Bonjour,
Pas si vite !
Restons sur la première question : si tu supprimes des colonnes ou des lignes, tu commences toujours par les plus éloignées, parce que le rang de celles qui suivent va se modifier à chaque suppression, mais non celles qui précèdent.
Donc il convient que tu supprimes d'abord la C puis ensuite la B ! Mais il sera de toute façon mieux de les supprimer en même temps !
.Columns("B:C").Delete[Et astreins-toi à mettre ton code cité sous balises code si tu veux qu'on puisse le lire sans fatigue inutile.]
On reviendra sur la suite de ton code, brouillé par la quantité de mouvements enregistrés..., je n'ai pu arriver que tardivement sur le Forum en raison d'une panne de courant provoquée par des travaux dans une rue voisine...
Mais puisque tu parles fort à propos d'anticiper, explique donc pourquoi tu veux mettre les 2 premières colonnes de ta triade (leur contenu concaténé) dans une nouvelle, puis concaténer le contenu de cette nouvelle avec la 3e dans une seconde nouvelle colonne !
Pour ensuite supprimer 3 colonnes et en garder 2 !
D'abord pourquoi en garder 2 ?
Ensuite pourquoi faire deux concaténations distinctes ?
Et pourquoi ces noms de S1 ou S2, alors que les noms initiaux correspondent à des plages horaires (et que la colonne non concernée par les concaténations va demeurer ainsi) ?
On viendra ensuite à la question de l'insertion de nouvelles colonnes, justifiées par le fait que tu utilises une formule pour concaténer, en utilisant l'insertion de formule dans une cellule puis la recopie incrémentée pour garnir ta colonne de formules... Très fastidieux et long, c'est tout cela qu'il faut remettre en question !
Car concaténer des données sans utiliser de formules permet de les placer dans l'une des colonnes, que l'on conservera, sans en introduire de nouvelles...
Mais avant d'en venir là, on passera par l'écriture de tes différentes phases pour voir quelle façon de les écrire aurait été meilleure à chaque stade... avant de supprimer le tout pour une méthode plus économique !
Mais il serait bon que tu répondes aux questions qui précèdent, car j'avais enregistré le fait que ton objectif était de concaténer 3 colonnes dans une, et qu'il semble donc remis en question. Je voudrais donc être sûr de savoir où je vais.
Cordialement.
Désolée si je veux aller trop vite !
Alors, pour t'expliquer mon besoin, mon logiciel me produit un fichier excel, avec, pour chaque enfant et cela chaque jour d'école, 4 créneaux:
7h30 - 8h30 (colonne F)
16h30-17h30 (G)
16h30-18h30 (H)
17h30-18h30 (I)
dans chaque colonne, quand l'enfant est inscrit, j'ai un R dans la cellule correspondante.
Au final, j'ai besoin d'imprimer des listings "simplifiés" pour les animatrices avec 3 colonnes pour noter les présences des enfants:
7h30 - 8h30
16h30-17h30
17h30-18h30
Pour avoir tous les enfants présents de 16h30 à 17h30 il faut que je concatene G + H
de 17h30 à 18h30 il faut que je concatene H+I
Donc à partir de mes 4 colonnes, il m'en restera 3, et je garde la première colonne en l'état
Je renommais les cellule en S1, ce qui signifie Soir 1 (créneau 16H30-17H30), puis S2 pour le suivant (17H30-18H30), mais ça n'a pas d'importance, c'était juste plus simple pour ne pas m'y perdre dans mes copier coller.
Est-ce que tu me suis ?
Je te suis !
Bonjour elhana24, salut MFerrand,
Au final il s'agit juste de ventiler les "R" de la colonne H en G et en I, avant de supprimer la colonne H ? Voilà donc une proposition en macro :
Sub ReporterColH()
Dim LigMax As Long, Lig As Long 'Déclaration du type de donnée à stocker dans les variables utilisées
With ActiveSheet 'Tout objet commençant par "." se rattache à la feuille active
LigMax = .Range("A" & .Rows.Count).End(xlUp).Row 'N° de la dernière ligne utilisée de la colonne A
For Lig = 1 To LigMax 'Parcourir les lignes
If .Range("H" & Lig).Value = "R" Then 'Si la cellule en H contient "R"
.Range("G" & Lig).Value = "R" 'écrire R en colonne G
.Range("I" & Lig).Value = "R" 'Idem en I
End If
Next Lig
.Columns("H").Delete
End With
End SubBonjour, Salut Pedro !
Elhana : Tes explications ont permis de constater que 2 de tes colonnes ne se chevauchent pas et peuvent être conservées, il suffit d'ajouter au deux le contenu de la colonne médiane, qui couvre les 2 plages.
C'est ce que te montre Pedro...
Mais on va poursuivre pas à pas à partir de ton enregistrement, car cela permet d'examiner des aspects d'écriture du code qui, sinon, seraient escamotés tout de suite car on n'en a pas besoin pour traiter la question.
A plus tard.
On va faire un petit pas de plus...
Ton code contient énormément de mouvements, tant par déplacements du curseur qu'au niveau de la fenêtre (Scroll) et combinés à des sélections pouvant concerner des colonnes entières. Vu le nombre on pourrait passer des jours à les analyser en détail pour cerner tous leurs effets, si une sélection ordinaire suivie d'une action ne constitue qu'un élément parasite superflu qui n'empêche pas l'opération de se réaliser, le grand nombre de mouvements combinés à des sélections incluant des cellules fusionnées introduit un risque que le déroulement à l'exécution puisse avoir des effets inattendus. A cet égard faire disparaître tous les mouvements du code (d'autant plus que certains relèvent de fausses manipulations) est indispensable pour garantir que le déroulement se fera comme on le souhaite.
On réfléchit donc !
En reprenant au point où l'on en était et en supprimant les Select, une ligne s'ajoute donc :
Sub Macro()
With Worksheets(" Listes des pointages")
.Columns("B:C").Delete
.Columns("G").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
'...
End WithElle nous amène à deux autres aspects du code enregistré :
- utilisant une méthode (ici Insert) destinée à réaliser une action, qui comporte des arguments (ou paramètres), l'enregistreur inscrit les arguments en les passant systématiquement par nom, soit le nom de l'argument, suivi de ":=", suivi de la valeur de l'argument,
or le passage normal d'arguments se fait par position en VBA (je le qualifie de normal, car il est des cas où le passage par position est obligatoire et passer par nom ne fonctionnerait pas), lequel est en outre plus pratique et réduit le code écrit, d'autant plus que l'énumération suit l'ordre positionnel des arguments,
- l'enregistreur ne distingue pas les paramètre selon que tu les as modifié ou non, il en reproduit la totalité (ou quasi), y compris ceux que tu n'as pas touché, qui sont donc laissés à leur valeur par défaut, ou même ceux qui seront ignorés selon le choix de l'option principale que tu retiens,
tu te retrouves donc avec une masse de code inutile (qui peut être considérable pour certaines méthodes) et qui te fait rédéfinir des paramètres à la valeur qu'ils ont déjà !
Il faut donc épurer tout ça : éliminer les paramètres par défaut que tu ne modifies pas. Précaution pour quelques méthodes qui conservent les valeurs de paramètres modifiés antérieurement comme Find (recherche) ou Sort (tri), pour lesquelles il faut te poser la question de savoir si le paramètre a une incidence dans ton opération et doit être maintenu ou s'il n'interviendra pas quelle que soit sa valeur, auquel cas le supprimer.
Et passer les arguments par position après cette épuration : la position doit alors être respectée et on doit marquer les emplacements des arguments inutilisés en les laissant vides...
Si une méthode comporte beaucoup d'arguments optionnels dont on ne va utiliser que les derniers de la liste, là il devient alors judicieux de passer les premiers par position, et basculer par nom pour éviter une succession importante de virgules sans rien entre qui peut être une source d'erreurs.
Il y a aussi quelque cas où pour éviter une ambiguïté d'interprétation ultérieure lorsqu'on reprendra le code il peut être préférable de passer par nom (mais ils sont rares).
Dans le cas de ton code, on peut donc supprimer l'argument CopyOrigin, à sa valeur par défaut (tu le sais, ne l'ayant pas défini explicitement !). Quant à l'argument Shift défini par la constante xlToRight, qui indique que les cellules qui suivent la plage insérée vont être déplacées vers la droite, il y a deux choses à en dire :
- les constantes VBA ont été globalement redéfinies et complétées dans les nouvelles versions d'Excel, la nouvelle est xlShiftToRight, les anciennes demeurent opérantes par souci de compatibilité...
- s'agissant d'une colonne entière (ou d'une ligne entière) insérée, cet argument (optionnel) n'a pas besoin d'être défini, il n'y a aucune cellule sous la colonne (ni aucune à la suite d'une ligne), le déplacement des cellules environnantes sera donc automatiquement réalisé par Excel (vers la droite pour une colonne insérée, vers le bas pour une ligne insérée) sans qu'on ait à le définir.
Le code simplifié ainsi devient donc :
Sub Macro()
With Worksheets(" Listes des pointages")
.Columns("B:C").Delete
.Columns("G").Insert
'...
End WithUne ligne de plus !
Tant qu'on y est un petit pas de plus...
Tu insères une formule en G3, que tu étends ensuite jusqu'à G91.
En tenant compte des simplifications déjà vues lors des pas précédents, cela va te faire ajouter 2 lignes à ton code :
Sub Macro()
With Worksheets(" Listes des pointages")
.Columns("B:C").Delete
.Columns("G").Insert
.Range("G3").FormulaR1C1 = "=CONCATENATE(RC[-2],RC[-1])"
.Range("G3").AutoFill .Range("G3:G91")
'...
End WithInsérer une formule en VBA est une chose que je ne préconises pas sauf exception, mais il est utile de savoir le faire.
L'enregistreur ne connait que la propriété FormulaR1C1 pour insérer une formule, et même d'ailleurs pour insérer une valeur ! Il vaut mieux bien sût réserver les propriétés d'insertion de formules aux formules... les car il en a d'autres : la propriété Formula, pour insérer une formule en style A1, soit de la façon dont la plupart des utilisateurs insèrent leurs formules manuellement (FormulaR1C1 correspondant à des formules en style L1C1...).
Et ces deux propriétés sont doublées par leur équivalent en FormulaLocal et FormulaLocalR1C1, permettant d'écrire les formules dans la langue utilisateur (à éviter sauf cas particuliers).
Il y a également FormulaArray pour les formules matricielles (qui en principe doivent être écrites en anglais et en style L1C1).
Tu as donc suivi l'enregistreur en codant ta formule en G3 en style L1C1. Il aurait été plus facile pour ce faire de la coder en style A1 avec Formula... mais si on y réfléchit le style L1C1, un peu plus compliqué à écrire peut présenter quelques avantages, ainsi c'est exactement la même formule que tu peux insérer dans toutes les cellules de ta colonne, soit sans avoir à passer par l'insertion dans une cellule individuelle puis sa recopie destinée à l'incrémenter ! Tu peux l'insérer d'emblée dans toutes les cellules de ta colonne.
On va donc opérer cette simplification :
Sub Macro()
With Worksheets(" Listes des pointages")
.Columns("B:C").Delete
.Columns("G").Insert
.Range("G3:G91").FormulaR1C1 = "=CONCATENATE(RC[-2],RC[-1])"
'...
End WithEt voilà une ligne de plus...
Je profite de l'occasion pour indiquer à nouveau que je considère la fonction CONCATENER d'une inutilité parfaite
A suivre...
Poursuivons un peu, quelques pas de plus :
- Tu intitules ta colonne (en G2) : S1
Il me semble préférable de conserver les horaires, indication homogène et plus parlante, mais laissons cela pour le moment.
L'enregistreur te fournit l'affectation d'une valeur à une cellule au moyen de la propriété FormulaR1C1 (il ne connait pas autre chose !
Il faut être un peu circonspect avec l'omission des propriétés par défaut, car Excel ou VBA exigent parfois qu'elles soient explicites, mais pour une seule cellule les cas où cela bloque si on l'omet sont excessivement rares, et tu peux l'omettre sans crainte. Cette façon de coder est très habituelle sinon quasi généralisée.
- Tu procèdes ensuite à l'insertion d'une nouvelle colonne, en I, soit après la 3e de tes 3 colonnes initiales...
Mais pourquoi le faire après ? On peut y réfléchir préalablement : si au contraire tu l'insères en H, la nouvelle colonne sera H, et les deux colonnes que tu conserves à la fin seront mitoyennes. Et tu peux aisément adapter ta formule : "=CONCATENATE(RC[-2],RC[1])". Le résultat sera le même.
[On va poursuivre sur le lundi, en laissant les autres jours de côté, car je ne suis pas sûr que ton code suivait bien le fil des insertions successives, avant d'en arriver aux suppressions, même en tenant compte qu'on a déjà apporté une modification en supprimant au départ 2 colonnes au lieu d'une... En tout état de cause ta façon un peu désordonnée de procéder comporte des risques d'erreurs, on y reviendra...]
- Donc on passe à la procédure de copier-coller en valeurs des 2 colonnes ajoutées avec formules, sur elles-mêmes, dans le but d'écraser les formules.
Les remarques faites précédemment à propos des paramètres de méthodes sont tout à fait d'actualité pour PasteSpecial : Opération, SkipBlanks et Transpose sont des paramètres que tu ne modifies pas, à leur valeur par défaut, qu'il est donc judicieux de supprimer.
En tenant donc compte des simplifications déjà vues précédemment et celles ci-dessus, ton code devient maintenant :
Sub Macro()
With Worksheets(" Listes des pointages")
.Columns("B:C").Delete
.Columns("G").Insert
.Range("G3:G91").FormulaR1C1 = "=CONCATENATE(RC[-2],RC[-1])"
.Range("G2") = "S1"
.Columns("H").Insert
.Range("H3:H91").FormulaR1C1 = "=CONCATENATE(RC[-2],RC[1])"
.Range("H2") = "S2"
.Range("G3:H91").Copy
.Range("G3").PasteSpecial xlPasteValues
'...
End WithIci, une halte s'impose pour un point des plus important dans l'utilisation de VBA. Le copier-coller, méthode obligatoire en manuel, est une méthode lente en VBA, et le collage spécial la rend encore beaucoup plus lente. On a donc tout intérêt à l'éviter chaque fois qu'on le peut, quand il s'agit de coller en valeurs on peut toujours l'éviter.
Comment ?
VBA permet de réaliser des affectations directes de valeurs à une cellule, comme on l'a vu, mais cela vaut aussi pour une plage de plusieurs cellules, on peut affecter une valeur à toutes les cellules d'une plage, on peut aussi affecter les valeurs d'une plage à une autre plage de même dimension, sous la forme Plage1.Value = Plage2.Value.
Et évidemment si la plage est la même cela fonctionne de la même façon.
Cette méthode échappe bien sûr à tout enregistrement, elle ne peut être réalisée manuellement...
[NB- Même lorsque l'on veut conserver la mise en forme, si celle-ci n'est pas trop complexe, il peut être avantageux de procéder au transfert de valeurs de cette façon, puis coder le rétablissement de la mise en forme.]
On va donc encore faire évoluer notre code :
Sub Macro()
With Worksheets(" Listes des pointages")
.Columns("B:C").Delete
.Columns("G").Insert
.Range("G3:G91").FormulaR1C1 = "=CONCATENATE(RC[-2],RC[-1])"
.Range("G2") = "S1"
.Columns("H").Insert
.Range("H3:H91").FormulaR1C1 = "=CONCATENATE(RC[-2],RC[1])"
.Range("H2") = "S2"
With .Range("G3:H91")
.Value = .Value
End With
'...
End WithPour terminer le lundi, on va supprimer les colonnes qui doivent, soit les 3 anciennes remplacées par les 2 insérées : elles se trouvent donc en E, F et I, selon la façon dont on a procédé.
Si on les supprime une par une, on sait qu'il faut commencer par la dernière et terminer par la première.
Mais on va essayer de les regrouper pour la suppression : on pourrait indiquer une plage multizones, par exemple : .Range("E1:F1, I1"), cibler les colonnes de cette plage avec la propriété EntireColumn en vue de les suppression. Cela fonctionne en général, mais pas avec la propriété Columns. avec cette dernière on a toutefois la ressource de les regrouper avec Union. Ce que l'on va faire :
Sub Macro()
With Worksheets(" Listes des pointages")
.Columns("B:C").Delete
.Columns("G").Insert
.Range("G3:G91").FormulaR1C1 = "=CONCATENATE(RC[-2],RC[-1])"
.Range("G2") = "S1"
.Columns("H").Insert
.Range("H3:H91").FormulaR1C1 = "=CONCATENATE(RC[-2],RC[1])"
.Range("H2") = "S2"
With .Range("G3:H91")
.Value = .Value
End With
Union(.Columns("E:F"), .Columns("I")).Delete
'...
End WithParvenus à ce point, on ne va pas répéter la même chose pour les autres jours, ni (pour l'instant) mettre en place une boucle de façon à répéter l'opération sans répéter le code sur chaque jour.
On va d'abord procéder à un examen critique en vue de simplifier encore un peu ce code...
OK, c'est bon je te suis toujours, même si j'ai relu plusieurs fois tes messages pour tout comprendre !
Pedro, "Au final il s'agit juste de ventiler les "R" de la colonne H en G et en I, avant de supprimer la colonne H ? " Oui c'est exactement ça. Et quand je vois ce que tu proposes Pedro, je vois bien que le code peut-être beaucoup simplifié
Bonjour Ehlana !
Si tu as compris ce qu'à fait Pedro, on va pouvoir avancer un tout petit peu plus vite.
Repartons de l'état auquel nous en étions arrivés. On a travaillé sur le lundi et on sait qu'il faudra répéter sur les 3 autres jours, mais auparavant penchons-nous sur les méthodes utilisées jusqu'ici :
- On a utilisé une formule de concaténation pour l'écraser ensuite au profit du résultat, mais on aurait pu fort bien concaténer sans formule et mettre directement le résultat dans la colonne.
Cellule.Value = Cellule.Offset(, -2) & Cellule.Offset(, -1) à répéter en boucle pour tout Cellule de G3:G91.
- Mais en poursuivant la réflexion on se rend compte que les valeurs de la colonne centrale étaient concaténées avec les valeurs des 2 colonnes qui l'entourent, et que la nature de ce qui est noté dans la colonne centrale fait que cela correspond à des vides dans les autres colonnes. On peut donc non seulement se passer de formule mais également se passer de concaténer, il suffit de prendre les valeurs de la colonnes et de les affecter aux cellules de même ligne de chacune des colonnes qui l'entoure.
- Faisant ainsi, il ne restera plus alors qu'à supprimer ladite colonne centrale, et l'on n'aura pas eu besoin d'insérer d'autres colonnes. On commence à multiplier les économies !
- En poussant un peu encore la réflexion, on peut se dire que n'ayant rien à insérer, on n'aura que quelques colonnes à supprimer, et qu'il peut être préférable de regrouper les suppressions pour les faire à la fin. On peut alors travailler sur des colonnes dont la position ne change pas durant les modifications qu'on apporte, ce qui limite les risques d'erreurs lors de l'écriture du code...
Appliquons ce que nous venons d'évoquer au code auquel nous étions parvenu, en le remaniant en conséquence :
Sub Macro()
Dim c As Range
With Worksheets(" Listes des pointages")
For Each c In .Range("H3:H91")
If c.Value <> "" Then
c.Offset(, -1) = c: c.Offset(, 1) = c
End If
Next c
'...
End WithOn aurait pu faire une boucle For... Next pour parcourir les lignes de 3 à 91, on testait alors .Cells(i, 8) pour mettre sa valeur si elle n'était pas vide dans .Cells(i, 7) et dans .Cells(i, 9), un peu plus long à écrire que ce que l'on a fait :
une boucle For Each... Next sur la plage colonne désignée... La variable de boucle est alors une variable objet de type Range, à chaque tour de boucle cette variable représente une cellule de la plage, on la teste et si non vide, on affecte sa valeur aux deux cellules qui l'entourent, en décalage colonne de -1 et 1 par rapport à elle.
Il ne reste donc plus qu'à introduire une boucle sur l'ensemble des colonnes à traiter de la même façon, puis supprimer toutes les colonnes en trop.
A suivre.
Bon je sais pas si c'est la fatigue du lundi ou ma journée de formation sur la chimie des silicones, mais là, j'ai du mal avec le codage des boucles
Je reprendrai tout ça à tête reposée demain !
Une question tout de même, mon nombre de lignes varie d'une semaine à l'autre, et tout en bas, j'ai des lignes de calculs à retravailler, donc à ne pas supprimer. Ou alors, recréer une autre ligne de code pour avoir le nombre d’enfants présents par créneaux (je dois additionner tous les 'R').
Mais dans ce cas comment dire à Excel de supprimer les dernières lignes si on ne sait pas à l'avance jusqu'où ira le tableau ?
On reviendra sur les dernières lignes à la fin, il faut rétablir les résultats calculés.
Mais pourquoi 4 lignes dont les deux dernières doublent les deux précédentes ?
Si ton nombre de lignes est variable il faudra le déterminer dans une variable... mais ton opération macro vise à modifier un tableau dans sa structure, cette modification n'est à faire qu'une fois. La faire par macro ne se justifie que parce qu'elle comporte une séquence répétitive fastidieuse à réaliser à la main. Mais une fois exécutée, elle n'aura plus à l'être, ton tableau aura la structure voulue. C'est une macro jetable !
[A moins que l'on te communique régulièrement ce type de tableau et que tu doive le transformer à chaque fois. Tu n'as rien précisé en la matière, il serait temps de le faire.]
Si on est dans ce dernier cas, on reprend pour rendre variable la dimension en lignes !
Sinon la suite comprend encore 3 phases : placer notre dernière boucle dans une boucle pour réaliser la même chose sur les 4 jours, supprimes les colonnes à supprimer, rétablir les bons résultats dans les lignes de totaux.
Cordialement.
Bon je sais pas si c'est la fatigue du lundi ou ma journée de formation sur la chimie des silicones, mais là, j'ai du mal avec le codage des boucles
Je reprendrai tout ça à tête reposée demain !
Une question tout de même, mon nombre de lignes varie d'une semaine à l'autre, et tout en bas, j'ai des lignes de calculs à retravailler, donc à ne pas supprimer. Ou alors, recréer une autre ligne de code pour avoir le nombre d’enfants présents par créneaux (je dois additionner tous les 'R').
Mais dans ce cas comment dire à Excel de supprimer les dernières lignes si on ne sait pas à l'avance jusqu'où ira le tableau ?
Bonsoir,
Si tu as la possibilité de travailler avec des tableaux structurés, Excel saura plus facilement jusqu'où vont tes données. Le cas inverse, il existe différentes méthodes pour délimiter une plage de travail avec VBA.
Un premier test : place toi sur une cellule de ton tableau et utilises CTRL + FLECHE (peu importe la direction) et regarde sur quelle cellule ça t’emmène. Si tu es partie d'une cellule contenant une donnée, cela t'emmène juste avant la première cellule vide rencontrée sur la direction. Le cas inverse se sera sur la première cellule non vide.
Cette action peut être reproduite via VBA :
Set DerCell = Range("A65536") 'Dernière cellule d'une feuille sur une version Excel antérieure à 2007
Set DerCell = Range("A65536").End(xlUp) 'On remonte vers le haut à partir de cette cellule jusqu'à rencontrer une cellule non vide
DerLigne = Range("A65536").End(xlUp).Row 'Si on veux juste récupérer le n° de la dernière ligne
'xlUp peut être remplacé par xlDown (vers le bas), xlToRight (vers la droite), xlToLeft (vers la gauche)On peut aussi récupérer le nombre de lignes utilisées dans la feuille (surtout utile si les données commencent à la ligne 1 et s'il n'y a pas de ligne vide)
NbLignes = UsedRange.Rows.CountSi tu ne t'intéresse qu'a certaines lignes, tu peux t'appuyer sur les formules proposées dans le tableur:
NbLignesInteret = Application.CountIf(Range("A1:A65536"),"R") 'Compte le nombre de "R" en colonne AEt c'est une liste non exhaustive des possibilités...
On est jeudi !
Petite reprise du point où on en était, on doit répéter l'opération faite en H aux colonnes L, P et T, soit un décalage de 4 colonnes par rapport à la précédente pour chacune.
Soit si on applique à la colonne H un décalage successif de 0, 4, 8, 12, on couvrira successivement nos 4 colonnes.
On va donc mettre notre procédure établie précédemment dans une boucle de 0 à 12, incrémentée par pas de 4, en prenant comme base la colonne H à laquelle on appliquera ce décalage colonne.
On a besoin d'une variable de boucle, on en déclare une de type Integer.
Sub Macro()
Dim c As Range, i%
With Worksheets(" Listes des pointages")
For i = 0 To 12 Step 4
For Each c In .Range("H3:H91").Offset(, i)
If c.Value <> "" Then
c.Offset(, -1) = c: c.Offset(, 1) = c
End If
Next c
Next i
'...
End WithVoilà un nouveau petit pas, tout simple !
Il reste qu'on définit notre colonne par "H3:H91", et que ce 91 est susceptible de varier...
J'avais introduit une détection de la plage dans ma proposition initiale en utilisant CurrentRegion. A partir de A1, cette propriété renvoyait la plage A1:V95. En diminuant son nombre de lignes de 4, on aboutissait à 91, dans une variable. Mais il convient de savoir si cette structure offre une permanence permettant de l'utiliser, et pour cela que tu répondes aux questions posées qui sont restées en suspens.
Cordialement.
Merci merci pour ces nouvelles infos...
Alors, en fait, je fais cette extraction de données toutes les fins de semaines ! D'où la nécessité de se créer une macro sympa pour faciliter le travail. Je trie ensuite toutes les présents par classe, et vient ensuite la mise en forme
Pour les lignes de fin, je ne sais pas pourquoi il y a ces doublons, c'est le logiciel de données qui extrait ces infos là, mais en l'état, rien d'exploitable, par contre elle sont fusionnées, est ce que c'est gênant pour l’exécution du code ?
La seule info intéressante que je pourrais en tirer c'est le nombre d'enfants total présents par créneau mais pour le moment pas nécessaire. Comme je le disais dans un autre poste, j'imagine qu'une fonction existe pour sommer tous les R afin d'avoir le nombre de présents ? On pourra voir ça plus tard.
Du coup, le nombre de lignes varie en fonction du nombre d'enfants présents, et ces fameuses 4 lignes de fin viennent ensuite. Donc H91 peux effectivement varier. Si on détermine par exemple qu'on aura jamais plus de 200 enfants et qu'on décide de traiter les données jusqu'en H200, est ce que ça peut fonctionner ?
Pedro, le CTRL + FLECHE m'amène sur une cellule non vide.
Voilà, bon sinon ma formation ne se termine que demain
Bonsoir,
elle sont fusionnées, est ce que c'est gênant pour l’exécution du code ?
Non, pas de la façon dont elles sont fusionnées.
j'imagine qu'une fonction existe pour sommer tous les R afin d'avoir le nombre de présents ?
Bien sûr, mais tu les reçois sans formule. Pas la peine d'en ajouter, on peut calculer et mettre le résultat dans les cellules de totaux.
Si on détermine par exemple qu'on aura jamais plus de 200 enfants et qu'on décide de traiter les données jusqu'en H200, est ce que ça peut fonctionner ?
Non ! D'une part tu as 4 lignes de totaux à la suite, d'autre part on peut savoir où ça s'arrête, donc on le fait, ce n'est pas du travail bien fait que bricoler dans l'à peu près...
Pedro, le CTRL + FLECHE m'amène sur une cellule non vide.
C'est normal. Ctrl+Fin t'amène à l'intersection de la dernière ligne utilisée et de la dernière colonne utilisée. La cellule peut avoir un contenu ou être vide, cela dépend de la disposition de l'ensemble des données.
De plus cette commande t'envoie à la fin de la zone maximale ayant été utilisée, soit même si des données ont ensuite été effacées : si tu effaces toute ta feuille, la commande t'enverra toujours à la fin de la zone anciennement utilisée. Cette zone correspond à la propriété UsedRange d'une feuille dans le code VBA.
Par contre la propriété CurrentRegion, d'une plage ou une cellule, définit la plage rectangulaire maximale à laquelle la cellule (ou plage) peut être rattachée, sans solution de continuité.
Ainsi, si j'écris ...Range("A1").CurrentRegion, dans les cellules qui entourent A1, B1 est vide mais A2 et B2 ne le sont pas, d'où extension à la ligne 2 et la colonne B, mais ces cellules sont en contacts avec d'autres, sur la ligne 2 on atteint ainsi la colonne V, et en vertical on atteint la ligne 91 par la colonne E et si E92 est vide, F92 non et ça nous amène à la ligne 95 (qu'on atteignait aussi par la colonne V). La région courante délimitée sera donc A1:V95, la ligne 96 étant vide jusqu'à W, et la colonne W vide jusqu'à la ligne 96, cela isole cette plage rectangulaire.
CurrentRegion prend en compte l'occupation actuelle, alors que UsedRange peut refléter une occupation passée.
Cordialement.
Re,
Application immédiate sur le code auquel on était arrive :
Sub Macro()
Dim c As Range, i%, dln%
With Worksheets(" Listes des pointages")
dln = .Range("A1").CurrentRegion.Rows.Count - 4
For i = 0 To 12 Step 4
For Each c In .Range("H3:H" & dln).Offset(, i)
If c.Value <> "" Then
c.Offset(, -1) = c: c.Offset(, 1) = c
End If
Next c
Next i
'...
End With- On ajoute une déclaration de variable Integer, qui nous servira à définir notre ligne variable de fin de colonne en excluant les lignes de totaux.
- On initialise la variable sur la ligne : dln = .Range("A1").CurrentRegion.Rows.Count - 4
- On remplace la définition de notre plage colonne traitée "H3:H91" par "H3:H" & dln qui l'ajustera à la dimension souhaitée au fil de ses variations.
Bonjour bonjour, alors, quand j’enchaîne les modifs de colonne puis les suppressions, ça ne marche pas, où est mon erreur ?
Sub Macro()
Dim c As Range, i%, dln%
With Worksheets(" Listes des pointages")
dln = .Range("A1").CurrentRegion.Rows.Count - 4
For i = 0 To 12 Step 4
For Each c In .Range("H3:H" & dln).Offset(, i)
If c.Value <> "" Then
c.Offset(, -1) = c: c.Offset(, 1) = c
End If
Next c
Next i
.Columns("V:T:P:L:H:C:B").Delete
End With
End SubParce que ta syntaxe n'est pas bonne !
Sub Macro()
Dim c As Range, i%, dln%, Col
With Worksheets(" Listes des pointages")
dln = .Range("A1").CurrentRegion.Rows.Count - 4
For i = 0 To 12 Step 4
For Each c In .Range("H3:H" & dln).Offset(, i)
If c.Value <> "" Then
c.Offset(, -1) = c: c.Offset(, 1) = c
End If
Next c
Next i
Col = Split("B:C H L P T")
For i = UBound(Col) To 0 Step -1
.Columns(Col(i)).Delete
Next i
.Rows(dln + 3 & ":" & dln + 4).Delete
'...
End WithAnalyse cette version...
mais j'aurais aimé que tu répondes aux questions avant...