CODEWITHSUNDEEP
excelvba
skbeez
skbeez

Reputation: 109

Excel VBA: Why doesn't any subsequent series get added on?

I am trying to plot around 200 series's onto one chart and am trying to use a for loop to plot all the series's for me however, when I run the following code, only the first series (not in the for loop) actually gets onto the chart. All the others that are generated by the for loop do not show up and I have no idea why...

Sub Macro3()

    Dim r As Integer
    Dim cellName As String
    Dim first As String
    Dim second As String
    Dim newCell As String
    Dim xAxis As String
    Dim i As Integer

    xAxis = "=Compilation!A7:A7507"
    originCell = "=Compilation!B7:B7507"

    ActiveSheet.ChartObjects(1).Activate
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).XValues = Range(xAxis)
    ActiveChart.SeriesCollection(1).Values = Range(originCell)

    For i = 2 To 200
        ' Incriment the columns
        first = InStr(originCell, "!")
        second = InStr(first + 1, originCell, ":")

        cellName = Mid(originCell, first + 1, second - first - 1)
        r = Range(cellName).Column + 1

        newCell = Replace(originCell, cellName, cellS(7, r).Address(RowAbsolute:=False, ColumnAbsolute:=False))

        If (cellS(7, r - 1).Value = "YES") Then
            ActiveChart.SeriesCollection.NewSeries
            ActiveChart.SeriesCollection(i).XValues = Range(xAxis)
            ActiveChart.SeriesCollection(i).Values = Range(newCell)
        End If

        originCell = newCell
        MsgBox (i & ", " & xAxis & ", " & newCell)
    Next i


End Sub

Any insight would be greatly appreciated! Thanks in advance!

Upvotes: 0

Views: 676

Answers (1)

PatricK
PatricK

Reputation: 6433

From your code I believe you want to plot data series that are vertical instead of horizontal and have a very misleading variable name - used r for Columns.

First, remove the equal sign for the Range() to work

xAxis = "Compilation!A7:A7507"
originCell = "Compilation!B7:B7507"

Then you are keep adding series to the chart, regardless of how many is in there.

Next issue is that ActiveChart.SeriesCollection(i) should not relate to i, as you only want to add if "YES".

Code below should work for you, assuming that cells in Row 7 may equal to "YES". If it is "YES" then the data below it will be added to the chart (should not include itself as you have done). Will also handle if there isn't a Chart in Activesheet. It will remove all old series in the Chart before adding the "YES" ones.

Comment out my TEST DATA row and uncomment ACTUAL DATA:

Sub AddDataToChart1()
    Const YesNoRow As Long = 7 ' Yes/No should not be plotted in the chart
    Const xAxis As String = "Compilation!A8:A13" ' TEST DATA
    'Const xAxis As String = "Compilation!A8:A7507" ' ACTUAL DATA

    Dim oRngAxis As Range, oCht As Chart
    Dim i As Long ' Offset counter
    Dim n As Long ' Number of data series in chart

    On Error Resume Next
    ' Check if existing chart available
    Set oCht = ActiveSheet.ChartObjects(1).Chart
    If oCht Is Nothing Then Set oCht = ActiveSheet.Shapes.AddChart.Chart
    On Error GoTo 0
    ' Chart Object valid, add series
    If Not oCht Is Nothing Then
        Set oRngAxis = Range(xAxis)
        With oCht
            ' Remove previous data
            For i = .SeriesCollection.Count To 1 Step -1
                .SeriesCollection(i).Delete
            Next
            n = 0
            For i = 1 To 200
                If UCase(oRngAxis.Worksheet.Cells(YesNoRow, oRngAxis.Column + i).Value) = "YES" Then
                    n = n + 1
                    If n > .SeriesCollection.Count Then
                        .SeriesCollection.NewSeries
                    End If
                    .SeriesCollection(n).XValues = oRngAxis
                    .SeriesCollection(n).Values = oRngAxis.Offset(0, i)
                    .SeriesCollection(n).Name = "Col " & Split(oRngAxis.Offset(0, i).Address, "$")(1)
                End If
            Next i
        End With
        Set oCht = Nothing
        Set oRngAxis = Nothing
    End If
End Sub

Sample Data and Output:

TestData

SamleChart

UPDATE:

Add below code to Compilation sheet to make whenever a cell change in row 7 it will update the Chart immediately! You can also move the sub AddDataToChart1 to there too:

Private Sub Worksheet_Change(ByVal oRng As Range)
    If Not Intersect(oRng, Rows(7)) Is Nothing Then AddDataToChart1
End Sub

Upvotes: 1

Related Questions