Pointer une ligne relative dans une macro

P

PièceJointe

XLDnaute Nouveau
Pointer les lignes "sous-totaux" dans une macro

Bonjour à tous...

de retour parmi vous, je crois que je suis en train de devenir addict...

Voilà je n'ai pas trouvé en parcourant vos forums comment, dans une macro, pointer une plage relative (est-ce seulement le nom ? Dans mon exemple il s'agit de sous totaux...)

Voici mon code :
Code: 
Sub GRAPH()
    Charts.Add
    ActiveChart.ChartType = xl3DColumnClustered
    ActiveChart.SetSourceData Source:=Sheets("Général").Range("H166:L166"), _
        PlotBy:=xlColumns
    ActiveChart.SeriesCollection(1).Name = "=Général!R5C8"
    ActiveChart.SeriesCollection(2).Name = "=Général!R5C9"
    ActiveChart.SeriesCollection(3).Name = "=Général!R5C10"
    ActiveChart.SeriesCollection(4).Name = "=Général!R5C11"
    ActiveChart.SeriesCollection(5).Name = "=Général!R5C12"
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Graphiques"
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Répartion de l'offre de formation par domaines"
        .Axes(xlCategory).HasTitle = True
        .Axes(xlCategory).AxisTitle.Characters.Text = "Domaine"
        .Axes(xlSeries).HasTitle = False
        .Axes(xlValue).HasTitle = True
        .Axes(xlValue).AxisTitle.Characters.Text = "Nombre"
    End With
End Sub

Comment faire en sorte que "Range("H166:L166")" prenne une valeur relative ? En gros sil des données sont insérées ma ligne de réfernce n'est plus la 166...

Et y a t'il moyen de faire en sorte qu'il me sorte un graph pour tous mes sous totaux et pas seulement ceux de la ligne 166 colonnes H à F ?


Merci d'avance pour vos conseils.
 
Dernière édition:
Trier par date Trier par votes
Gael

Gael

XLDnaute Barbatruc
Re : Pointer une ligne relative dans une macro

Bonjour Piècejointe,

Un moyen simple est de nommer la zone souhaitée avec "insertion nom définir". La plage peut être variable au moyen d'une formule pour pointer sur la bonne ligne.

Si tu nommes le champ "Plage", tu peux écrire dans la macro:

...Range("Plage")...

Pour la 2ème question, je n'ai pas bien compris, le mieux serait d'envoyer un exemple.

@+

Gael
 
Vote positif 0 Vote négatif
P

PièceJointe

XLDnaute Nouveau
Re : Pointer une ligne relative dans une macro

Wouha...quelle rapidité...

Merci Gaël...

En fait à la réflexion mes deux questions sont liées : l'idée étant de sortir des graphiques directement à partir des sous totaux car non seulement je peux être amené à insérer des lignes mais également à recalculer mes sous totaux...et je veux autant de graph que de lignes sous total...pour faire simple

Un petit exemple avec 2 sous tot et un tot général (3 séries de graphs donc)
 

Pièces jointes

  • TEST GRAPH.xls
    47.5 KB · Affichages: 147
  • TEST GRAPH.xls
    47.5 KB · Affichages: 144
  • TEST GRAPH.xls
    47.5 KB · Affichages: 137
Vote positif 0 Vote négatif
pierrejean

pierrejean

XLDnaute Barbatruc
Re : Pointer une ligne relative dans une macro

bonjour Piècejointe

Salut Gael :)

Une petite macro qui te donne les numeros de ligne ou figure un sous-total
Tu pourras peut-etre t'en inspirer

Code: 
Sub test()
For n = 1 To Range("C65536").End(xlUp).Row
 If InStr(Range("C" & n).FormulaLocal, "SOUS.TOTAL") <> 0 Then
   MsgBox ("Il y a un Sous-total a la ligne " & n)
 End If
Next n
End Sub
 
Vote positif 0 Vote négatif
P

PièceJointe

XLDnaute Nouveau
Re : Pointer une ligne relative dans une macro

Bon ça ne marche pas comme ça direct...

Code: 
Sub GRAPH()
'
' GRAPH Macro
' Macro enregistrée le 14/10/2008 par utiliseur
For n = 1 To Range("C65536").End(xlUp).Row
 If InStr(Range("C" & n).FormulaLocal, "SOUS.TOTAL") <> 0 Then
    Charts.Add
    ActiveChart.ChartType = xl3DColumnClustered
    ActiveChart.SetSourceData Source:=Sheets("Général").InStr(Range("C" & n).FormulaLocal, "SOUS.TOTAL"), _
        PlotBy:=xlColumns
    
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Graphiques"
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Répartion de l'offre de formation par domaines"
        .Axes(xlCategory).HasTitle = True
        .Axes(xlCategory).AxisTitle.Characters.Text = "Domaine"
        .Axes(xlSeries).HasTitle = False
        .Axes(xlValue).HasTitle = True
        .Axes(xlValue).AxisTitle.Characters.Text = "Nombre"
    End With
 End If
Next n
End Sub

le pb est là, évidemment.... ;)
Code: 
ActiveChart.SetSourceData Source:=Sheets("Général").InStr(Range("C" & n).FormulaLocal, "SOUS.TOTAL"), _
        PlotBy:=xlColumns
J'envisage de demander qu'à chaque fois qu'est repérée la ligne "sous total" il copie-colle les cellules désirées sur une feuille dédiée (en position absolue du coup) et que seulement dans un deuxième temps que soit produit le graph à partir de ces dernières sources... Suis-je sur la bonne voir d'après vous ?

D'autre part il va falloir que je produise un code qui prennent également en compte le fait qu'il y aura autant "positions absolues" sur cette feuille que de "sous total" ...

Suis je clair ?
 
Vote positif 0 Vote négatif
pierrejean

pierrejean

XLDnaute Barbatruc
Re : Pointer une ligne relative dans une macro

A tester:

Code: 
Sub GRAPH()
'
' GRAPH Macro
' Macro enregistrée le 14/10/2008 par utiliseur
Num=1
For n = 1 To Range("C65536").End(xlUp).Row
 If InStr(Range("C" & n).FormulaLocal, "SOUS.TOTAL") <> 0 Then 
ligne=n
    Charts.Add
    ActiveChart.ChartType = xl3DColumnClustered
    ActiveChart.SetSourceData Source:=Sheets("Général").Range("H" & ligne & ":L" & ligne),  PlotBy:=xlColumns
 
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Graphiques" & num
num=num+1
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Répartion de l'offre de formation par domaines"
        .Axes(xlCategory).HasTitle = True
        .Axes(xlCategory).AxisTitle.Characters.Text = "Domaine"
        .Axes(xlSeries).HasTitle = False
        .Axes(xlValue).HasTitle = True
        .Axes(xlValue).AxisTitle.Characters.Text = "Nombre"
    End With
 End If
 
Next n
End Sub
 
Dernière édition:
Vote positif 0 Vote négatif
P

PièceJointe

XLDnaute Nouveau
Re : Pointer une ligne relative dans une macro

Hélas, on tombe sur un code 1004 avec pour commentaire "la méthode 'range' de l'objet _GLOBAL a échoué"

Code incriminé... :
Code: 
If InStr(Range("C" & n).FormulaLocal, "SOUS.TOTAL") <> 0 Then

Pas simple cette histoire...
 
Vote positif 0 Vote négatif
pierrejean

pierrejean

XLDnaute Barbatruc
Re : Pointer une ligne relative dans une macro

Re

Toutes mes excuses
Apres avoir créé le 1er graphique la feuille active est le graphique
On ne peut donc pas se passer de nommer la feuille

Remplacer
Code: 
For n = 1 To Range("C65536").End(xlUp).Row
 If InStr(Range("C" & n).FormulaLocal, "SOUS.TOTAL") <> 0 Then
ligne = n

par
Code: 
For n = 1 To [COLOR=blue]Sheets("Général").[/COLOR]Range("C65536").End(xlUp).Row
 If InStr([COLOR=blue]Sheets("Général").[/COLOR]Range("C" & n).FormulaLocal, "SOUS.TOTAL") <> 0 Then
ligne = n
 
Vote positif 0 Vote négatif
P

PièceJointe

XLDnaute Nouveau
Re : Pointer une ligne relative dans une macro

Wahou mais ça marche !... je regarde dans le détail mais 1) c'est une macro qui tourne (pas de bug à l'horizon) et 2) la réponse fut tellement rapide... comment remercier ce forum qui en 5 jours à répondu à 2 de mes questions existentielles !!!

Merci bcp PierreJean pour ta réactivité...maintenant je vais essayer de comprendre...et vérifier que tout tourne comme prévu...
 
Vote positif 0 Vote négatif
P

PièceJointe

XLDnaute Nouveau
Re : Pointer une ligne relative dans une macro

Bon tout est impec , juste deux petites questions pour optimiser encore la chose étant donné que le fichier est hyper lourd...

1) Comment nommer chaque graph du nom de la cellule liée aux différents saut de sous total (dans mon exemple on saute à chaque nouvel établissement donc comment faire en sorte que le nom de l'onglet et la légende du graph soit raccord avec chaque établissement)...?
2) Comment selectionner pour mon histogramme non pas 1 plage (de H à L) mais 2 plages (de H à L et de P à R) ?

Merci d'avance pour vos conseils...
 
Vote positif 0 Vote négatif
P

PièceJointe

XLDnaute Nouveau
Re : Pointer une ligne relative dans une macro

Bon alors on oublie la question 2...j'ai trouvé...

A
Code: 
ctiveChart.SetSourceData Source:=Sheets("Général").Range( _
        "H217:L217,P217:R217,T217,V217,Z217"), PlotBy:=xlColumns

Pour la 1) on peut nommer els séries de cette facon... peut adapté quelque chose de ce genre ?

Code: 
ActiveChart.SeriesCollection(1).Name = "=Général!R5C8"

en ajoutant toujours cette fameuse référence relative ?

Voici ce que donne le code intégral actuellement...


Code: 
Sub GRAPH()
'
' GRAPH Macro
' Macro enregistrée le 14/10/2008 par utiliseur
num = 1
For n = 1 To Sheets("Général").Range("C65536").End(xlUp).Row
 If InStr(Sheets("Général").Range("C" & n).FormulaLocal, "SOUS.TOTAL") <> 0 Then
ligne = n
    
    Charts.Add
    ActiveChart.ChartType = xl3DColumnClustered
    ActiveChart.SetSourceData Source:=Sheets("Général").Range( _
        "H" & ligne & ":Z" & ligne), PlotBy:=xlColumns
    ActiveChart.SeriesCollection(1).Name = "=Général!R5C8"
    ActiveChart.SeriesCollection(2).Name = "=Général!R5C9"
    ActiveChart.SeriesCollection(3).Name = "=Général!R5C10"
    ActiveChart.SeriesCollection(4).Name = "=Général!R5C11"
    ActiveChart.SeriesCollection(5).Name = "=Général!R5C12"
    ActiveChart.SeriesCollection(6).Name = "=Général!R5C16"
    ActiveChart.SeriesCollection(7).Name = "=Général!R5C17"
    ActiveChart.SeriesCollection(8).Name = "=Général!R5C18"
    ActiveChart.SeriesCollection(9).Name = "=Général!R6C20"
    ActiveChart.SeriesCollection(10).Name = "=Général!R6C22"
    ActiveChart.SeriesCollection(11).Name = "=Général!R4C26"
 
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Graphique" & num
num = num + 1
    
 End If
 
Next n
End Sub

malheureusement il m'ajoute des séries (en fait lorsque il n'y a pas de données numérique je faudrait lui demander de ne pas ajouter de série...
 
Dernière édition:
Vote positif 0 Vote négatif
pierrejean

pierrejean

XLDnaute Barbatruc
Re : Pointer une ligne relative dans une macro

Re

Pour la question 1

Code: 
Sub GRAPH()
'
' GRAPH Macro
' Macro enregistrée le 14/10/2008 par utiliseur
num = 1
For n = 1 To Range("C65536").End(xlUp).Row
 If InStr(Sheets("Général").Range("C" & n).FormulaLocal, "SOUS.TOTAL") <> 0 Then
ligne = n
    Charts.Add
    ActiveChart.ChartType = xl3DColumnClustered
    ActiveChart.SetSourceData Source:=Sheets("Général").Range("H" & ligne & ":L" & ligne), PlotBy:=xlColumns
 [COLOR=blue]If Sheets("Général").Range("A" & n) = "" Then[/COLOR]
[COLOR=blue]   nom = "Total General"[/COLOR]
[COLOR=blue]Else[/COLOR]
[COLOR=blue]   nom = Sheets("Général").Range("A" & n)[/COLOR]
[COLOR=blue]End If[/COLOR]
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:=[COLOR=blue]nom[/COLOR]
num = num + 1
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Répartion de l'offre de formation par domaines"
        .Axes(xlCategory).HasTitle = True
        .Axes(xlCategory).AxisTitle.Characters.Text = "Domaine"
        .Axes(xlSeries).HasTitle = False
        .Axes(xlValue).HasTitle = True
        .Axes(xlValue).AxisTitle.Characters.Text = "Nombre"
    End With
 End If
 
Next n
End Sub

tu pourras adapter eventuellement
 
Dernière édition:
Vote positif 0 Vote négatif

Discussions similaires

L
  • Question
XL 2013 positionnement de plusieurs graphique dans une plage de données sur des lignes différente
Réponses
9
Affichages
1 K
Lionel7918e
L
B
  • Question
Ajouter une nouvelle courbe sur un graphique en fonction du ieme clic
Réponses
3
Affichages
1 K
chris
chris
C
  • Question
VBA macro graphique
Réponses
4
Affichages
2 K
Cloud
C
A
  • Question
MACRO/VBA Graphique spécifique en fonction des valeurs d'une 3ème colonne
Réponses
8
Affichages
3 K
Misange
Misange
N
  • Question
Booster une macro
Réponses
5
Affichages
3 K
Staple1600
Staple1600
E
  • Question
Probleme d'etiquette de donnée
Réponses
4
Affichages
1 K
eizo
E
B
  • Question
Probleme enregistrement macro : activechart
Réponses
0
Affichages
1 K
Benzema
B
M
  • Question
concorddance plage de données/lignes dans macro
Réponses
6
Affichages
1 K
matsam
M
S
  • Question
macro graphique VBA
Réponses
3
Affichages
3 K
shakapouet
S
B
  • Question
Transmettre adresse cellule entre fonctions
Réponses
1
Affichages
991
tototiti2008
tototiti2008

Membres actuellement en ligne

17 membres et 82 invités.

Partager cette page

Tutos vidéos

Power Query : Ajouter des requêtes (Excel et Power BI)
Créez votre première fonction LAMBDA dans Excel en 3 étapes!
LAMBDA, l’arme secrète de Microsoft pour Excel !
Office Insider - Découvrez les nouveautés Excel avant tout le monde

Statistiques des forums

Discussions
312 496
Messages
2 088 974
Membres
103 995
dernier inscrit
Flodk