VB Dynamic Graph Excel Range Error

Posted on 2011-05-06
Last Modified: 2012-05-11
Hi experts,

I have a small vb script that I want to run to change the source data on a graph.  No matter what I try I can not get the graph to accept the defined range.  Here is the code:

intLastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
        intLastCol = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column
        ActiveChart.SetSourceData Source:=Sheets("Chart Data").Range(Cells(5, 1), Cells(intLastRow, intLastCol))

Open in new window

The error is always shown on the last line.

Any help would really be appreciated.

Question by:GlenmoranUK
    LVL 22

    Accepted Solution

    Try this:
    ActiveChart.SetSourceData Source:=Sheets("Chart Data").Range(Sheets("Chart Data").Cells(5, 1), Sheets("Chart Data").Cells(intLastRow, intLastCol))
    LVL 22

    Expert Comment

    by:Nico Bontenbal
    The Cells(5, 1) in your code refers to the cells of the active sheet. Being the Graph sheet, which doesn't have cells, hence the error.
    LVL 1

    Author Closing Comment

    Fantastic..  That has had me going round in circles for a good couple of hours..  :o)

    Many thanks for the code and more importantly the explanation.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
    If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now