Excel VBA Script to dynamically add series to chart
thanks for the help. I solved the problem. It seems as I have somehow completely messed up the notation of the cell range. You cannot use
Set xRng = Sheets("Data").Range("R14:C3", "R" & LastRow & ":C3")
But rather have to use
Set xRng = .Range(.Cells(14, 3), .Cells(LastRow, 3))
Also, the use of Charts.Add didnt help very much as Excel tries to automatically find the correct ranges for all series and adds them resulting in a completely messed up chart. A better way was to use
Set ChartObj = ActiveSheet.ChartObjects.Add(Left:=20, Width:=800, Top:=20, Height:=500)
As this will create a completely empty graph to which you can add your own series
Here is the complete and working code for anyone interested:
Sub MakeChart() Dim LastRow As Long Dim LastColumn As Long Dim ColumnCount As Long LastRow = ActiveSheet.Range("C" & ActiveSheet.Rows.Count).End(xlUp).Row LastColumn = ActiveSheet.Cells(8, Columns.Count).End(xlToLeft).Column ColumnCount = LastColumn - 4 Debug.Print ("Last Column: " & LastColumn & " Count: " & ColumnCount & " LastRow: " & LastRow) Dim wsChart As Worksheet Set wsChart = Sheets(1) wsChart.Activate Dim ChartObj As ChartObject Set ChartObj = ActiveSheet.ChartObjects.Add(Left:=20, Width:=800, Top:=20, Height:=500) ChartObj.chart.ChartType = xlLineMarkers Dim i As Integer Dim u As Integer Dim NameRng As String Dim xRng As Range Dim CountsRng As Range For i = 1 To ColumnCount u = i + 4 With Sheets("Data") NameRng = .Cells(9, u).Value Set CountsRng = .Range(.Cells(14, u), .Cells(LastRow, u)) Set xRng = .Range(.Cells(14, 3), .Cells(LastRow, 3)) Debug.Print "--" & i & "--" & u & "--" Debug.Print "x Range: " & xRng.Address Debug.Print "Name Range: " & .Cells(9, u).Address Debug.Print "Value Range: " & CountsRng.Address End With 'Set ChartSeries = ChartObj.chart.SeriesCollection.NewSeries 'With ActiveChart.SeriesCollection.NewSeries With ChartObj.chart.SeriesCollection.NewSeries .XValues = xRng .Values = CountsRng .Name = NameRng End With 'Set xRng = Nothing 'Set CountsRng = Nothing 'NameRng = "" Next i 'ChartObj.Activate With ChartObj.chart .SetElement (msoElementLegendBottom) .Axes(xlValue).MajorUnit = 1 .Axes(xlValue).MinorUnit = 0.5 .Axes(xlValue).MinorTickMark = xlOutside '.Axes(xlCategory).TickLabels.NumberFormat = "#,##000" .Axes(xlCategory).TickLabels.NumberFormat = "#,##0" '.Location Where:=xlLocationAsObject, Name:="Plot" End WithEnd Sub
sample code
Sub InsertChart() Dim first As Long, last As Long first = 10 last = 20 Dim wsChart As Worksheet Set wsChart = Sheets(1) wsChart.Activate wsChart.Shapes.AddChart.Select Dim chart As chart Set chart = ActiveChart chart.ChartType = xlXYScatter ' adding series chart.SeriesCollection.NewSeries chart.SeriesCollection(1).Name = "series name" chart.SeriesCollection(1).XValues = "=" & ActiveSheet.Name & "!$A$" & first & ":$A$" & last chart.SeriesCollection(1).Values = "=" & ActiveSheet.Name & "!$B$" & first & ":$B$" & lastEnd Sub
you can iterate over range and keep adding more series