First time poster. To make this cumulative disti chart I make a straight lined xy scatter plot. The y axis values are the percentiles, all of which are in A2:A100. The corresponding x values are in Columns B through n. n being variable as it depends on how many simulated strategies are designated by the user for chart creation. I need the VBA code to create an xy scatter plot where A2:A100 is the y values for all x value series that are located in the adjascent columns. A named cell, cumulativeColumns counts the number columns with x values. My first thought based on my research of how SeriesCollections seemed to work was to use a Do Until Loop that would stop based on the result of the cumulativeColumns formula. I thought this code would work:
q = 1
Do Until q = Range("cumulativeColumns").Value
ActiveChart.SeriesCollection(q).XValues = _
"=Range(Cells(2,q+1), Cells(100,q+1))"
ActiveChart.SeriesCollection(q).Values = _
"=Range(Cells(2,1), Cells(100,1))"
q = q + 1
Loop
The first time through the loop q = 1 so the 1st series will get Col B data. The second time through the loop q = 2 so I figured the 2nd series would be defined and get the data in Column C, etc. until it stops when there are no more columns with data per the result of the cumulativeColumns.Value. But I get an error 1004, application defined or object defined error.
Tested:
q = 1
Do Until q = Range("cumulativeColumns").Value
With ActiveChart.SeriesCollection.NewSeries
.XValues = Range(Cells(2, q + 1), Cells(100, q + 1))
.Values = Range(Cells(2, 1), Cells(100, 1))
q = q + 1
End With
Loop