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