Excel 2010 Chart SetSourceData 1004 error

whatsup · #1 10-23-2014, 01:51 PM

Hi

Sure enough your posted code "as is" won't even work under excel 2003, that's because:
chart.Chart.SetSourceData Source:=ActiveSheet.Range("A1:FA6")
has to be:
chartt.Chart.SetSourceData Source:=ActiveSheet.Range("A1:FA6")

Otherwise you walk on thin ice: You're using the chart's name given by excel. Excel itself names chart according to the number of charts within a Sheet, for the first chart the name will be "Chart 1". Now regardless if the first chart still exists, the next chart created will be called "Chart 2". - and so on...
So just check the name of the chart to be modified, if it's indeed "Chart 1" if not so you will have to rename the chart, or correct your code.

See if the error renames, in case tell me which line of the code is marked by the debugger. But maybe above already helped!

pocerus · #2 10-24-2014, 12:28 AM

Hi whatsup,

Thanks for taking some time to look at this problem.

I have to apologize for posting the wrong code in my original question. Please find an updated version of the code that you can insert in a new module in a new Excel Workbook and test it.

Code:

Sub setDataChart()
    Call createAColValues
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
    ActiveChart.SetSourceData Source:=Range("A1:FA6"), PlotBy:=xlColumns
    ActiveSheet.ChartObjects(1).Activate
    With ActiveChart.Parent
         .Height = 325
         .Width = 900
         .Top = 120
         .Left = 10
    End With
    Call updateValues
    Call sendData
End Sub

    Sub sendData()
    Dim cht As ChartObject
    Set cht = ActiveSheet.ChartObjects(1)

    'On Error GoTo delSeries:
    For i = 0 To 1000
        cht.Chart.SetSourceData Source:=ActiveSheet.Range("A1:FA6"), PlotBy:=xlColumns
    Next i
End Sub

Sub createAColValues()
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "2"
    Range("A1:A2").Select
    Selection.AutoFill Destination:=Range("A1:A6"), Type:=xlFillDefault
    Range("A1:A6").Select
End Sub

Sub updateValues()
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "=RANDBETWEEN(0,10)"
    Range("B1").Select
    Selection.AutoFill Destination:=Range("B1:B6"), Type:=xlFillDefault
    Range("B1:B6").Select
    Selection.AutoFill Destination:=Range("B1:FA6"), Type:=xlFillDefault
    Range("B1:FA6").Select
End Sub

Run the Sub setDataChart to get the application started so you can find the error message after iteration number 209 once it enters in the For Loop.

Many thanks.

whatsup · #3 10-24-2014, 02:07 PM

Pocerus, what's the loop for?
I don't see the point to set the source of a chart again and again to the same Range! Can you explain what you are after?

p45cal · #4 10-26-2014, 08:10 AM

Quote:

Originally Posted by whatsup

Pocerus, what's the loop for?

Quite.
Is this what you're looking to do? (all subs rolled into 1):

Code:

Sub setDataChart()
Range("A1").Value = 1
Range("A1:A6").DataSeries
With ActiveSheet.Shapes.AddChart
  .Chart.ChartType = xlXYScatterSmoothNoMarkers
  .Height = 325
  .Width = 900
  .Top = 120
  .Left = 10
  Range("B1:FA6").Formula = "=RANDBETWEEN(0,10)"
  For i = 2 To 157
    .Chart.SetSourceData Source:=Union(Range("A1:A6"), Range("A1:FA6").Columns(i))
    Application.ScreenUpdating = True
    MsgBox "OK to continue…"
  Next i
End With
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Run Time Error '1004'	galkej	Excel	0	02-03-2014 06:39 AM
Run-time error '1004': Document not saved. The document may be open...	doctor_who12	Excel Programming	1	01-22-2014 04:47 PM
paste fails error 1004 in VBA Excel 2010	mikec	Excel Programming	17	05-08-2013 03:11 PM
Run time error 1004	yonasan	Excel Programming	3	06-12-2012 11:08 PM
runtime error 1004	gbaker	Excel Programming	11	06-06-2012 05:23 AM