Results 1 to 3 of 3

Thread: VBA code to automatically add multiple series to a scatter plot IF(ISNUMBER=true)

  1. #1

    VBA code to automatically add multiple series to a scatter plot IF(ISNUMBER=true)



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I need to dynamically add series' to a smooth line scatter plot chart if there is any number in a cell in column A. If $A$1 has a number (ISNUMBER function should work fine here I believe), then plot series such as: SERIES(sheet1!$A$1,sheet1!B$1:$B$37,sheet1!$C$1:$C$37,1).

    I need the code to repeat over the same size of ranges that will continue all the way down to row 5476. This means there could potentially be 5476/37 = 148 Series in my plot.

    Each Series will be the same size of 37 rows with Column A having the same value over those 37 rows for a series. Column B will be the X-axis and Column C will be the Y-axis. When Column A no longer contains a number the code should stop and not display blank series in the legend of the plot. The length of this table changes depending on the number of different inflows I have in Column A, and therefore the number of different series I have in my plot, which may vary from 1 to 148 inflows/series.

    The attached table is an exerpt of the data being plotted. In this excerpt there would be two series plotted named 60 and 80.

    Thanks for any help,
    Myles Mc
    Book2.xlsx

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,479
    Articles
    0
    Excel Version
    365
    First, for others, this has been cross-posted at ExcelForum.com.
    MylesMc, have you cross posted elsewhere too? If so, could you say where please?

    Second, the problem:
    Try the following in your sample workbook:
    Code:
    Sub blah()
    Range("A1").Select  'in case the chart is selected.
    Range("A1").Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    With ActiveSheet.ChartObjects("Chart 1").Chart
    'delete all existing series first:
      For i = 1 To .SeriesCollection.Count
        .SeriesCollection(1).Delete
      Next i
      For Each ar In Intersect(ActiveSheet.UsedRange, Columns("B:B")).Offset(1).SpecialCells(xlCellTypeConstants, 23).Areas
        '  ar.Offset(, -1).Resize(, 3).Select
        '  ar.Cells(1).Offset(, -1).Select
        '  ar.Columns(1).Select
        '  ar.Columns(2).Select
        With .SeriesCollection.NewSeries
          .XValues = ar.Columns(1)
          .Values = ar.Columns(2)
          .Name = ar.Cells(1).Offset(, -1)
          With .Border
            .Weight = xlThin
            .LineStyle = xlAutomatic
            .ColorIndex = 1
          End With
          .MarkerStyle = xlNone
          .Smooth = True
        End With
      Next ar
    End With
    Range("A1").RemoveSubtotal
    End Sub
    This was developed in Excel 2003.
    Last edited by p45cal; 2013-08-15 at 09:28 AM.

  3. #3
    Hey p45cal,

    Thanks for your reply. The code works great. Thank you very much!

    This question was cross posted to ExcelForum.com and MrExcel.com.

    Thanks Again,
    Myles Mc

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •