Series.Formula changes characters e.g. commas to semicolons

Viewed 49

I wrote code to go through the data (as shown below) and collect data based on category. Such data will be added to dedicated data series and plotted at the ScatterPlot.
enter image description here

Series.Formula changes some characters e.g. commas, semicolons (pictures below) and an error pops up.

How it looks before:
enter image description here

How it looks after:
enter image description here

Dim mySeries As Series    
For Each mySeries In ActiveSheet.ChartObjects("Test").Chart.SeriesCollection
    For Each c In Source.Range("U7:U13")
               
        If c = mySeries.Name Then
                    
            k = 0
            Do While Cells(c.row, c.Column) = Cells(c.row + k, c.Column)
                k = k + 1
            Loop
            k = k - 1
                
            srsX = "{"
            srsY = "{"
            For j = c.row To c.row + k
                srsX = srsX & (Cells(j, c.Column + 2)) & "\" '& Application.International(xlListSeparator)
                srsY = srsY & (Cells(j, c.Column + 1)) & "\" '& Application.International(xlListSeparator)
                        
            Next j
            srsX = Left(srsX, Len(srsX) - 1) & "}"
            srsY = Left(srsY, Len(srsY) - 1) & "}"
                    
            With ActiveSheet.ChartObjects("Test").Chart.SeriesCollection
                trashme = "=SERIES(""" & mySeries.Name & """" & Application.International(xlListSeparator) & srsX & Application.International(xlListSeparator) & srsY & Application.International(xlListSeparator) & mySeries.PlotColorIndex & ")"
                MsgBox trashme
                mySeries.Formula = "=SERIES(""" & mySeries.Name & """" & Application.International(xlListSeparator) & srsX & Application.International(xlListSeparator) & srsY & Application.International(xlListSeparator) & mySeries.PlotColorIndex & ")"
            End With
        End If    
                
    Next c
Next mySeries
0 Answers
Related