Sélection de cellules jusqu'à graphique

[Ewigefrost]

Bonjour !

J'ai une question concernant la sélection d'une plage de cellules et dont je n'ai trouvé la réponse nulle part.

Voici mon problème : j'ai un tableau dont le nombre de lignes varie, et dans chacune de ces lignes se trouvent des formules, ne renvoyant pas de valeur si celle-ci est égale à 0.
Ces formules sont étirées sur plusieurs dizaines de lignes, pour ne pas avoir à les retaper lorsque des lignes (correspondant à des références) s'ajoutent.

En-dessous de la dernière ligne remplie du tableau se trouve un graphique, qui lui se décale automatiquement vers le bas à chaque ajout de ligne dans le tableau.

J'aimerais pouvoir sélectionner la plage de cellules allant de N2 (haut du tableau) au bas du graphique. Est-ce possible ?

Le xlUp ne correspond pas à mon besoin, puisqu'il va chercher la dernière ligne avec formule, ce qui du coup supprime le caractère variable du xlUp.

Merci d'avance pour votre précieuse aide !

Bonne journée.

 

[Philippe68]

Re : Sélection de cellules jusqu'à graphique

Bonjour,

Sans fichier, il y aura moins de chance que les "experts" répondent...

 

[Ewigefrost]

Re : Sélection de cellules jusqu'à graphique

Geute morje Philippe,

je ne peux malheureusement pas joindre le fichier, sur lequel figurent des informations confidentielles, mais je joins de suite un fichier exemple permettant de mieux comprendre le problème !

 

[STephane]

Re : Sélection de cellules jusqu'à graphique

Bonjour,

quelque chose du genre suivant.

Sub selection_de_cellules_jusqua_graphique__Fil_XLD_179131()
Dim rg_Depart As Range
Dim rg_FinTableau As Range
Dim shp As Shape
Dim rg_Intersect
Set rg_Depart = [N2]
' Détermination du tableau (sans le graphique), tableau d'une colonne en l'occurence
Set rg_FinTableau = Range(rg_Depart, Cells(Rows.Count, rg_Depart.Column).End(xlUp))

' Boucle sur les formes de la page pour trouver le(s) graphique(s) sous les tableaux
For Each shp In rg_FinTableau.Worksheet.Shapes

' Passer à la forme suivante si celle-ci n'est pas de type graphique
If shp.Type <> 3 Then GoTo nnext

' Vérifie si le graphique se trouve à l'intersection de la colonne N
Set rg_Intersect = Intersect(Range(shp.TopLeftCell, shp.BottomRightCell), rg_Depart.EntireColumn)
' S'il l'intersection existe :
If Not rg_Intersect Is Nothing Then

'La plage rg_FinTableau est changée selon que le coin bas droit de la forme est plus pas que le tableau
If rg_Intersect.Item(rg_Intersect.Cells.Count).Row > rg_FinTableau.Row Then Set rg_FinTableau = Range(rg_Depart, Range(rg_Depart, Cells(rg_Intersect.Item(rg_Intersect.Cells.Count).Row, rg_Depart.Column)))

rg_FinTableau.Select

'L'instruction ci-dessous est facultative et peut être activé/désactiver pour tenir compte de la largeur du graphique le plus bas
'La plage rg_FinTableau est changée selon que le graphique est plus large que la colonne N
If shp.BottomRightCell.Column > rg_Depart.Column Then Set rg_FinTableau = Range(rg_FinTableau, rg_FinTableau.offset(, shp.BottomRightCell.Column - rg_Depart.Column))

End If
nnext:
Next shp

rg_FinTableau.Select

End Sub

 

[Ewigefrost]

Re : Sélection de cellules jusqu'à graphique

Bonjour STephane,

Comme promis je joins un fichier permettant de mieux expliquer mon problème.

Je teste ta solution STephane, je te tiens au courant. Par contre, j'ai oublié de préciser que ce n'est pas un graphique mais un groupe de graphiques. Je vais voir ce que je dois changer dans ton code pour l'adapter à un groupe !

Merci bien !

Regarde la pièce jointe ex_selection.xlsx

 

[Victor21]

Re : Sélection de cellules jusqu'à graphique

Bonjour.

Une piste d'après votre exemple : recherchez la ligne contenant "Total" et utilisez offset pour ajouter le nb de lignes correspondant à la hauteur qu'occupe(nt) votre(vos) graphique(s).

 

[Ewigefrost]

Re : Sélection de cellules jusqu'à graphique

Re STephane, bonjour Victor21,

j'ai testé ton code STephane, mais ça me sélectionne toutes les lignes contenant une formule, donc la sélection s'étend bien en-dessous du groupe. Je n'ai pas compris tout le code, mais ça vient sûrement de l'tuilisation du xlUp non ?

Victor21, merci pour l'astuce ! Je me renseigne sur le offset et sur la recherche de la ligne en fonction du mot "Total", et ça devrait marche je pense !

Merci à vous tous !

 

[Misange]

Re : Sélection de cellules jusqu'à graphique

Bonjour

Une autre solution, sans macro
tu ne prépares pas de lignes à l'avance mais tu transformes ton tableau en ... tableau au sens excel du terme (onglet accueil/style/mettre sous forme de tableau)
La formule qui est entrée dans une ligne se recopie toute seule dans la ligne du dessous quand tu ajoutes une ligne sous la dernière donnée du tableau
dans l'exemple sur la feuille Misange ajoute quelque chose en A19 et regarde ce qui se passe
L'avantage c'est qu'excel crée automatiquement des plages dynamiques avec les tableaux de ce type.
On peut même inclure une ligne de total mais dans ce cas là pour ajouter une ligne supplémentaire il faut l'insérer au dessus du total et je ne suis pas sure que cela te convienne.
J'ai donc déporté les totaux nécessaires à la réalisation de ton graphique en dehors de ce tableau. Comme il font référence aucx olonnes de ce tableau de façon dynamique, chaque valeur ajoutée au tableau est automatiquement prise en compte dans les totaux. Ca à l'air compliqué comme ça mais c'est au contraire très simple.

Un seul truc agaçant : quand tu ajoutes une nouvelle donnée sous ton tableau du fait de la présence du graphique tu as temporairement un message d'erreur (qui n'a d'ailleurs pas lieu d'être) mais dis OK et ça roule et ton graphique se met tout seul à jour dès que tu entres une nouvelle ligne.

 

[Ewigefrost]

Re : Sélection de cellules jusqu'à graphique

Bonjour Misange,

j'ai testé ta méthode en sélectionnant mon tableau puis en cliquant sur "Mettre sous forme de tableau", mais dans mon fichier d'origine, lorsque je rajoute une ligne dans le TCD qui sert de base aux formules présentes dans le tableau que je veux sélectionner (je ne rajoutepas de lignes manuellement en fait, ça se fait automatiquement), elle se rajoute mais décale aussi la ligne "Total" vers le bas et la rend invisible.
C'est normal ?

Victor21, j'ai cherché un peu comment je pourrais coder ça, et j'ai pour l'instant écrit ça :

Sub test()
With Worksheets("TCD ALU").Range("a1:a40")
Dim NLigneDescription As Long
Cells.Find(What:="Total général", After:=Range("A1"), LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
NLigneDescription = ActiveCell.Row
Range("A200").Value = NLigneDescription
End With
End Sub

Ca marche bien, mais maintenant concernant l'offset, je n'ai trouvé que de la documentation montrant comment l'utiliser pour déplacer une plage de cellules. Mais comme tu me l'as proposé dans ton post, il faudrait juste sélectionner les quelques lignes après la fin du tableau. Quelqu'un pourrait m'éclairer là-dessus svp ?

En tous cas merci encore à vous tous, c'est très sympa de votre part de prendre du temps pour m'aider !

 

[Misange]

Re : Sélection de cellules jusqu'à graphique

Bonjour
si tu as fait attention, j'ai décalé volontairement les totaux en dehors du tableau justement pour éviter ce que tu décris.
Que tes données soient ajoutées automatiquement (macro, requête) ou manuellement ne change rien : du moment que tu es dans un tableau, la nouvelle ligne s'intègre. Sauf hélas quand il y a une ligne de total.
mais reprends mon exemple, c'est quand même ce qui est le plus simple je pense...

 

[Ewigefrost]

Re : Sélection de cellules jusqu'à graphique

Ah ok merci pour ces explications. Par contre le total sur le côté me pose problème en fait. L'exemple que j'ai posté est un peu plus simple que ce que j'ai en réalité.

En effet, j'ai plusieurs paire de colonnes de type "Objectif/Réalisé", et l'affichage du tableau+diagrammes (sous forme de page HTML) nécessite que ma ligne de total soit tout en bas, c'est impératif en fait. Du coup tu m'as dit que ça n'irait pas non ?

Merci !

 

[Misange]

Re : Sélection de cellules jusqu'à graphique

Comment tes données sont-elles ajoutées dans ton tableau ?

 

[Ewigefrost]

Re : Sélection de cellules jusqu'à graphique

En fait je suis arrivé sur ce fichier déjà tout prêt (tout a été mis en place avant que je l'utilise, sauf l'automatisation), donc je ne pourrai pas t'expliquer dans les détails.

Mais en gros, le rendu des pièces réalisées rentre dans une feuille du tableau, puis il me faut actualiser le TCD (qui s'agrandit ou rétrécit suivant le nombre de références fabriquées) sur la même feuille que le tableau qui m'intéresse. Celui-ci change également de taille lorsque le TCD est actualisé.

Est-ce assez clair ?

 

[Ewigefrost]

Re : Sélection de cellules jusqu'à graphique

Rebonjour,

je pense être proche de la solution avec l'astuce de Victor21, vu que j'arrive à faire marcher la macro dans la feuille concernée. Mais il me faudrait maintenant l'intégrer au reste du code déjà présent dans "ThisWorkbook" (code qui permet de publier une page HTML à partir de la plage de cellules que je souhaite, càd le tableau + les diagrammes, et ce dès qu'il y a un changement sur un des feuilles).

Voici le code que j'utilise pour l'instant (la partie qui nous intéresse) :

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim DernièreLigne As Long
DernièreLigne = [V65536].End(xlUp).Row
wshSheets = [{"TCD ACIER", "TCD ALU"}]
If Not IsError(Application.Match(Sh.Name, wshSheets, 1)) Then
With ActiveWorkbook.PublishObjects.Add(xlSourceRange, _
"C:\blabla\test.htm" _
, "TCD ALU", "$N$2:$X$" & DernièreLigne, xlHtmlStatic, "Suivi Prod_2012_S05_21297", "")
.Publish (True)
.AutoRepublish = True
End With

Et voici le code qu'il faudrait que j'arrive à placer :

Sub test()
With Worksheets("TCD ALU").Range("a1:a40")
Dim NLigneDescription As Long
Dim Dligne As Long
Cells.Find(What:="Total général", After:=Range("A1"), LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
NLigneDescription = ActiveCell.Row
Range("A200").Value = NLigneDescription
Dligne = NLigneDescription + 10
Range("A201").Value = Dligne
End With
End Sub

Lorsque je place un Dim Dligne as Long à la place du Dim Dernièreligne as Long, et que je remplace le "&Dernièreligne" par "&Dligne", ça ne fonctionne pas ...

Auriez-vous une idée svp ?

Merci encore infiniment !

 

[Ewigefrost]

Re : Sélection de cellules jusqu'à graphique

Rebonjour le forum,

J'ai vraiment du mal avec l'intégration du code dans mon programme déjà existant, j'ai tout essayé mais je ne comprends vraiment pas pourquoi ça ne marche pas ...

La solution qui m'aurait paru la plus juste et la plus intuitive est celle-là, mais elle me fait juste planter Excel :

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim DernièreLigne As Long
DernièreLigne = [V65536].End(xlUp).Row
wshSheets = [{"TCD ACIER", "TCD ALU"}]
If Not IsError(Application.Match(Sh.Name, wshSheets, 1)) Then
With Worksheets("TCD ALU").Range("a1:a40")
Dim NLigneDescription As Long
Dim Dligne As Long
Cells.Find(What:="Total général", After:=Range("A1"), LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
NLigneDescription = ActiveCell.Row
Range("A200").Value = NLigneDescription
Dligne = NLigneDescription + 10
Range("A201").Value = Dligne
With ActiveWorkbook.PublishObjects.Add(xlSourceRange, _
"C:\blabla\test.htm" _
, "TCD ALU", "$N$2:$X$" & Dligne, xlHtmlStatic, "Suivi Prod_2012_S05_21297", "")
.Publish (True)
.AutoRepublish = True
End With
End With

Le but du code est de faire en sorte que lorsqu'il y a un changement dans une cellule des feuilles "TCD ALU" ou "TCD ACIER", Excel publie automatiquement la plage souhaitée (tableau + diagrammes) en HTML.

Les lignes

Range("A200").Value = NLigneDescription

Range("A201").Value = Dligne

me servent juste à vérifier si la macro pour trouver la ligne qui contient "Total général" marche bien.

Où est-ce que j'ai faux ? Je vois vraiment plus où placer mon code pour que ça fonctionne !