Add Variable Series to Chart w/ VBA

Question

I have a spreadsheet that looks like this:

A     1/1/2013     100
A     2/1/2013     200
A     3/1/2013     300
B     1/1/2013     150
B     2/1/2013     175
B     3/1/2013     200

The three columns are fixed, but the number of entries for each series (first column) varies.

Using VBA, I'd like to automatically add each unique value in the first column as a series on a scatterplot, using the second column for the X values and third column for the Y values. For example, for Series A above, I would need to dynamically ascertain ranges B1:B3 and C1:C3.

I generated the code for adding the series to a chart using recorded macros, so my real obstacle is finding the ranges for each series. I need to retain the name of the series for association (first column), and would like to avoid using filters.

My tutorial VBA to Split Data Range into Multiple Chart Series shows how to do this using VBA, while Split Data Range into Multiple Chart Series without VBA shows how to do it without VBA. — Jon Peltier

Cameron Sumpter Cameron Sumpter · Accepted Answer · 2013-03-07T09:41:57

I eventually thought of a somewhat roundabout way of solving it; here's the code for anyone interested, adapted to the layout of my original question:

Sub Example()
    ' Variable Definitions
    Dim y As Integer
    Dim Name(73) As String
    Dim Mins(73) As Integer
    Dim Maxs(73) As Integer

    ' Loop Through Name Column (Series)
    For Each x In Range("A4:A" & Cells(Rows.Count, "A").End(xlUp).Row)

        ' Add Values to Arrays, If Not Preexisting
        If IsInArray(x.Value(), Name) = False Then
            Name(y) = x.Value()
            Mins(y) = x.Row()
            Maxs(y) = x.Row()
            y = y + 1
        Else
            Maxs(y - 1) = x.Row()
        End If

    Next x

    ' Add to Chart
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.FullSeriesCollection(1).Name = Name(0)
    ActiveChart.FullSeriesCollection(1).XValues = "=Data!$B$" & Mins(0) & ":$B$" & Maxs(0)
    ActiveChart.FullSeriesCollection(1).Values = "=Data!$C$" & Mins(0) & ":$C$" & Maxs(0)
End Sub

' Array Function from @JimmyPena
' http://stackoverflow.com/a/11112305/2141501
Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function

Add Variable Series to Chart w/ VBA

1 Answers