Bonjour,
J'essaye désespérement de créer un graphique croisé en vba. La macro bloque sur la ligne en rouge avec message d'erreur Erreur d'execution 91
Variable objet ou variable de bloc with non définie
J'ai cherché pendant des heures mais je ne trouve pas la solution.
Démarrant depuis peu en VBA j'en appelle aux experts.
Merci d'avance

Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
'Début graph croisé
    
    Sheets("BD").Select
    Dim lastrow1
    Range("A1").Select
    
    
lastrow1 = Range("A1").End(xlDown).Row
        ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "BD!R1C1:R" & lastrow1 & "C9").CreatePivotTable TableDestination:= _
        "'Graph'!R7C1", TableName:= _
        "Tableau croisé dynamique2", DefaultVersion:=xlPivotTableVersion10
    
    Sheets("Graph").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique2").AddFields RowFields:= _
        Array("theme", "Catégorie", "Données")
    With ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields("Note")
        .Orientation = xlDataField
        .Caption = "Moyenne de Note"
        .Position = 1
        .Function = xlAverage
        .NumberFormat = "0,00%"
    End With
    With ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields("Niveau%" _
        )
        .Orientation = xlDataField
        .Caption = "Moyenne de Niveau%"
        .Function = xlAverage
        .NumberFormat = "0,00%"
    End With
    
    
    Charts.Add
    ActiveChart.Name = "Graphique"
    
    
    Charts("Graphique").Select
    ActiveChart.Location Where:=xlLocationAsNewSheet
    ActiveWorkbook.ShowPivotTableFieldList = False
    Charts("Graphique").Select
    
    With ActiveChart.PivotLayout.PivotTable.DataPivotField
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.Location Where:=xlLocationAsNewSheet
    With ActiveChart
        .HasTitle = False
        .Axes(xlCategory, xlPrimary).HasTitle = False
        .Axes(xlValue, xlPrimary).HasTitle = False
    End With
    ActiveChart.SeriesCollection(1).Select
    With Selection.Border
        .Weight = xlThin
        .LineStyle = xlAutomatic
    End With
    Selection.Shadow = False
    Selection.InvertIfNegative = False
    Selection.Fill.OneColorGradient Style:=msoGradientVertical, Variant:=4, _
        Degree:=0.231372549019608
    With Selection
        .Fill.Visible = True
        .Fill.ForeColor.SchemeColor = 17
    End With
    ActiveChart.SeriesCollection(2).Select
    ActiveChart.SeriesCollection(2).Points(1).Select
    ActiveChart.ChartArea.Select
    ActiveChart.SeriesCollection(2).Select
    With Selection.Border
        .Weight = xlThin
        .LineStyle = xlAutomatic
    End With
    Selection.Shadow = False
    Selection.InvertIfNegative = False
    Selection.Fill.OneColorGradient Style:=msoGradientVertical, Variant:=4, _
        Degree:=0.231372549019608
    With Selection
        .Fill.Visible = True
        .Fill.ForeColor.SchemeColor = 19
    End With
    ActiveChart.PlotArea.Select
    With Selection.Border
        .ColorIndex = 16
        .Weight = xlThin
        .LineStyle = xlContinuous
    End With
    With Selection.Interior
        .ColorIndex = 47
        .PatternColorIndex = 1
        .Pattern = xlSolid
    End With
    ActiveChart.Axes(xlValue).Select
    With ActiveChart.Axes(xlValue)
        .MinimumScaleIsAuto = True
        .MaximumScale = 1
        .MinorUnitIsAuto = True
        .MajorUnitIsAuto = True
        .Crosses = xlAutomatic
        .ReversePlotOrder = False
        .ScaleType = xlLinear
        .DisplayUnit = xlNone
    End With
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SeriesCollection(1).Trendlines.Add(Type:=xlPolynomial, Order:=2 _
        , Forward:=0, Backward:=0, DisplayEquation:=False, DisplayRSquared:= _
        False).Select
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SeriesCollection(1).Trendlines.Add(Type:=xlLinear, Forward:=0, _
        Backward:=0, DisplayEquation:=False, DisplayRSquared:=False).Select
    ActiveChart.SeriesCollection(1).Trendlines(2).Select
    ActiveChart.SeriesCollection(1).Trendlines(2).Select
    Selection.Delete
    ActiveChart.PlotArea.Select
    ActiveChart.SeriesCollection(1).Trendlines(1).Select
    ActiveChart.SeriesCollection(1).Trendlines(1).Select
    Selection.Delete