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
| Option Base 1
Sub Macro4()
Dim NbLig As Integer, Graph As Shape, Valeurs() As Single, xValeurs() As String, Ctr As Integer
Dim Couleur() As Integer, Couleurs As Variant
Couleurs = Array(4, 3, 32, 15, 17)
NbLig = Cells.SpecialCells(xlCellTypeLastCell).Row
For i = 2 To NbLig
ReDim Valeurs(1)
ReDim xValeurs(1)
ReDim Couleur(1)
Ctr = 0
Sheets.Add After:=ActiveSheet 'création d'une nouvelle feuille après la feuille active '
Set Graph = ActiveSheet.Shapes.AddChart2(262, xl3DPie)
With Graph.Chart
For j = 1 To 5
If Sheets("fichier").Cells(i, j) > 0 And IsNumeric(Sheets("fichier").Cells(i, j)) Then
Ctr = Ctr + 1
ReDim Preserve Valeurs(Ctr)
ReDim Preserve xValeurs(Ctr)
ReDim Preserve Couleur(Ctr)
Valeurs(Ctr) = Sheets("fichier").Cells(i, j)
xValeurs(Ctr) = Sheets("fichier").Cells(1, j)
Couleur(Ctr) = Application.Index(Couleurs, j)
End If
Next j
.ChartTitle.Text = Sheets("fichier").Cells(i, 6).Value
.SeriesCollection.NewSeries
With .SeriesCollection(1)
.Values = Valeurs
.XValues = xValeurs
.Explosion = 40
For k = 1 To UBound(Couleur)
.Points(k).Interior.ColorIndex = Couleur(k)
Next k
.ApplyDataLabels
With .DataLabels.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 255, 255)
.Solid
End With
With .DataLabels
.ShowPercentage = True
.ShowValue = False
End With
End With
End With
Next i
End Sub |
Partager