Error with SetSourceData constant receive error 438

[Invisibleman]

Dear all,

In my excel, I have an excisting chart, where the source should be dynamic. So I am trying to use the SetSourceData, but no matter what and how I try, it always generate the error 438.

The chart I have is on the sheet; New_Chart_Overview
The name of the chart is; BAR12;InvestedCurrent
or the number of the chart on the sheet is number; 6

The source of the data is on sheet; New_Settings_Formula_Charts
The source cells (are split) and the row 24 should be dynamic; "$D$20:$D$24" & ",$I$20:$N$24", for the 24 I have a byte Line and get then the Range("$D$20:$D$" & Line & ",$I$20:$N$" & Line).

But no matter what I do, I keep getting the error 438, even when I try to have the single range on the sheet where the chart is located, still the error is existing.


But I do need to have these correct splitted ranges added as the source with the dynamic row, like this;

   'ActiveWorkbook.Names.Add Name:="VBA_Chart_Overview_Main_Chart", RefersTo:=New_Settings_Formula_Charts.Range("$D$20:$D$" & Line & ",$I$20:$N$" & Line)
   ActiveWorkbook.Names.Add Name:="VBA_Chart_Storage_Location_Chart", RefersTo:=New_Settings_Formula_Charts.Range("$C$16:$C$" & Line & ",$G$16:$H$" & Line)

And here some examples I did try;

 ChartObjects(1).SetSourceData Source:=Range("$D$60:$D$64")
 ChartObjects(1).SetSourceData Source:=Range(Cells(10, 40), Cells(15, 40))

Public Sub SetMainChartData()
Dim Line As Byte, rng As Range, rng1 As Range, rng2 As Range
Dim test As String
Line = 17 + New_RAW_Currencies.Range("Y2")

Set rng1 = New_Settings_Formula_Charts.Range("$D$16:$D$" & Line)
Set rng2 = New_Settings_Formula_Charts.Range("$I$16:$N$" & Line)
Set rng = Application.Union(New_Settings_Formula_Charts.Range("$D$16:$D$" & Line), New_Settings_Formula_Charts.Range("$I$16:$N$" & Line))

 New_Chart_Overview.ChartObjects("BAR12;InvestedCurrent").SetSourceData Source:=New_Settings_Formula_Charts.Range("$D$20:$D$" & Line)
 New_Chart_Overview.ChartObjects(1).SetSourceData Source:=New_Settings_Formula_Charts.Range("$D$20:$D$24")
 New_Chart_Overview.ChartObjects(1).SetSourceData Source:=New_Settings_Formula_Charts.Range(rng1, rng2)
 
End Sub

Can anybody please help me?

Thanks in advance,
Hans

 

[bobsan42]

ChartObject object does not have a SetSourceData method.
SetSourceData is a method of Chart object.

 

[Invisibleman]

ChartObject object does not have a SetSourceData method.
SetSourceData is a method of Chart object.

Hello Bobsan42,

Thanks, for this info, was really helpfull. I also could now have the 2 different ranges, working. As what I could see, it was needed to do the union, maybe if you have a other/better solution, then please let me know.

For now I have;

Private Sub CommandButton1_Click()

Dim line As Byte
Dim rng As Range, rng1 As Range, rng2 As Range

line = 17 + New_RAW_Currencies.Range("Y2")

Set rng1 = New_Settings_Formula_Charts.Range("$D$16:$D$" & line)
Set rng2 = New_Settings_Formula_Charts.Range("$I$16:$N$" & line)
Set rng = Application.Union(New_Settings_Formula_Charts.Range("$D$16:$D$" & line), New_Settings_Formula_Charts.Range("$I$16:$N$" & line))

ChartObjects("BAR12;InvestedCurrent").Chart.SetSourceData Source:=rng

End Sub

Thanks again,
Hans

 

[smozgur]

But no matter what I do, I keep getting the error 438, even when I try to have the single range on the sheet where the chart is located, still the error is existing.

The original problem is the wrong usage of the SetSourceData method and it is explained why and how to fix it at #2 by @bobsan42. Therefore, the marked solution has been changed accordingly.

No further action is required for this thread.