CODEWITHSUNDEEP
vbaexcel
Preston
Preston

Reputation: 8187

Excel VBA .AddChart Method 'SetSource Data' of Object' _chart' failed

I'm trying to insert a chart into my spreadsheet, as outlined in msdn here:

https://msdn.microsoft.com/en-us/library/bb238877(v=office.12).aspx

Unfortunately i get the error in the

Method 'SetSource Data' of Object' _chart' failed

on the line starting ActiveChart. Why is it doing this? i've tried both string and range variables here to no avail.

In addition to the fact that i can't get this method to work, i don't like the fact you need to select the select the graph, surely there is a better way?

Function TimeSeries(rngToPrint As Range)
    Dim strRange As String
    Dim rngChart As Range

    lngstartrow = 8
    lngendrow = Range("a10000").End(xlUp).Row
    Range("$A$" & CStr(lngstartrow) & ":$B$" & CStr(lngendrow)).Select
    Sheets(rngToPrint.Worksheet.Name).Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range("$A$" & CStr(lngstartrow) & ":$B$" & CStr(lngendrow)), PlotBy:=xlLine

End Function

Upvotes: 0

Views: 347

Answers (2)

Shai Rado
Shai Rado

Reputation: 33692

The error appears at the following line because there is no PlotBy:=xlLine

Modify it to:

ActiveChart.SetSourceData Source:=Range("$A$" & CStr(lngstartrow) & ":$B$" & CStr(lngendrow)), PlotBy:=xlRows

Or either:

ActiveChart.SetSourceData Source:=Range("$A$" & CStr(lngstartrow) & ":$B$" & CStr(lngendrow)), PlotBy:=xlColumns

Upvotes: 0

Domenic
Domenic

Reputation: 8124

The PlotBy argument specifies whether to plot the data by rows or columns. So the argument should be set to either xlRows or xlColumns.

Upvotes: 2

Related Questions