Creating Excel chart using Vba showing wrong results

[praveenpatel421983]

Hi,

I am trying to create a excel line chart using vba using the information in the same sheet. For some reason result has 2 lines in the graph which is supposed to be 1 line. I am not sure what is going wrong in that. Could someone guide me what is going wrong? I have tried searching this forum but couldn't find the right post.

Set chrt2 = Sheets("Graphs").Shapes.AddChart.Chart
With chrt2
GraphName = Sheets("Graphs").Cells(34, 1).Value
.ChartArea.Left = 600
.ChartArea.Top = 690
.ChartArea.Height = 300
.ChartArea.Width = 1200
.ChartType = xlLine
.SeriesCollection.NewSeries
.SeriesCollection(1).Name = "=""Scatter Chart2"""
.SetSourceData Source:=Sheets("Graphs").Range("$A$35:$D" & LastColGraph), PlotBy:=xlColumns
.SeriesCollection(1).XValues = "=Graphs!$A$35:$A" & LastColGraph
.SeriesCollection(1).Values = "=Graphs!$D$35:$D" & LastColGraph
.HasTitle = True
.ChartTitle.Characters.Text = GraphName
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = GraphName
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = Sheets("Graphs").Cells(34, 4).Text
.Axes(xlCategory).HasMajorGridlines = False
.Axes(xlCategory).HasMinorGridlines = False
.Axes(xlValue).HasMajorGridlines = True
.Axes(xlValue).HasMinorGridlines = False
.HasLegend = False
.ChartArea.Interior.Color = RGB(245, 245, 245)
.PlotArea.Interior.Color = RGB(219, 229, 241)
.SeriesCollection(1).Border.Color = RGB(46, 108, 150)
End With

Thanks

 

[pgc01]

Hi

Didn't try but looking at the code I'd say you have not 2 but 3 lines, 2 of them superposed.

Try deleting:

.SetSourceData Source:=Sheets("Graphs").Range("$A$35:$D" & LastColGraph), PlotBy:=xlColumns

 

[praveenpatel421983]

Hi,

Oh, I didn't knew that. You are right!
I was using below code before, it used to have 2 lines.
Please let me know what do you recommend?

.SetSourceData Source:=Sheets("Graphs").Range("$A$35:$A" & LastColGraph, "$D$35:$D" & LastColGraph), PlotBy:=xlColumns

 

[pgc01]

You just have to delete the .SetSourceData line.

The rest of the code that you have already adds a series and defines the source data.

.SeriesCollection.NewSeries
.SeriesCollection(1).Name = "=""Scatter Chart2"""
.SeriesCollection(1).XValues = "=Graphs!$A$35:$A" & LastColGraph
.SeriesCollection(1).Values = "=Graphs!$D$35:$D" & LastColGraph

 

[praveenpatel421983]

Sorry for replying late.

I did the way you informed and it worked for me. You are awesome . Thanks a lot pgc01!

 

[pgc01]

You're welcome.