CODEWITHSUNDEEP
excelvbachartsseries
Andrea Tarquinio
Andrea Tarquinio

Reputation: 3

Adding new series to a graph automatically

I'm trying to create a dynamic Scatterchart in the worksheet("Graphs") using a button.

The Seriesname has to be equal to Worksheets("VS_P240_X").Cells(1,i), where i is a counter for the columns.

The XValues have to be equal to Worksheets("VS_P240_X").Range(cells(3,i).cells(1000,i)).

The YValues have to be equal to Worksheets("VS_P240_Y").Range(cells(3,i).cells(1000,i)).

When I Update the workbook the counter i will change, and I want that the chart will automatically update with the new series. I wrote this code but it is not working, do you have some suggestions?

Private Sub CommandButton5_Click()

'Graph generation NON COMPLETO
Dim i As Integer


Dim Chart1 As Chart
    Set Chart1 = Sheets("Graphs").ChartObjects("Chart 1").Chart

    For i = 1 To Lastcolumn
    
         With Chart1
         
                .ChartType = xlXYScatter
                .SeriesCollection.NewSeries
                'Change to what your series should be called
                .SeriesCollection(i).Name = Worksheets("VS_P240_X").Cells(1, i).Value
                .SeriesCollection(i).XValues = Worksheets("VS_P240_X").Range(Cells(3, i), Cells(1000, i))
                .SeriesCollection(i).Values = Worksheets("VS_P240_Y").Range(Cells(3, i), Cells(1000, i))
        
            End With
    
    Next i
    
End Sub

Upvotes: 0

Views: 50

Answers (1)

Tim Williams
Tim Williams

Reputation: 166126

To avoid confusion/bugs, it's best to specify a worksheet every time you use Range or Cells()

Something like this should work:

Private Sub CommandButton5_Click()

    Dim i As Long, Lastcolumn As Long 'use long not integer
    Dim Chart1 As Chart, wsX As Worksheet, wsY As Worksheet
    
    Set wsX = Worksheets("VS_P240_X")
    Set wsY = Worksheets("VS_P240_Y")
    
    Set Chart1 = Sheets("Graphs").ChartObjects("Chart 1").Chart
    Do While Chart1.SeriesCollection.Count > 0 'remove any existing data
        Chart1.SeriesCollection(1).Delete
    Loop
    Chart1.ChartType = xlXYScatter 'do this outside of the loop...
    
    Lastcolumn = wsX.Cells(1, wsX.Columns.Count).End(xlToLeft).Column
    
    For i = 1 To Lastcolumn
        With Chart1.SeriesCollection.NewSeries
            .Name = wsX.Cells(1, i).Value
            .XValues = wsX.Range(wsX.Cells(3, i), wsX.Cells(1000, i))
            .Values = wsY.Range(wsY.Cells(3, i), wsY.Cells(1000, i))
        End With
    Next i
End Sub

Upvotes: 1

Related Questions