Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Nov 2002
    Location
    Miami, Florida, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SetSourceData in Excel (Excel - 2000)

    I'm trying to set source data in an Excel chart using a variable range.

    Using the Record New function, I got this code
    ActiveChart.SetSourceData Source:=Sheets("Store - ").Range("B65:B95"), _
    PlotBy:=xlColumns

    First I tried putting my variables directly in the statement.
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SetSourceData Source:=Sheets(PasteSheet3).Range(Cells(pasterow + 24, 2), Cells(pasterow + 54, 2)), _
    PlotBy:=xlColumns

    With this statement, I get an error that says
    Method 'Cells' of object '_global' failed

    I also tried setting a range variable and putting the named range in there
    Dim dayr As Range
    Set dayr = Range(Cells(pasterow + 24, 2), Cells(pasterow + 54, 2))
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SetSourceData Source:=Sheets(PasteSheet3).Range(dayr)), _
    PlotBy:=xlColumns

    I tried it using quotation marks too.

    I recorded myself setting the source data using a named range - but even though it let me set source data with the name in Excel itself, the macro displayed the absolute cells
    ActiveWorkbook.Names.Add Name:="dayr", RefersToR1C1:= _
    "='Store - EN'!R73C2:R103C2"
    ActiveChart.SetSourceData Source:=Sheets("Store - ").Range("B65:B95"), _
    PlotBy:=xlColumns

    I modified the code that I saw in Record New to add a name and tried to use the name in the source data code instead of using a variable and that doesn't work either...
    ' ActiveWorkbook.Names.Add Name:="dayr", RefersToR1C1:= _
    "Sheets(PasteSheet3).Range(Cells(pasterow + 24, 2), Cells(pasterow + 54, 2))"
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SetSourceData Source:=Sheets(PasteSheet3).Range("dayr")), _
    PlotBy:=xlColumns

    All the methods I went through to try to use the variable/name for the range give an error that says
    Application-defined or object-defined error

    Can anyone help???

    Thanks
    Chris

  2. #2
    New Lounger
    Join Date
    Nov 2002
    Location
    Miami, Florida, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    solved by a coworker

    Turns out the solution is easy - get rid of all the range parameters and just use the variable name

    Set dayr = Range(Cells(pasterow + 25, 2), Cells(pasterow + 55, 2))

    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.ChartArea.Select
    ActiveChart.SetSourceData Source:=dayr, _
    PlotBy:=xlColumns

    Hope this helps someone else in need [img]/forums/images/smilies/smile.gif[/img]
    chris

  3. #3
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: solved by a coworker

    One more comment... you don't need to do the .Activate or the .Select, those two actions are rarely necessary to manipulate an object.

    Deb

  4. #4
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SetSourceData in Excel (Excel - 2000)

    Hey someone already answered this, but here's what I do. I had a stacked bar chart where I had three data ranges. Heres's the sub I use to assign all the data to the chart (the data is in 3 separate areas in a sheet - the data is generated dynamically based on other things in the workbook and after the user clicks a 'go' button to generate it).

    <pre>Private Sub SetChartData()
    Dim rngVar As Range, rngMin As Range, rngMax As Range
    Dim obCht As ChartObject, rngSource As Range


    Set obCht = ActiveSheet.ChartObjects("chtTornado")
    obCht.Chart.SeriesCollection(1).Delete

    Set rngVar = ActiveSheet.Range("_sensTable").Offset(1, 0)
    Set rngVar = Range(rngVar, rngVar.End(xlDown)) ' variable names

    Set rngMin = ActiveSheet.Range("_sensTable").Offset(1, 4)
    Set rngMin = Range(rngMin, rngMin.End(xlDown)) ' list of Min values

    Set rngMax = ActiveSheet.Range("_sensTable").Offset(1, 6)
    Set rngMax = Range(rngMax, rngMax.End(xlDown)) ' list of Max values

    Set rngSource = Union(rngVar, rngMin, rngMax)
    obCht.Chart.SetSourceData Source:=rngSource, PlotBy:=xlColumns
    obCht.Chart.SeriesCollection(1).Interior.ColorInde x = cLIME 'min
    obCht.Chart.SeriesCollection(2).Interior.ColorInde x = cBLUE 'max
    obCht.Chart.SeriesCollection(1).Name = "Low"
    obCht.Chart.SeriesCollection(2).Name = "High"

    Set obCht = Nothing

    End Sub</pre>

    The chart object in Excel is very complex and does work the same across all Excel versions. I've had tons of problems getting the same code to work in xl97, xl2000 and xl2002 so make sure you test it well if you know your users will be working with other versions.

    Deb <img src=/S/compute.gif border=0 alt=compute width=40 height=20>

  5. #5
    New Lounger
    Join Date
    Nov 2002
    Location
    Miami, Florida, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: solved by a coworker

    Thanks for your help!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •