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
| Sub Graphe()
Dim LastLig As Long, i As Long
Dim X As Range, Y As Range
Dim Ch As ChartObject
Dim Sem As String
Dim Tb() As Double
Application.ScreenUpdating = False
With Worksheets("Feuil1")
LastLig = .Cells(.Rows.Count, 1).End(xlUp).Row
Set X = .Range("A1:A" & LastLig)
Set Y = .Range("B1:B" & LastLig)
Set Ch = .ChartObjects.Add(300, 50, 400, 250)
End With
With Ch.Chart
.ChartType = xlLineStacked
With .SeriesCollection.NewSeries
.Values = Y
.XValues = X
End With
'Semaine dernière sous forme ANNEE/N°SEMAINE
Sem = Year(DateAdd("ww", -1, Date)) & "/" & DatePart("ww", DateAdd("ww", -1, Date), vbMonday)
ReDim Tb(1 To LastLig)
For i = 1 To LastLig
Tb(i) = IIf(X(i) = Sem, Application.Max(Y), 0)
Next i
Set X = Nothing
Set Y = Nothing
With .SeriesCollection.NewSeries
.Values = Tb
.ChartType = xlColumnClustered
End With
End With
Set Ch = Nothing
End Sub |
Partager