Microsoft 365 plage macro dynamique

[JBL07]

Bonjour à toutes et tous !

Une petite question VBA :

J'ai plusieurs petites macros qui permettent de balayer une colonne dans un tableau, pour différentes actions
Je les ai créées avec l'enregistreur de macros - Mon souci est qu'elles sont limitées à la plage que je sélectionne, et ne prolongent pas leur action sur les nouvelles lignes

Exemple celle-ci, où je voudrais remplacer le ("J9:J792") par ce qui permettrait de rendre la chose dynamique, en fonction des ajouts ou suppressions à partir de J9

Sub MAJ_formule_recherche()
'MAJ_formule_recherche Macro

' Re balaye la colonne masquée J en cas de décalage formule pour la recherche
Range("J9").Select
Selection.AutoFill Destination:=Range("J9:J792")
Range("J9:J792").Select
Range("G8").Select
End Sub

Merci pour votre aide

 

[chris]

Bonjour

Utilise des tableaux structurés : outre la taille automatiquement gérée, la recopie automatique des formules...

Tableaux structurés Excel

Tableaux structurés Excel

fauconnier.developpez.com

 

[JBL07]

C'est déjà le cas, mais ça ne fonctionne qd même pas : j'ai entré qq lignes test pour avoir des écritures jusque J795, et comme mes macros ont une plage jusque J792, ça plante

Il me faudrait vraiment une macro qui permette de ne pas nommer des valeurs de cellules, mais une plage dynamique ( n'y a-t-il pas qq chose comme liste-objets ?? J'ai cru voir ça qq part

 

[Dranreb]

Bonsoir.
Effectivement un tableau Excel est toujours représenté par un objet de type ListObject faisant partie d'une collection ListObjects. Mais propagée une seule fois manuellement à toutes les lignes du tableau, une formules est ensuite automatiquement reconduite sur les nouvelles lignes ajoutées, pas besoin d'une macro pour cela.

 

[JBL07]

C'est que mon cas est un peu plus compliqué, j'espérais faire l'économie de trop d'explications

En fait :
un tableau d'écritures comptables
- un bouton macro pour insérér une ligne en haut + 1 bouton pour retrier si besoin chronologiquement
- 1 colonne "solde" avec une formule qui calcule le solde à chaque écriture, en remontant puisque dates anciennes en bas
- Un bouton pour éventuellement supprimer une ligne et là... toutes les cellules "solde" au-dessus de la ligne supprimée se mettent en erreur, puisque la chaine est interrompue
Donc, je veux que la macro reprenne la cellule solde la plus ancienne ( dernière ligne tableau), et remonte toutes les lignes en copiant la formule

Ca fonctionne, sauf que j'ai donc créé tout ça depuis la ligne 792, et que ça ne tient pas compte de l'allongement du tableau

Pas certain d'être clair, je mettrai peut-être un fichier allégé ce we pour illustrer ma demande

 

[Dranreb]

J'attends donc votre fichier …
Mais la solution c'est peut être d'utiliser la fonction DECALER.

 

[chris]

RE à tous

Quand on utilise des listobjects il faut coder en conséquence et non utiliser des référence de type range classique. On se sert des listrows, du databodyrange, etc

Sans exemple concret on n'avancera pas

 

[JBL07]

Bonjour,

Je laisse tomber ma demande, je n'arrive pas à envoyer un fichier allégé qui garde les fonctionnalités qui vous permettent de travailler dessus, il y a trop de macros installées dedans.
Je vais faire autrement
Merci quand même à vous
Bon we !

 

[Dranreb]

Il y a surtout trop de données, à mon avis !
Mais effectivement seul le module qui se rapporte au problème pourrait présenter un intérêt.
Mais ce n'est pas sûr. On va commencer par chercher une formule à références structurées qui supporte une suppression de ligne. Pour ça il faudrait juste qu'on connaisse déjà la formule actuelle et voir comment on pourrait remplacer la référence à une cellule de la ligne suivante par un DECALER(la référence à celle de la même ligne;1;0).
Ne laissez que quelques lignes dans le tableau sur la seule feuille laissée.

 

[chris]

RE

Avec une tableau structuré respectant les règles, les formule se recopiant automatiquement, l'ajout comme la suppression de ligne n'ont généralement pas d'incidence si on raisonne bien en ajout ou suppression de listrow.

J'ai l'impression qu'il s'agit d'un faux problème lié à une mauvaise utilisation de tableau structuré...

 

[JBL07]

Voici
Les colonnes H / J / K sont masquées, formules servant à faire des recherches par mot
Il y a des bugs puisque j'ai enlevé les autres feuilles de mon classeur, mais ça ne devrait pas être trop génant
Je ne vous tiendrait pas rigueur si vous ne vous y retrouvez pas !
La saisie d'une nouvelle écriture est censée se positionner en ligne 9, et le bouton Chrono retrier tout le tableau ensuite par ordre chrono du plus récent au plus ancien

 

[chris]

RE

Ton tableau structuré ne respecte pas les règles du tableau structuré : formule non homogène sur toute la colonne (ligne 8) ce qui pose problème

Par ailleurs les Tableaux structurés gèrent mal les formules avec références à d'autres lignes du même tableau, ce qui oblige à relativiser avec DECALER, comme suggéré par Dranreb
exemple

=SI(GAUCHE([@[S/Catégorie]];6)="Saisie";0;SI([@Compte]="Courant";DECALER([@P];1;1)-[@Débit]+[@Crédit];DECALER([@P];1;1)))

Ton code ne gère pas un tableau mais des plages

 

[JBL07]

Ah.... compliqué pour moi, je bidouille sur des choses déjà faites, et je me doute que ça crée des incongruités

 

[Hasco]

Bonjour le fil,

Comme il n'est pas conseillé (à moins d'un besoin TRES particulier) de référencer des colonnes (ou lignes) entières dans les formules et que les tableaux structurés gèrent très bien le nombre de lignes, vos formules devraient ressembler à celle-ci (exemple somme.si.ens de L3) :

=SOMME.SI.ENS(Tabécritures[Crédit];Tabécritures[Compte];$I3;Tabécritures[P];"")

en lieu et place de SOMME.SI.ENS(Col_Cr;Col_Compte;I3;Col_P;"")

De plus cela diminuerait grandement le nombre de colonnes nommées définies par 'decaler(.....)' et clarifierait votre projet.

Un petits conseils : ne donnez pas de titres trop longs à vos entêtes de colonnes de tableaux structurés [Mode de Paiement] peut être remplacé par [Mode]. Il n'est pas conseillé non plus de mélanger les types informationnels dans une même colonne [Tiers / Commentaire] . Faites une colonne Tiers et éventuellement une Commentaire. Cela vous permettra un traitement des données plus clairs (Listage et dénombrement des tiers, TCD, traitement par PowerQuery etc.)

Les colonnes 'Année' et 'Mois' sont-elle réellement indispensables les deux informations, se trouvant déjà dans la colonne 'Date' dont le format d'affichage pourrait éventuellement changer pour afficher jour mois année.

Bon courage.