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
| Sub Graph()
Dim LastLig As Long, i As Long
Dim Mn As Long, Mx As Long, Klr As Long
Dim Ch As ChartObject
Dim DATA
Application.ScreenUpdating = False
With Worksheets("Feuil3")
On Error Resume Next
.ChartObjects("TonNom").Delete
On Error GoTo 0
LastLig = .Cells(.Rows.Count, 1).End(xlUp).Row
Mn = Application.Min(.Range("B2:B" & LastLig))
Mx = Application.Max(.Range("B2:B" & LastLig))
Set Ch = .ChartObjects.Add(240, 50, 600, 340)
With Ch
.Name = "TonNom"
With .Chart
.ChartArea.ClearContents
.ChartType = xlBubble
.HasTitle = False
.HasLegend = False
.Axes(xlValue, xlPrimary).HasTitle = False
With .Axes(xlCategory, xlPrimary)
.HasTitle = False
.TickLabels.NumberFormat = "mmm yyyy"
.MinimumScale = Mn - 30
.MaximumScale = Mx + 30
.MajorUnit = 31
End With
End With
End With
For i = 2 To LastLig
DATA = .Range("A" & i).Resize(, 4)
Klr = .Range("A" & i).Interior.Color
With Ch.Chart.SeriesCollection.NewSeries
.Name = DATA(1, 1)
.BubbleSizes = DATA(1, 4)
.Values = DATA(1, 3)
.XValues = CLng(DATA(1, 2))
.Format.Fill.ForeColor.RGB = Klr
.ApplyDataLabels
.DataLabels.ShowSeriesName = True
.DataLabels.ShowValue = False
.DataLabels.Font.Color = Klr
End With
Next i
End With
Set Ch = Nothing
End Sub |
Partager