How to pass a value multiple times in a Chart using VBA

[Juggler_IN]

I want to pass a computed value n number of times in a Chart. For example, in an already plotted chart with 36 values, I want to add another series (say, mean score) to the Chart. I tired to put a macro but it is not giving the output.

Sub Chrt1()
   
    ActiveChart.ChartArea.Select
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(2).Values = _
        "={50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50}"

End Sub
Sub Chrt2()

    Dim u$
    u=Application.WorksheetFunction.Rept("50",36)

    ActiveChart.ChartArea.Select
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(2).Values = _
        "={" & u & "}"""

End Sub

I want to achieve Chrt1 output using Chrt2 logic.

 

[Domenic]

Try building your string as follows...

    Dim u$
    u = Application.WorksheetFunction.Rept(",50", 36)
    u = Mid(u, 2)

Then assign it as follows...

ActiveChart.SeriesCollection(2).Values = "={" & u & "}"

So your code would be amended as follows...

Sub Chrt2()

    Dim u$
    u = Application.WorksheetFunction.Rept(",50", 36)
    u = Mid(u, 2)

    ActiveChart.ChartArea.Select
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(2).Values = "={" & u & "}"

End Sub

Your code could also be re-written as follows...

Sub Chrt3()

    Dim u$
    u = Application.WorksheetFunction.Rept(",50", 36)
    u = Mid(u, 2)

    With ActiveChart.SeriesCollection.NewSeries
        .Values = "={" & u & "}"
    End With

End Sub

Hope this helps!

 

[Juggler_IN]

@Domenic; Thanks, it works!