VB Dynamic Graph Excel Range Error

Posted on 2011-05-06
Medium Priority
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
  • 2
LVL 22

Accepted Solution

Nico Bontenbal earned 2000 total points
ID: 35705424
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
ID: 35705431
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.

Author Closing Comment

ID: 35705457
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

862 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