locked
Problems setting chart source data in Excel RRS feed

  • Question

  • I hope this isn't too basic a question for this forum. I'm a complete VBA newbie, and still struggling with the basic syntax.

    I'm trying to programmatically generate a series of charts from a set of data in Excel. I've created a chart and formatted it the way I want, and now just need to iterate through the data by changing the source data range, updating the chart, and exporting it. Everything seems to be working fine, except I can't set the data range properly.

    When I set the range manually, everything works fine. For example, this line works:

      ActiveChart.SetSourceData Source:=ActiveWorkbook.Sheets("Google-PVs").Range("A3:I3")

    But when I try to change the range by using variables, like this:

    ActiveChart.SetSourceData Source:=ActiveWorkbook.Sheets("Google-PVs").Range(Cells(i, 1), Cells(i, LastCol))

    I get a run-time error '1004': Application-defined or object-defined error. I think it must be the syntax I'm using, because even when I hard-code the cell references, like below, I get the same error:

    ActiveChart.SetSourceData Source:=ActiveWorkbook.Sheets("Google-PVs").Range(Cells(2, 1), Cells(2, 9))

    Any ideas what I'm doing wrong here? This is driving me crazy!

    Thanks,

        Andy

     

     

    Wednesday, January 26, 2011 5:19 AM

Answers

  • "Cells" without a parent qualifier refers to the active sheet.  Try...

    With ActiveWorkbook.Sheets("Google-PVs")
    ActiveChart.SetSourceData Source:=.Range(.Cells(2, 1), .Cells(2, 9))
    End With
    '---
    Note the "dots" in front of Cells.
    '---
    Jim Cone
    Portland, Oregon USA
    http://www.mediafire.com/PrimitiveSoftware
    (Bingo Card Variety - in the free folder)

    • Marked as answer by AndyWa Wednesday, January 26, 2011 5:54 PM
    Wednesday, January 26, 2011 5:49 AM

All replies

  • "Cells" without a parent qualifier refers to the active sheet.  Try...

    With ActiveWorkbook.Sheets("Google-PVs")
    ActiveChart.SetSourceData Source:=.Range(.Cells(2, 1), .Cells(2, 9))
    End With
    '---
    Note the "dots" in front of Cells.
    '---
    Jim Cone
    Portland, Oregon USA
    http://www.mediafire.com/PrimitiveSoftware
    (Bingo Card Variety - in the free folder)

    • Marked as answer by AndyWa Wednesday, January 26, 2011 5:54 PM
    Wednesday, January 26, 2011 5:49 AM
  • One way in which I do that is to use an Index() function to modify the data in the range that is being plotted and the iterate through the row and column indices of the index to return the data to be plotted.  I am not sure how the following formulae copied from Excel will turn out, but in that Excel application by adjusting the values in column B for DataRow and DataColumn, a total of over 12,000 charts (573 records by 21 charts for each record) were created and copied and pasted into a Word "mail merge" application.  It wasn't actually mail merge as mail merge cannot be used to create documents like those created.
                     =INDEX(ChartData,1,DataColumn1)=INDEX(ChartData,1,DataColumn2) =INDEX(ChartData,1,DataColumn3)
    ChartData    =INDEX(ChartData,DataRow+1,DataColumn1 =INDEX(ChartData,DataRow+1,DataColumn2) =INDEX(ChartData,DataRow+1,DataColumn3)
    DataRow                    1DataColumn    1 =B4+1=C4+1
     -- Hope this helps.

    Doug Robbins - Word MVP,
    dkr[atsymbol]mvps[dot]org
    Posted via the Community Bridge

    "AndyWa" wrote in message news:d38f306e-c29d-4881-be22-a07f51a229a0@communitybridge.codeplex.com...

    I hope this isn't too basic a question for this forum. I'm a complete VBA newbie, and still struggling with the basic syntax.

    I'm trying to programmatically generate a series of charts from a set of data in Excel. I've created a chart and formatted it the way I want, and now just need to iterate through the data by changing the source data range, updating the chart, and exporting it. Everything seems to be working fine, except I can't set the data range properly.

    When I set the range manually, everything works fine. For example, this line works:

      ActiveChart.SetSourceData Source:=ActiveWorkbook.Sheets("Google-PVs").Range("A3:I3")

    But when I try to change the range by using variables, like this:



    ActiveChart.SetSourceData Source:=ActiveWorkbook.Sheets("Google-PVs").Range(Cells(i, 1), Cells(i, LastCol))

    I get a run-time error '1004': Application-defined or object-defined error. I think it must be the syntax I'm using, because even when I hard-code the cell references, like below, I get the same error:



    ActiveChart.SetSourceData Source:=ActiveWorkbook.Sheets("Google-PVs").Range(Cells(2, 1), Cells(2, 9))

    Any ideas what I'm doing wrong here? This is driving me crazy!

    Thanks,

        Andy




    Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org
    Wednesday, January 26, 2011 5:54 AM
  • Thanks! A modified version of that worked perfectly! The cells I was referencing were on a different worksheet than the chart, but as soon as I explicitly identified the sheet before referencing the cells, it worked. The strange thing was that using the standard cell reference format ("A3:I3") seemed to grab the data from the correct worksheet without my having to specify it again.

    Regardless, thanks for your help!

      - Andy

    Wednesday, January 26, 2011 5:54 PM