Hi
I am having problem with a macro used to create a line chart. There are four Y series plotted. Because two of the Y series are moving averages, there are some number of blank cells determined by the moving average period before the moving average data are displayed.
Both the moving average series plot out coincident with the first x value. There should instead be a gap before the data are plotted. I tried the feature "Show empty Cells as Gaps" but it does nothing. Maybe this is because the chart is created using VBA. Below is the code used to create the chart:
Can anyone suggest a code change that will allow for the gaps in the data in columns S and T and plot the data correctly?
Thanks,
Art
I am having problem with a macro used to create a line chart. There are four Y series plotted. Because two of the Y series are moving averages, there are some number of blank cells determined by the moving average period before the moving average data are displayed.
Both the moving average series plot out coincident with the first x value. There should instead be a gap before the data are plotted. I tried the feature "Show empty Cells as Gaps" but it does nothing. Maybe this is because the chart is created using VBA. Below is the code used to create the chart:
Code:
Sub CreateChart()
Dim MyChart As Chart
Dim MyRange As Range
Dim LastRow As Long
Dim srs As Series
Application.ScreenUpdating = False
On Error Resume Next
Sheets("Chart").Select
ActiveSheet.ChartObjects.Select
ActiveSheet.ChartObjects.Delete
On Error GoTo 0
LastRow = Worksheets("Data").Cells(Rows.Count, "E").End(xlUp).Row
If LastRow < 2 Then
MsgBox "No data is available...", vbInformation
Exit Sub
End If
Set MyRange = Worksheets("Data").Range("E2:E" & LastRow & ",K2:K" & LastRow & ",R2:R" & LastRow & ",S2:S" & LastRow & ",T2:T" & LastRow)
Set MyChart = Worksheets("Chart").Shapes.AddChart(xlLine).Chart
MyChart.SetSourceData Source:=MyRange, PlotBy:=xlColumns
Worksheets("Chart").ChartObjects(1).Activate
ActiveChart.HasTitle = True
ActiveChart.ChartTitle.Text = "Inverse Stock Pair"
Set srs = ActiveChart.SeriesCollection(1)
srs.Name = Worksheets("Chart").Range("B1").Value
Set srs = ActiveChart.SeriesCollection(2)
srs.Name = Worksheets("Chart").Range("C1").Value
With ActiveChart
.ChartTitle.Font.Size = 12
.ChartTitle.Font.Name = "Arial Unicode MS"
.SeriesCollection(1).Format.Line.Weight = 1.25
.SeriesCollection(2).Format.Line.Weight = 1.25
.Axes(xlCategory).TickLabels.Orientation = 45
End With
With ActiveChart.Parent
.Height = 325 ' resize
.Width = 500 ' resize
.Top = 75 ' reposition
.Left = 275 ' reposition
End With
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = True
.HasMinorGridlines = False
.MajorTickMark = xlNone
.MinorTickMark = xlNone
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
.MajorTickMark = xlNone
.MinorTickMark = xlNone
End With
Application.ScreenUpdating = True
Call ScaleCharts
End Sub
Can anyone suggest a code change that will allow for the gaps in the data in columns S and T and plot the data correctly?
Thanks,
Art