Insertion de graphique en VBA

Bonjour à tous,

je viens à vous car j'essaie de réaliser une opération qui est encore un peu trop compliquée pour moi, j'essaie depuis hier soir sans résultat concluant.

J'ai un code dans le module ratio_corrélation utilisé sur la feuille corrélation_ratio qui me permet de calculer la corrélation entre des données issues de deux colonnes, je choisis les colonnes grâce à une liste déroulante en B5 B7 B9 B11. une fois les noms de postes choisis le code fait une recherche dans la feuille_données, selectionne les deux colonnes correspondantes et calcul mon coefficient de corrélation qui ressort respectivement sous les cellules de choix de colonnes.

J'aimerai cependant une autre option , qui me permettrait de reprendre les mêmes colonnes mais cette fois non pas pour calculer le coeff de corrélation mais pour avoir les données représentées sous forme de graphique (nuage de point) pour chaque postes (dénominateur, donc 4 graphiques) histoire d'avoir un aspect graphique de la dispersion en plus du résultat. Petite précision je veux qu'il s'affiche sur la même feuille, 2 par 2 entre D et H mais je pense pouvoir gérer le code de disposition .

J'avais réussi à intégrer les selections au graphique en lui affiliant les variables rng pour avoir rng en ordonnée, et rng(1,2..) en abscisse mais lorsque je lançais les calcules, les graphiques s'emplilaient, ou alors ils ne se mettaient pas à jours. J'aimerai donc qu'à chaque fois que les postes changes, les données du graphiques changent.

Voici mon fichier pour mieux visualiser, (données privées supprimées), le code que j'avais pour les graphique est en vert, je l'avais généré avec une macro en tentant tant bien que mal de l'adapter.

Sub coefcorrelation()

Dim rng As Range, valeur As String, lCol As Long, lRow As Long
Dim rng1 As Range, valeur1 As String, lCol1 As Long, lRow1 As Long
Dim rng2 As Range, valeur2 As String, lCol2 As Long, lRow2 As Long
Dim rng3 As Range, valeur3 As String, lCol3 As Long, lRow3 As Long
Dim rng4 As Range, valeur4 As String, lCol4 As Long, lRow4 As Long

    valeur = Cells(3, 2).Value
    valeur1 = Cells(5, 2).Value
    valeur2 = Cells(7, 2).Value
    valeur3 = Cells(9, 2).Value
    valeur4 = Cells(11, 2).Value

    lCol = Application.Match(valeur, Sheets("Base_Données").Range("A5:GJ5"), 0)
    lCol1 = Application.Match(valeur1, Sheets("Base_Données").Range("A5:GJ5"), 0)
    lCol2 = Application.Match(valeur2, Sheets("Base_Données").Range("A5:GJ5"), 0)
    lCol3 = Application.Match(valeur3, Sheets("Base_Données").Range("A5:GJ5"), 0)
    lCol4 = Application.Match(valeur4, Sheets("Base_Données").Range("A5:GJ5"), 0)

    If Err.Number = 0 Then
        With Worksheets("Base_Données")
            lRow = .Cells(.Rows.Count, lCol).End(xlUp).Row
            Set rng = .Cells(6, lCol).Resize(lRow - 5)
            lRow1 = .Cells(.Rows.Count, lCol1).End(xlUp).Row
            Set rng1 = .Cells(6, lCol1).Resize(lRow1 - 5)
            lRow2 = .Cells(.Rows.Count, lCol2).End(xlUp).Row
            Set rng2 = .Cells(6, lCol2).Resize(lRow2 - 5)
            lRow3 = .Cells(.Rows.Count, lCol3).End(xlUp).Row
            Set rng3 = .Cells(6, lCol3).Resize(lRow3 - 5)
            lRow4 = .Cells(.Rows.Count, lCol4).End(xlUp).Row
            Set rng4 = .Cells(6, lCol4).Resize(lRow4 - 5)
        End With

        Cells(6, 2).Value = WorksheetFunction.Correl(rng, rng1)
        Cells(8, 2).Value = WorksheetFunction.Correl(rng, rng2)
        Cells(10, 2).Value = WorksheetFunction.Correl(rng, rng3)
        Cells(12, 2).Value = WorksheetFunction.Correl(rng, rng4)

                   ' ActiveSheet.Shapes.AddChart2(240, xlXYScatter).Select
              'ActiveChart.ChartArea.Select
             ' ActiveChart.SeriesCollection.NewSeries
             ' ActiveChart.FullSeriesCollection(1).Name = valeur1
             ' ActiveChart.FullSeriesCollection(1).XValues = rng1
             ' ActiveChart.FullSeriesCollection(1).Values = rng
         '
          ' ActiveSheet.Shapes.AddChart2(240, xlXYScatter).Select
              'ActiveChart.ChartArea.Select
             ' ActiveChart.SeriesCollection.NewSeries
             ' ActiveChart.FullSeriesCollection(1).Name = valeur2
             ' ActiveChart.FullSeriesCollection(1).XValues = rng2
              'ActiveChart.FullSeriesCollection(1).Values = rng

          ' ActiveSheet.Shapes.AddChart2(240, xlXYScatter).Select
              'ActiveChart.ChartArea.Select
             ' ActiveChart.SeriesCollection.NewSeries
             ' ActiveChart.FullSeriesCollection(1).Name = valeur3
             ' ActiveChart.FullSeriesCollection(1).XValues = rng3
             ' ActiveChart.FullSeriesCollection(1).Values = rng

           ' ActiveSheet.Shapes.AddChart2(240, xlXYScatter).Select
              'ActiveChart.ChartArea.Select
             ' ActiveChart.SeriesCollection.NewSeries
             ' ActiveChart.FullSeriesCollection(1).Name = valeur4
              'ActiveChart.FullSeriesCollection(1).XValues = rng4
              'ActiveChart.FullSeriesCollection(1).Values = rng '

    End If

End Sub

Bonjour,

Un début de réponse à adapter.

Cdlt.

Bonjour à toi Jean Eric,

Merci beaucoup pour ta réponse, je pense comprendre à peu prés le code mais ca ne fonctionne pas exactement, d'ou le besoin d'adapter j'imagine comme tu as dis. Voila ce que j'ai compris , et au passage j'ai des petites questions .... étant débutant en VBA j'essaie de récupérer le maximum de connaissances à chaque fois que l'on m'aide.

Pour commencer je suis pas sur de comprendre les lignes avec les points d'interrogation, y compris aprés une recherche F1 sur le terme.

J'avais aussi déclaré chaque variable lrow peux tu m'expliquer pourquoi une seule est nécessaire? J'ai vu que tu n'as mis qu'une seule ligne pour chaque variable rng.

Sub coefcorrelation()

Dim rng As Range, valeur As String, lCol As Long, lRow As Long
Dim rng1 As Range, valeur1 As String, lCol1 As Long, lRow1 As Long
Dim rng2 As Range, valeur2 As String, lCol2 As Long, lRow2 As Long         'définition des variables comme dans le code précédent'
Dim rng3 As Range, valeur3 As String, lCol3 As Long, lRow3 As Long
Dim rng4 As Range, valeur4 As String, lCol4 As Long, lRow4 As Long
Dim r As Range                                               'déclaration de la cellule référence pour le positionnement du graphique
Dim objChart As ChartObject                      'déclaration du nom des graphique
Dim sr As Series                        '????'
Dim i As Long, j As Long                '????'

    Application.ScreenUpdating = False           '?????'

    With ActiveSheet                        'dans la feuille active'
        For Each objChart In .ChartObjects                'pour chaque graphique 
            objChart.Delete                        'supprimer le graphique et afficher le suivant
        Next objChart
        valeur = .Cells(3, 2).Value        'déclaration des variables cellules textes de références'
        valeur1 = .Cells(5, 2).Value
        valeur2 = .Cells(7, 2).Value
        valeur3 = .Cells(9, 2).Value
        valeur4 = .Cells(11, 2).Value
    End With

    With Worksheets("Base_Données")
        lCol = Application.Match(valeur, .Range("A5:GJ5"), 0)
        lCol1 = Application.Match(valeur1, .Range("A5:GJ5"), 0)
        lCol2 = Application.Match(valeur2, .Range("A5:GJ5"), 0)            'dans la feuille base_données , chercher les valeurs de la ligne 5 
        lCol3 = Application.Match(valeur3, .Range("A5:GJ5"), 0)            'correspondant aux variables valeurs'
        lCol4 = Application.Match(valeur4, .Range("A5:GJ5"), 0)
    End With

    With Worksheets("Base_Données")
        lRow = .Cells(.Rows.Count, lCol).End(xlUp).Row        'petite doute sur celle la
        Set rng = .Cells(6, lCol).Resize(lRow - 5)                'déclaration des colonnes à selectionner, début ligne 6, colonne
        Set rng1 = .Cells(6, lCol1).Resize(lRow - 5)            'correspondant à la valeur, suite ???
        Set rng2 = .Cells(6, lCol2).Resize(lRow - 5)                    
        Set rng3 = .Cells(6, lCol3).Resize(lRow - 5)
        Set rng4 = .Cells(6, lCol4).Resize(lRow - 5)
    End With

    With ActiveSheet
        .Cells(6, 2).Value = WorksheetFunction.Correl(rng, rng1)
        .Cells(8, 2).Value = WorksheetFunction.Correl(rng, rng2)            'dans la feuille active renvoyer la formule coeff correl des deux '
        .Cells(10, 2).Value = WorksheetFunction.Correl(rng, rng3)            'colonnes choisis dans les cellules précisées'
        .Cells(12, 2).Value = WorksheetFunction.Correl(rng, rng4)
        Set r = .Cells(2, 4)                                                'mettre r point de départ du graph sur la cellule 2,4'
        Set objChart = .ChartObjects.Add(r.Left, r.Top, 550, 250)            'emplacement graph'
        objChart.Name = "Chart_1"                                    'déclaration du nom du graph'
        With objChart.Chart                                            'pour le graphique utiliser le type xlXY...'
            .ChartType = xlXYScatter
            .HasLegend = False
            .SeriesCollection.NewSeries
            .FullSeriesCollection(1).Name = valeur1                'nom du graph à afficher'
            .FullSeriesCollection(1).XValues = rng1                'valeur en abscicce = plage de données rng'
            .FullSeriesCollection(1).Values = rng                    ''valeur en ordonnée = plage de données rng'
        End With                                            'même démarche pour les graph suivants'
        Set r = .Cells(20, 4)
        Set objChart = .ChartObjects.Add(r.Left, r.Top, 550, 250)
        objChart.Name = "Chart_2"
        With objChart.Chart
            .ChartType = xlXYScatter
            .HasLegend = False
            .SeriesCollection.NewSeries
            .FullSeriesCollection(1).Name = valeur2
            .FullSeriesCollection(1).XValues = rng2
            .FullSeriesCollection(1).Values = rng
        End With
        Set r = .Cells(2, 14)
        Set objChart = .ChartObjects.Add(r.Left, r.Top, 550, 250)
        objChart.Name = "Chart_3"
        With objChart.Chart
            .ChartType = xlXYScatter
            .HasLegend = False
            .SeriesCollection.NewSeries
            .FullSeriesCollection(1).Name = valeur3
            .FullSeriesCollection(1).XValues = rng3
            .FullSeriesCollection(1).Values = rng
        End With
        Set r = .Cells(20, 14)
        Set objChart = .ChartObjects.Add(r.Left, r.Top, 550, 250)
        objChart.Name = "Chart_4"
        With objChart.Chart
            .ChartType = xlXYScatter
            .HasLegend = False
            .SeriesCollection.NewSeries
            .FullSeriesCollection(1).Name = valeur4
            .FullSeriesCollection(1).XValues = rng4
            .FullSeriesCollection(1).Values = rng
        End With
    End With

End Sub

Autres question peux tu m'expliquer à quoi sert le code présent sur la feuille elle même

Private Sub Worksheet_Activate()
    coefcorrelation
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Not Intersect(Target, Range("B5,B7,B9,B11")) Is Nothing Then
        coefcorrelation
    End If
End Sub 

De ce que je lis le code me parait bien adapter , toutes les cellules choisis sont bien les bonnes et pourtant j'ai le message

"erreur d'éxécution 1004 impossible de lire la propriété correl de la classe worksheetfunction. Pourrais-tu me dire d'où cela vient?

Bonjour,

C'était un début de réponse (pas optimisé) et j'ai opté pour la simplicité.

Je joins le fichier avec mes commentaires et quelques modifications.

Au sujet de lRow unique, j'ai considéré que toutes les plages avaient la même dimension !?

Pour tes erreurs 1004, c'est une erreur Excel : Les plages sont vides !...

A te relire.

Cdlt.

Merci Jean Eric je n'avais pas fais attention que les valeurs que j'avais prises pour tester le code faisaient parties des colonnes que j'avais vidées .

Le code fonctionne parfaitement, merci pour tes commentaires explicatives dans les feuilles de code, ils m'aident beaucoup.