I'm trying to plot two series on one chart, the first showing actuals over time and the second showing the maximum they are approaching.
The first line shows up.
The second series is created and shows in the legend, but no data appears on the plot.
Simplified example:
Sub SimpleDebug()
Dim test_sheet As Worksheet
Set test_sheet = ThisWorkbook.Worksheets("TestData")
Dim ch As Chart
test_sheet.ChartObjects.Add Left:=750, Top:=50, Width:=400, Height:=300
Set ch = test_sheet.ChartObjects(1).Chart
Dim ser As Series
Set ser = ch.SeriesCollection.NewSeries
ser.ChartType = xlLine
ser.XValues = test_sheet.Range("F2:F278")
ser.Values = test_sheet.Range("K2:K278")
ser.name = "Running Total"
Dim max_val As Double
max_val = 175000
Set ser = ch.SeriesCollection.NewSeries
ser.ChartType = xlLine
Dim min_date As Date
Dim max_date As Date
min_date = test_sheet.Range("F2").Value
Debug.Print "min_date: " & min_date
max_date = test_sheet.Cells(278, 6).Value
Debug.Print "max_date: " & max_date
ser.XValues = Array(min_date, max_date)
ser.Values = Array(max_val, max_val)
ser.name = "Maximum"
End Sub
The debug print statements confirm that I get the values that I expect from the sheet for min_date and max_date.
The second series doesn't appear.
I tried setting the second series as an independent variable rather than re-using 'ser' and it didn't make a difference.
Instead of using the min and max dates, just use the same XValues
as the first Series
, and create an array of the same size for the Values
:
Dim arr() As Double
ReDim arr(1 To test_sheet.Range("F2:F278").Count)
Dim i As Long
For i = LBound(arr) To UBound(arr)
arr(i) = max_val
Next
ser.XValues = test_sheet.Range("F2:F278")
ser.Values = arr