Sub CreateChart()
Dim ser As Series
Dim WString As String
Dim ArraySize As Long
ArraySize = UBound(AM)
Dim XValues() As Date
Dim YValues2() As Double
Dim Backup() As Long
Dim Positions() As Long
For i = 1 To UBound(AM) + 1
ReDim Preserve Positions(i - 1)
Positions(i - 1) = WorksheetFunction.Match(WorksheetFunction.Min(AL), AL, 0) - 1
ReDim Preserve Backup(i - 1)
ReDim Preserve YValues2(i - 1)
Backup(i - 1) = CLng(AL(Positions(i - 1)))
YValues2(i - 1) = AM(Positions(i - 1))
AL(Positions(i - 1)) = Empty
Next i
Worksheets("Product Search 2").Shapes.AddChart.Select
ActiveChart.ChartType = xlLineMarkers
ActiveChart.HasTitle = True
ActiveChart.ChartTitle.Text = "Time plot of price (£/kg)"
ActiveChart.ChartTitle.Font.Size = 25
For i = ActiveChart.SeriesCollection.Count To 1 Step -1
ActiveChart.SeriesCollection(i).Delete
Next i
Set ser = ActiveChart.SeriesCollection.NewSeries
With ser
.XValues = Backup
.Values = YValues2
End With
With ActiveChart
With .Axes(xlCategory, xlPrimary)
.CategoryType = xlTimeScale
.TickLabels.NumberFormat = "dd/mmm/yyyy"
.TickLabels.Font.Size = 22
.HasTitle = True
.AxisTitle.Characters.Text = "Date"
.AxisTitle.Font.Size = 25
'.MajorUnitScale = xlAutomatic
'.TickLabelSpacingIsAuto = True
MajorUnit = 4
End With
With .Axes(xlValue, xlPrimary)
.TickLabels.Font.Size = 22
.HasTitle = True
.AxisTitle.Characters.Text = "Price (£/kg)"
.AxisTitle.Font.Size = 25
End With
End With
Bookmarks