Plotting with VBA

[Gitad]

Hi, I am a beginner and trying to do a simple plot with VBA. The issue is x values are y values are plotted separately; however it works fine (the second plot) when I do manually. The Demo Plot is the one with VBA code as below and incorrect.
Any help would be greatly appreciated.

'Embed the plot in the same worksheet
Sub Charts_Example2()

Dim Ws As Worksheet
Dim rng As Range
Dim MyChart As Object

Set Ws = Worksheets("Sheet1") 'Sheet reference
Set rng = Ws.Range("A1:B5") 'Data reference
Set MyChart = Ws.Shapes.AddChart2 'Sets the chart object

With MyChart.Chart
.SetSourceData rng
.ChartType = xlXYScatterLines
.ChartTitle.Text = "Demo Plot"
End With

End Sub




View attachment 112626

 

[Gitad]

Hi, I am a beginner and trying to do a simple plot with VBA. The issue is x values are y values are plotted separately; however it works fine (the second plot) when I do manually. The Demo Plot is the one with VBA code as below and incorrect.
Any help would be greatly appreciated.

'Embed the plot in the same worksheet
Sub Charts_Example2()

Dim Ws As Worksheet
Dim rng As Range
Dim MyChart As Object

Set Ws = Worksheets("Sheet1") 'Sheet reference
Set rng = Ws.Range("A1:B5") 'Data reference
Set MyChart = Ws.Shapes.AddChart2 'Sets the chart object

With MyChart.Chart
.SetSourceData rng
.ChartType = xlXYScatterLines
.ChartTitle.Text = "Demo Plot"
End With

End Sub




View attachment 112626

 

[Artik]

Try it

Sub Charts_Example2_1()
    Dim Ws As Worksheet
    Dim rng As Range
    Dim rngXVal As Range
    Dim MyChart As Object

    Set Ws = Worksheets("Sheet1")
    Set rng = Ws.Range("B1:B5")
    Set rngXVal = Ws.Range("A2:A5")

    Set MyChart = Ws.Shapes.AddChart2(227, xlLine).Chart

    With MyChart
        .SetSourceData Source:=rng
        .FullSeriesCollection(1).XValues = rngXVal
    End With
End Sub

Artik

 

[Gitad]

Try it

Sub Charts_Example2_1()
    Dim Ws As Worksheet
    Dim rng As Range
    Dim rngXVal As Range
    Dim MyChart As Object

    Set Ws = Worksheets("Sheet1")
    Set rng = Ws.Range("B1:B5")
    Set rngXVal = Ws.Range("A2:A5")

    Set MyChart = Ws.Shapes.AddChart2(227, xlLine).Chart

    With MyChart
        .SetSourceData Source:=rng
        .FullSeriesCollection(1).XValues = rngXVal
    End With
End Sub

Artik

Thanks a lot, Artik! That works fine.