Comment insérer des lignes/colonnes et que les macros se mettent à jour?

[dwidou]

Bonjour,
Lorsque j'insère des lignes/colonnes/cellules dans mes feuilles, les macros déjà créées et ayant des références de plage déjà, ne se mettent pas à jour!!! du coup je dois reprendre 1 par 1 tyoutes les macros et mettre à jour à la main le code!!!

Possible que cela soit automatique?

Merci d'avance pour votre aide.

 

[Excel-lent]

Re : Comment insérer des lignes/colonnes et que les macros se mettent à jour?

Bonjour et bienvenu sur le forum Dwidou,

N'hésite pas à mettre une pièce jointe exemple (sans données confidentielles) pour illustrer ta question et mieux montrer ton attente. Ne pas hésiter à lire Lien supprimé

Dans le cas présent, comment savoir ce que tu as fais? Savoir d'où vient ton problème? Sans le code, ni le fichier dur dur!

A priori (mais cela reste une supposition), tu ne semble pas avoir utilisé cette astuce très pratique dans ton cas :

-> pour traiter ton tableau de la première ligne à la dernière :

DernièreLigne = Range("A1").End(xlDown).Row

Dans le cas où la première cellule de ton tableau est situé en A1 et que cette colonne est tout le temps renseigné. C'est pourquoi on utilise souvent la colonne qui contient l'identifiant (appelé clés primaire en language "traitement de base de données").

-> idem pour la colonne

Après il te reste plus qu'à modifier ton code pour utiliser ces variables, au lieu d'une adresse "fixe", ainsi ta macro s'adaptera automatiquement à la taille de ton tableau.

Bonne après midi

 

[Papou-net]

Re : Comment insérer des lignes/colonnes et que les macros se mettent à jour?

Bonjour dwidou, et bienvenue à toi,

Difficile d'être précis sans fichier à se mettre sous les yeux, mais une des premières idées qui me vient à l'esprit est de définir des noms de plages et d'utiliser ces noms dans tes macros. L'ajout ou la suppression de cellules, lignes ou colonnes dans ces plages ne t'obligera plus à reprendre ton code. Mais il est impératif que ces opérations se fassent dans une plage et non en dehors.

Si tu éprouves des difficultés, tu peux joindre un fichier d'exemple, avec des données non confidentielles ou fictives.

Espérant t'avoir aidé.

Cordialement.

Edit : bonjour Excel-lent,

C'est aussi une autre piste, mais comme on ne le dira jamais assez : sans fichier ...???

 

[dwidou]

Re : Comment insérer des lignes/colonnes et que les macros se mettent à jour?

Merci Excel-lent et Papou-net.
Je suis novice sur ce forum, mais aussi en macros sur excel!!! Vos indications restent vagues pour moi. Voici le fichier. Par exemple si j'insère une colonne avec la colonne Q sur la feuille "saisie des résultats" toute mes macros deviennent fausses. Idem si j'insère une ligne avant la ligne 230. Choses que j'aimerais faire.

 

[Excel-lent]

Re : Comment insérer des lignes/colonnes et que les macros se mettent à jour?

Salut Dwidou,

Tout d'abord chapeau bas pour la présentation : il en jette ton fichier
Et bravo également pour la présentation de ton code, avec les sauts de lignes bien placés et les retraits de lignes.

Pour t'aider à progresser en VBA, pour rendre un code plus clair :
-> enlever les lignes inutiles : au vue de ton code tu maîtrise déjà ce point
-> faire des boucles pour éviter les répétitions : une notice très bien faite
-> simplifier ton code : en raccourcissant les instructions

Premier point facile pour raccourcir ton code :

Sheets("Saisie des équipes").Select

If Range("D11") = 3 Then
Sheets("Saisie des résultats").Select
Range("E9:I39").Select
Selection.ClearContents

Sheets("Saisie des équipes").Select

If Range("D11") = 3 Then
Sheets("Saisie des résultats").Range("E9:I39").ClearContents

Comme tu peux voir, on lieu de sélectionner la feuille voulu, puis la plage voulu, puis... on fait tout en une fois.

Autre point, que ce code raccourci met en évidence, tu n'as pas besoin de "sélectionner" une feuille (ou cellule) pour effectuer quelque chose (par exemple "effacer le contenu" comme ci-dessus). Avantage, la macro tourne plus vite.

Voici ci-joint ton code raccourci comme expliqué ci-dessus. Pas plus sympa à lire? Plus facile à s'y retrouver?

La suite au prochain numéro.
A+

 

[Excel-lent]

Re : Comment insérer des lignes/colonnes et que les macros se mettent à jour?

Hello,

Après avoir un peu raccourci tout ça on voit mieux ton code et remarquons que tu teste la cellule D11 et en fonction du chiffre contenu dans cette dernière il faut faire ...

Si D11 = 3 alors ...
Si D11 = 4 alors ...
Si D11 = 5 alors ...
Si D11 = 6 alors ...
...

Dans ce cas l'utilisation de "Select case" devrais mieux te correspondre : explications détaillées ici

Autre point pour t'aider à simplifier encore plus ton code, l'utilisation de la fonction "With ... End With"

Cela évite de sélectionner ta feuille et de répéter l'information plusieurs fois.

-> voici un exemple sans "With... End With"

Sheets("Saisie des résultats").Range("E9:E39").ClearContents
Sheets("Saisie des résultats").Range("G9:I39").ClearContents
Sheets("Saisie des résultats").Range("L9:M39").ClearContents
Sheets("Saisie des résultats").Range("O9:Z39").ClearContents

-> le même exemple avec "With... End With"

With Sheets("Saisie des résultats")
     .Range("E9:E39").ClearContents
     .Range("G9:I39").ClearContents
     .Range("L9:M39").ClearContents
     .Range("O9:Z39").ClearContents
End With

Bref, pour dire que tu travail sur la feuille sélectionner au début, il te suffit de mettre un point devant. Si tu ne mets pas de points, Excel travailleras sur la feuille précédemment sélectionner (feuille active).

Bref, très pratique pour simplifier et raccourcir ton code, mais également lorsque, comme dans ton cas on travail sur deux feuilles (onglets).

Au lieu d'avoir à faire comme tu as fais, sélectionner sans cesse un coup la feuille... puis après la feuille... puis ensuite à nouveau la première feuille.

Voilà ce que tu peux faire :
-> TOUT au DEBUT de ton code tu écris :

Sheets("Saisie des équipes").Select

Ainsi à chaque que tu ne préciseras rien, Excel sera que tu travail sur cette feuille.

-> En dessous tu utilise "With... End With" pour ta deuxième feuille.

Ce qui donne :

Sub AffichagePouleA()
Sheets("Saisie des équipes").Select
With Sheets("Saisie des résultats")
     .Range("E9:I39").ClearContents
     'comme tu as mis un point devant, tu travail sur la feuille "Saisie des Résultats"
     Range("M9:Q39").ClearContents
     'comme tu n'as pas mis de point devant, tu travail sur la feuille "Saisie des équipes"
End With

Essaye de mettre tous ces conseils en place dans ta macro, pendant ce temps je travail sur ton problème de macro qui ne s'adapte pas à la taille de tes tableaux

A+

 

[Excel-lent]

Re : Comment insérer des lignes/colonnes et que les macros se mettent à jour?

Re,

Pour ton soucis de macro qui ne s'adapte pas, comme indiqué il faut utiliser ce qu'on appelle une variable. Tu trouveras ici la liste de tout les types de variable.

Dans ton cas, ta variable contiendra le numéro de ligne de ton tableau, et une autre le numéro de la colonne.
Colonne A = 1
Colonne B = 2
...
Colonne F = 6
...
Colonne AB = 28
...


Or comme tu le sais peut-être, chaque feuille de Microsoft Excel est composée :
de 16 384 lignes sur 256 colonnes jusqu'à la version Excel 95
de 65 536 lignes sur 256 colonnes jusqu'à la version Excel 2003
de 1 048 576 lignes sur 16 384 colonnes pour les versions Excel 2007 et 2010

Donc tu devras utiliser des variables de type : "Integer" pour les variables contenant le numéro de la colonne. Et de type "Long" pour les variables contenant le numéro de la ligne.

Je ne te cache pas que cela aurait été plus simple si tu avais eu une feuille "saisie des résultats" par poule.

Car pour ton tableau Poule A :
-> la première ligne sera toujours la ligne 5
-> la dernière ligne sera la dernière cellule pleine (obtenu grâce au fameux code : Range("A5").End(xlDown).Row

Pour ton tableau Poule B :
-> la première ligne sera variable
-> la dernière ligne sera elle aussi variable!

Là c'est plus complexe! Mais pas irréalisable

Cours rapide sur les variables, il faut les définir au début de ta macro ce qui donneras dans ton cas :

Dim DernièreLignePouleA as Long
Dim DernièreColonnePouleA as Integer
Dim PremièreLignePouleB as Long
Dim DernièreLignePouleB as Long
Dim DernièreColonnePouleB as Integer
Dim PremièreLignePouleC as Long
Dim DernièreLignePouleC as Long
Dim DernièreColonnePouleC as Integer
...

Ce qui peut se raccourcir de la façon suivante :

Dim DernièreColonnePouleA as Integer, DernièreColonnePouleB as Integer, DernièreColonnePouleC as Integer
Dim PremièreLignePouleB as Long, PremièreLignePouleC as Long
Dim DernièreLignePouleA as Long, DernièreLignePouleB as Long, DernièreLignePouleC as Long
...

Autre chose à savoir, pour définir une cellule il existe plusieurs méthodes dont :
1/ Range("C1") -> très facile à lire et comprendre, lorsqu'on sait que "Range" veut dire "cellule" en language VBA
2/ Range("C" & NuméroLigne ) -> permet ainsi de remplacer le chiffre "1" par une variable "NuméroLigne" (ainsi suivant ce que tu écriras au début de ton code, le numéro de la ligne pourras changer au grès de tes envies.

Exemple tout bête :

For NuméroLigne = 1 To 5
     Range("A" & NuméroLigne).clearcontents
Next NuméroLigne

Ainsi lors de la première exécution de la boucle, la variable contiendra le chiffre 1, puis la seconde fois le chiffre 2, ..., puis 5

Nettement plus court que d'écrire :

Range("A1").clearcontents
Range("A2").clearcontents
Range("A3").clearcontents
Range("A4").clearcontents
Range("A5").clearcontents

Bien évidement, c'est un exemple pour expliquer la boucle For ... To ... Next et Range("A" & ...) car l'on aurait pu l'écrire de façon plus simple :

Range("A1:A5").clearcontents

3/ Cells(1, 3) -> il faut savoir que c'est l'inverse de "Range", le premier caractère (un chiffre dans le cas présent) représente le numéro de la ligne, et le second le numéro de la colonne.

Comme tu peux t'en rendre compte cette dernière méthode est très pratique à utiliser avec des variables. Car c'est plus facile de dire "NuméroColonne" = 5 + ... que s'il s'agissait d'une lettre.

La suite au prochain numéro...

Edition : dans les Dim, j'avais oublié qu'il fallait une variable pour la dernière de chaque tableau ET la première ligne des tableaux 2 (poule B) à ... (poule F)

 

[dwidou]

Re : Comment insérer des lignes/colonnes et que les macros se mettent à jour?

Merci pour ta réponse Excel-lent, très bonne explication très pédagogique. J'ai tout compris à la première lecture !!!
Maîtrisant assez bien le html, j'avoue que ca m'aide un peu dans l'écriture de macros. Je vais donc suivre déjà tes conseils pour tout "cleaner" et raccourcir, afin que le code soit moins lourd et donc plus rapide d'exécutio.

 

[Excel-lent]

Re : Comment insérer des lignes/colonnes et que les macros se mettent à jour?

Hello,

Maîtrisant assez bien le html

Je comprend mieux pourquoi, malgré que tu sois débutant en VBA, ton code soit bien présenté.

Maintenant qu'on a bien tout débroussaillé et complété les connaissances qu'il te manquait (ou pas) pour arriver à ta question :

ayant des références de plage déjà, ne se mettent pas à jour!!!

Plus précisément, il te manque encore un point pour vraiment comprendre le fonctionnement de la fonction :

Range("A5").End(xlDown).Row

Pour mieux la comprendre :
-> place toi sur ta feuille : "Saisie des résultats"
-> lance l'enregistreur de macro
-> sélectionne la cellule A5
-> appuie sur la touche "Ctrl", laisse appuyé dessus, appuie sur la touche "flèche vers le bas" un coup bref, puis relâche la touche "Ctrl"
-> arrête l'enregistreur de macro.

Comme tu as pu le constater, au cas où tu ne connaîtrais pas cette astuce : les touches "Ctrl" + "flèche vers ..." permet d'aller à la prochaine cellule vide/pleine dans la direction indiqué.
-> Si la cellule d'origine est vide, il va jusqu'à la prochaine cellule rempli et s'arrête juste avant : "la dernière cellule vide".
-> Si la cellule d'origine est pleine, il va jusqu'à la prochaine cellule vide et s'arrête juste avant : "la dernière cellule pleine".

Si tu as suivi les instructions à la lettre, tu as obtenu le code suivant :

Range("A5").Select
Selection.End(xlDown).Select

Si tu avais utilisé la flèche vers la droite (pour obtenir la dernière colonne), tu aurais eu le code suivant :

Range("A5").Select
Selection.End(xlRight).Select

Qui comme tu le sais maintenant peut s'écrire ainsi :

Range("A5").End(xlDown).Select

Range("A5").End(xlRight).Select

Oui mais voilà... le code ci-dessus "sélectionne" la fameuse cellule, nous nous désirons le numéro de la ligne.

Range("A5").End(xlDown).Row

Ou de la colonne

Range("A5").End(xlRight).Column

A placer tout au début de ta macro :

Dim DernièreColonnePouleA as Integer, DernièreColonnePouleB as Integer, DernièreColonnePouleC as Integer
Dim DernièreColonnePouleD as Integer, DernièreColonnePouleE as Integer, DernièreColonnePouleF as Integer

Dim PremièreLignePouleB as Long, PremièreLignePouleC as Long, PremièreLignePouleD as Long
Dim PremièreLignePouleE as Long, PremièreLignePouleF as Long,

Dim DernièreLignePouleA as Long, DernièreLignePouleB as Long, DernièreLignePouleC as Long
Dim DernièreLignePouleD as Long, DernièreLignePouleE as Long, DernièreLignePouleF as Long

'Poule A
DernièreLignePouleA = [A5].End(xlDown).Row
' Pas besoin de définir de variable pour la première ligne du tableau PouleA
' puisque ce sera toujours la ligne 6

DernièreColonnePouleA = [A5].End(xlRight).Column - 1
' si on laisse [A5].End(xlRight).Column dans ton fichier exemple on obtiendra
' le numéro de la colonne comportant le mot "Equipe"
' or comme tu veux le numéro de la colonne comportant le mot "Drop", il suffit
' de rajouter "-1" (pratique non? Imagine de coder en VBA : Colonne R - 1)

'Poule B
PremièreLignePouleB = [A5].End(xlDown).End(xlDown).Row + 2
' j'ai mis "+2" car sinon on tombe sur la fameuse ligne contenant : Poule B - Terrain 2
' ainsi le "+2" nous donnera le numéro de la cellule [A82], celle qui t'intéresse

DernièreLignePouleB = [A5].End(xlDown).End(xlDown).End(xlDown).Row
DernièreColonnePouleB = Range("A" & PremièreLignePouleB -1 ).End(xlRight).Column - 1

'Poule C
'...

Pour information : lorsque je parle de première ligne Poule B, je parle la première ligne de donnée : c'est à dire la troisième ligne du tableau.
-> la toute première ligne c'est celle qui contient "Poule ... - Terrain ..." (la première cellule pleine)
-> la vrai seconde ligne c'est celle qui contient tes titres de colonnes : "début", "fin", "Arbitre", "Equipe", ...
-> la troisième ligne (celle que j'appelle "PremièreLignePoule..." d'où le '+2'

Et plus tard, lorsque tu sera plus calé, tu pourras regrouper toutes tes macro en une seule, raccourcir encore plus le code, rendre la gestion des macros à la hauteur du fichier : par exemple en mettant qu'un seul bouton "Afficher les matchs de la poule ...", voir même encore mieux, utiliser une UserForm.

Mais comme on dit : l'important est que tout fonctionne et soit simple d'utilisation.

Voili voilou, avec tout ça tu devrais t'en sortir comme un(e) chef.
Mais si tu as besoins d'explications plus détaillées sur un point, n'hésite pas. Et si tu as des questions sur un autre point, n'hésite pas à ouvrir un nouveau sujet (sans oublié de mettre un fichier exemple ).

 

[dwidou]

Re : Comment insérer des lignes/colonnes et que les macros se mettent à jour?

Encore merci Excel-lent, je vais mettre tous tes conseils en pratique ce WE ou la semaine prochaine. Et j'irai aussi jeter un oeil au site GSF (j'ai eu 1 bandit 600 et 2 bandit 1200 depuis 12 ans).

 

[Excel-lent]

Re : Comment insérer des lignes/colonnes et que les macros se mettent à jour?

Re,

Une fois que tu auras :
-> remplacer ton imbrication de 40 milles "If... Then... Else... End If" par "Case"

-> rajouter la partie "définition des variables"
Dim NomDeTaVariable as TypeDeTaVariable

-> donné une valeur à tes variable
MaVariable1 = ...
MaVariable2 = ...
...

-> remplacé dans ton code les adresses de cellules dite "fixe", par des adresses "variables"

Tu auras un code clair, aux petits oignons et qui s'adaptera automatiquement.

Astuces bonus :
1/ Comme en HTML, en VBA pour qu'un code soit clair et facile il faut (comme tu l'as si bien fait), sauter des lignes, faire des retraits de lignes.

Là où ça diffère, en language VBA nous avons le droit aux majuscules et aux accents. Donc autant en profiter pour donner aux variables des noms clair avec une majuscule au début de chaque mot. Comme ceux utilisés dans mes posts ci-dessus (par exemple : PremièreLignePouleB)

Pour information, tu n'es pas obligé de définir tes Variables en début de macro, mais l’exécution de la macro est moins rapide et parfois bug.

Bonne fin de journée

 

[dwidou]

Re : Comment insérer des lignes/colonnes et que les macros se mettent à jour?

Hello,

Après avoir un peu raccourci tout ça on voit mieux ton code et remarquons que tu teste la cellule D11 et en fonction du chiffre contenu dans cette dernière il faut faire ...

Si D11 = 3 alors ...
Si D11 = 4 alors ...
Si D11 = 5 alors ...
Si D11 = 6 alors ...
...

Dans ce cas l'utilisation de "Select case" devrais mieux te correspondre : explications détaillées ici

Autre point pour t'aider à simplifier encore plus ton code, l'utilisation de la fonction "With ... End With"

Cela évite de sélectionner ta feuille et de répéter l'information plusieurs fois.

-> voici un exemple sans "With... End With"


-> le même exemple avec "With... End With"

With Sheets("Saisie des résultats")
     .Range("E9:E39").ClearContents
     .Range("G9:I39").ClearContents
     .Range("L9:M39").ClearContents
     .Range("O9:Z39").ClearContents
End With

Bref, pour dire que tu travail sur la feuille sélectionner au début, il te suffit de mettre un point devant. Si tu ne mets pas de points, Excel travailleras sur la feuille précédemment sélectionner (feuille active).

Bref, très pratique pour simplifier et raccourcir ton code, mais également lorsque, comme dans ton cas on travail sur deux feuilles (onglets).

Au lieu d'avoir à faire comme tu as fais, sélectionner sans cesse un coup la feuille... puis après la feuille... puis ensuite à nouveau la première feuille.

Voilà ce que tu peux faire :
-> TOUT au DEBUT de ton code tu écris :


Ainsi à chaque que tu ne préciseras rien, Excel sera que tu travail sur cette feuille.

-> En dessous tu utilise "With... End With" pour ta deuxième feuille.

Ce qui donne :

Sub AffichagePouleA()
Sheets("Saisie des équipes").Select
With Sheets("Saisie des résultats")
     .Range("E9:I39").ClearContents
     'comme tu as mis un point devant, tu travail sur la feuille "Saisie des Résultats"
     Range("M9:Q39").ClearContents
     'comme tu n'as pas mis de point devant, tu travail sur la feuille "Saisie des équipes"
End With

Essaye de mettre tous ces conseils en place dans ta macro, pendant ce temps je travail sur ton problème de macro qui ne s'adapte pas à la taille de tes tableaux

A+

Bonsoir Excel-Lent,
Je viens de mettre en pratique tes conseils mais ca bloque (erreur 424 objet). J'ai écris ce code :
Sub AffichagePouleA()
'
' AffichagePouleA Macro
' Affiche les matchs de la poule A en fonction du nb d'équipes dans la poule A
'

'
Sheets("Saisie des équipes").Select

If Range("D11") = 3 Then
With Sheets("Saisie des résultats").Range("E9:I39").ClearContents
.Range("M9:Q39").ClearContents
.Rows("6:8").EntireRow.Hidden = False
.Rows("9:39").EntireRow.Hidden = True
Sheets("Saisie des résultats").Range("I6").Select
End With
Ca bloque sur .Range("M9:Q39").ClearContents Par contre cela m'a bien nettoyer la plage E9:I39 juste au dessus.

Enlevant le point devant Range, effectivement je m'aperçois que la macro agit sur l'autre sheet. Donc le point est bon mais erreur dans l'objet. Tu saurais ce qui ne va pas?

 

[dwidou]

Re : Comment insérer des lignes/colonnes et que les macros se mettent à jour?

Bon j'avance un peu : j'ai mis un retour ligne après With Sheets ... Et là ca passe, par contre la macro s'arrête sur .Range ("I6") Erreur 1004 "La méthode select de la classe Range a échoué"

 

[Staple1600]

Re : Comment insérer des lignes/colonnes et que les macros se mettent à jour?

Bonsoir à tous

dwidou
Bien que je sois un fervent partisan des endives dans le VBA, parfois on peut aussi ne pas les utiliser

Sub a()
Sheets("Saisie des résultats").Range("E9:E39,G9:I39,L9:M39,O9:Z39") = Empty
End Sub

 

[dwidou]

Re : Comment insérer des lignes/colonnes et que les macros se mettent à jour?

Bonsoir à tous

dwidou
Bien que je sois un fervent partisan des endives dans le VBA, parfois on peut aussi ne pas les utiliser

Sub a()
Sheets("Saisie des résultats").Range("E9:E39,G9:I39,L9:M39,O9:Z39") = Empty
End Sub

Merci, j'apprends encore que je peux mettre plusieurs plages en même temps pour la même instruction !!! Du coup mon code s'allège encore un peu plus. Heureusement qu'il y a les forums...