CODEWITHSUNDEEP
excelvbachartsdynamically-generated
CHLee
CHLee

Reputation: 1

Excel VBA, having multiple column inputs as XYscatter chart Series name in dynamically generated chart

I'm trying to automate the generation of a dynamic XYscatter chart using VBA. My data are in sets of 12 individual data per trial and the number of trial will vary. Firstly by scanning the csv file to locate the last set of data, and by removing the first line of the tag, i divided it by 12 to determine the number of sets available and then populate the data into to graph with the corresponding data points and series name. I've got most of the code working but I'm having some syntax issue on including a range data for series name. The series name will only work if I am selecting a column instead of a range of column data.

How can I modify my code to have multiple column input as my Series Name? 

Sub PlotSelect()
    Dim myChart As Chart

    DataRow = 1
    SelectRow = 2

    With ActiveSheet
    'To count the number of rows to determine number of sets of data
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    Row = LastRow - 1
    N = Row / 12

    'Creating a dummy chart before repopulating the data points
    Range("A1:B2").Select
    Set myChart = ActiveSheet.Shapes.AddChart2(240, xlXYScatterSmooth).Chart
    '    delete all the dummy series 
    For i = myChart.SeriesCollection.Count To 1 Step -1
    myChart.SeriesCollection(i).Delete
    Next

    'Populating chart with data    
    Do While DataRow <= N
        If DataRow <> -1 Then
            myChart.SeriesCollection.NewSeries
            myChart.SeriesCollection(DataRow).Name = Range(ActiveSheet.Cells(SelectRow, 2), ActiveSheet.Cells(SelectRow, 5))
            myChart.SeriesCollection(DataRow).Values = Range(ActiveSheet.Cells(SelectRow, 9), ActiveSheet.Cells(SelectRow + 11, 9))
            myChart.SeriesCollection(DataRow).XValues = Range(ActiveSheet.Cells(SelectRow, 8), ActiveSheet.Cells(SelectRow + 11, 8))
        End If
        DataRow = DataRow + 1
        SelectRow = SelectRow + 12
    Loop
End Sub

Upvotes: 0

Views: 382

Answers (1)

CHLee
CHLee

Reputation: 1

Instead of using a range of cells to generate the Name for the data entry, I merge the data in the cells and copy the content into another cell and select the new cell with the concatenated cells. Later i delete the temporary cell. 

Do While DataRow <= 2
    If DataRow <> -1 Then
        myChart.SeriesCollection.NewSeries  'To add new data entries
        **Range("B9999").Select
        ActiveCell.FormulaR1C1 = ((Cells(SelectRow, 4)) & "_" & (Cells(SelectRow, 5)) & "_" & (Cells(SelectRow, 6)))
        myChart.FullSeriesCollection(DataRow).Name = Range("B9999")**
        myChart.SeriesCollection(DataRow).Values = Range(Cells(SelectRow, 2), ActiveSheet.Cells(SelectRow + 600, 2))
        myChart.SeriesCollection(DataRow).XValues = Range(Cells(SelectRow, 1), ActiveSheet.Cells(SelectRow + 600, 1))

    End If
    DataRow = DataRow + 1
    SelectRow = SelectRow + 601
Loop   

Range("B9999").Delete               'to delete temp data
ActiveWindow.ScrollRow = 2          'to reset spreadsheet view

The modifications are located in the ** ** range

Upvotes: 0

Related Questions