Changing Chart SourceData in VBA

Excel 97.

Can anyone spot where I'm going wrong? I'm trying to 
change the source data in code so it only shows upto the 
last cell that has a value in it. My code at the mo is..

Dim intActiveCell As Integer
Dim strCell As String
Dim strrange As String

Sheets("Financial Tracker").Select
Range("C13").Select

If ActiveCell <> "" Then
Do Until ActiveCell.Value = ""
ActiveCell.Offset(0, 1).Select
Loop
End If
ActiveCell.Offset(0, -1).Select
'Selection.End(xlLeft).Select
strCell = "R" & ActiveCell.Row & "C" & ActiveCell.Column


Sheets("Graphs").Select
ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.ChartArea.Select
    ActiveChart.SeriesCollection(3).Values = "='Financial 
Tracker'!R13C2: & strCell"


It works fine until the last line where it falls over 
with "Object variable......"message. Can this be done in 
code?

0
anonymous (74722)
5/7/2004 2:00:00 PM
excel.charting 18370 articles. 0 followers. Follow

4 Replies
738 Views

Similar Articles

[PageSpeed] 43

Hi Jim,

Try revising the last line., looks like the variable strCell is inside 
the quotes.

ActiveChart.SeriesCollection(3).Values _
  = "='Financial Tracker'!R13C2:" & strCell

Cheers
Andy

JimPNicholls wrote:

> Excel 97.
> 
> Can anyone spot where I'm going wrong? I'm trying to 
> change the source data in code so it only shows upto the 
> last cell that has a value in it. My code at the mo is..
> 
> Dim intActiveCell As Integer
> Dim strCell As String
> Dim strrange As String
> 
> Sheets("Financial Tracker").Select
> Range("C13").Select
> 
> If ActiveCell <> "" Then
> Do Until ActiveCell.Value = ""
> ActiveCell.Offset(0, 1).Select
> Loop
> End If
> ActiveCell.Offset(0, -1).Select
> 'Selection.End(xlLeft).Select
> strCell = "R" & ActiveCell.Row & "C" & ActiveCell.Column
> 
> 
> Sheets("Graphs").Select
> ActiveSheet.ChartObjects("Chart 1").Activate
>     ActiveChart.ChartArea.Select
>     ActiveChart.SeriesCollection(3).Values = "='Financial 
> Tracker'!R13C2: & strCell"
> 
> 
> It works fine until the last line where it falls over 
> with "Object variable......"message. Can this be done in 
> code?
> 

-- 

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
0
andy9699 (3616)
5/7/2004 2:30:39 PM
Andy

Thanks for the quick reply. I've tried that but I'm still 
getting the following error message "Run-Time error'1004' 
MEthod 'SeriesCollection' of object ' _Chart' failed. Any 
ideas?






>-----Original Message-----
>Hi Jim,
>
>Try revising the last line., looks like the variable 
strCell is inside 
>the quotes.
>
>ActiveChart.SeriesCollection(3).Values _
>  = "='Financial Tracker'!R13C2:" & strCell
>
>Cheers
>Andy
>
>JimPNicholls wrote:
>
>> Excel 97.
>> 
>> Can anyone spot where I'm going wrong? I'm trying to 
>> change the source data in code so it only shows upto 
the 
>> last cell that has a value in it. My code at the mo is..
>> 
>> Dim intActiveCell As Integer
>> Dim strCell As String
>> Dim strrange As String
>> 
>> Sheets("Financial Tracker").Select
>> Range("C13").Select
>> 
>> If ActiveCell <> "" Then
>> Do Until ActiveCell.Value = ""
>> ActiveCell.Offset(0, 1).Select
>> Loop
>> End If
>> ActiveCell.Offset(0, -1).Select
>> 'Selection.End(xlLeft).Select
>> strCell = "R" & ActiveCell.Row & "C" & ActiveCell.Column
>> 
>> 
>> Sheets("Graphs").Select
>> ActiveSheet.ChartObjects("Chart 1").Activate
>>     ActiveChart.ChartArea.Select
>>     ActiveChart.SeriesCollection(3).Values 
= "='Financial 
>> Tracker'!R13C2: & strCell"
>> 
>> 
>> It works fine until the last line where it falls over 
>> with "Object variable......"message. Can this be done 
in 
>> code?
>> 
>
>-- 
>
>Andy Pope, Microsoft MVP - Excel
>http://www.andypope.info
>.
>
0
anonymous (74722)
5/7/2004 2:32:49 PM
Has the chart actually got 3 data series?

The error appears for me if I try it on a chart with only 2 data series.

Cheers
Andy


JimPNicholls wrote:

> Andy
> 
> Thanks for the quick reply. I've tried that but I'm still 
> getting the following error message "Run-Time error'1004' 
> MEthod 'SeriesCollection' of object ' _Chart' failed. Any 
> ideas?
> 
> 
> 
> 
> 
> 
> 
>>-----Original Message-----
>>Hi Jim,
>>
>>Try revising the last line., looks like the variable 
> 
> strCell is inside 
> 
>>the quotes.
>>
>>ActiveChart.SeriesCollection(3).Values _
>> = "='Financial Tracker'!R13C2:" & strCell
>>
>>Cheers
>>Andy
>>
>>JimPNicholls wrote:
>>
>>
>>>Excel 97.
>>>
>>>Can anyone spot where I'm going wrong? I'm trying to 
>>>change the source data in code so it only shows upto 
> 
> the 
> 
>>>last cell that has a value in it. My code at the mo is..
>>>
>>>Dim intActiveCell As Integer
>>>Dim strCell As String
>>>Dim strrange As String
>>>
>>>Sheets("Financial Tracker").Select
>>>Range("C13").Select
>>>
>>>If ActiveCell <> "" Then
>>>Do Until ActiveCell.Value = ""
>>>ActiveCell.Offset(0, 1).Select
>>>Loop
>>>End If
>>>ActiveCell.Offset(0, -1).Select
>>>'Selection.End(xlLeft).Select
>>>strCell = "R" & ActiveCell.Row & "C" & ActiveCell.Column
>>>
>>>
>>>Sheets("Graphs").Select
>>>ActiveSheet.ChartObjects("Chart 1").Activate
>>>    ActiveChart.ChartArea.Select
>>>    ActiveChart.SeriesCollection(3).Values 
> 
> = "='Financial 
> 
>>>Tracker'!R13C2: & strCell"
>>>
>>>
>>>It works fine until the last line where it falls over 
>>>with "Object variable......"message. Can this be done 
> 
> in 
> 
>>>code?
>>>
>>
>>-- 
>>
>>Andy Pope, Microsoft MVP - Excel
>>http://www.andypope.info
>>.
>>

-- 

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
0
andy9699 (3616)
5/7/2004 3:29:44 PM
Jim -

You can also do this without VBA, using dynamic ranges. I have a few 
examples and a lot of links:

   http://peltiertech.com/Excel/Charts/Dynamics.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

JimPNicholls wrote:

> Excel 97.
> 
> Can anyone spot where I'm going wrong? I'm trying to 
> change the source data in code so it only shows upto the 
> last cell that has a value in it. My code at the mo is..
> 
> Dim intActiveCell As Integer
> Dim strCell As String
> Dim strrange As String
> 
> Sheets("Financial Tracker").Select
> Range("C13").Select
> 
> If ActiveCell <> "" Then
> Do Until ActiveCell.Value = ""
> ActiveCell.Offset(0, 1).Select
> Loop
> End If
> ActiveCell.Offset(0, -1).Select
> 'Selection.End(xlLeft).Select
> strCell = "R" & ActiveCell.Row & "C" & ActiveCell.Column
> 
> 
> Sheets("Graphs").Select
> ActiveSheet.ChartObjects("Chart 1").Activate
>     ActiveChart.ChartArea.Select
>     ActiveChart.SeriesCollection(3).Values = "='Financial 
> Tracker'!R13C2: & strCell"
> 
> 
> It works fine until the last line where it falls over 
> with "Object variable......"message. Can this be done in 
> code?
> 

0
DOjonNOT (619)
5/8/2004 1:32:53 AM
Reply:

Similar Artilces:

Need help to read Pie Chart Series Range
Excel 2003, I have an existing Pie Chart and want to extract the Ranges (cells) used. My code below returns with a "Type mismatch" error. Sub GetPieChartSeries() Dim mySeries As Series ActiveSheet.ChartObjects(1).Activate Set mySeries = ActiveChart.SeriesCollection(1) Debug.Print mySeries.XValues (errors here) Debug.Print mySeries.Values End Sub Thanks, - Pat ...

Slow VBA macro
Every time the following code runs and it reaches a blank cell it takes 2-3 seconds to update the cell with a zero: Do Until ActiveCell.Offset(1, -3) = "" ActiveCell.Offset(1, 0).Range("A1").Select Select Case ActiveCell Case Is = "" Selection.FormulaR1C1 = 0 End Select Loop I've tried Application.ScreenUpdating = False but the macro is still very slow. Any suggestions as to ho...

How to stop RichEdit color change overriding default color
I've got a handly little syntax editor going along (that--so far-- is waaay faster than the examples on codeguru.com (if you'd like to know how to speed them up, I spotted a logic flaw in two of them. If you're interested, just email me). I have a question on a different syntax editor I'm writing for an in-house language though, so you'll notice some unique features that enable me to take shortcuts. But I've got a funny little problem. If the user is typing at the end of the rich edit control, and they type in a keyword and it gets colored, any text typed after tha...

Legend Changes After Save
I am working in Excel 2007. When I delete certain Series from the legend, save and close the file, the next time I open it the deleted series are back. How can I stop it from defaulting back? Thanks! PD, I can't duplicate this problem, why don't you send me a copy of the file before you delete the item from the legend and tell me exactly what you do, so I can duplicate it. -- Cheers, Shane Devenshire shanedevenshire@sbcglobal.net Microsoft Excel MVP "WolfgangPD" wrote: > I am working in Excel 2007. When I delete certain Series from the legend, > save and...

Problem changing parent Business unit
WE have re-arranged some business units into a more appropriate hierarchy, however, we have noticed that a user in a role with deep privileges (own business unit and children I think) to read users can only see users in his own business unit and only one of the child business units (there are 2 child business units). We cannot enable him to see all the users in both child business units. We tried reassigning the parent business unit to the one child that is not appearing, but it doesn't seem to make any difference. WE also tried adjusting the privileges up and down (i.e. Global and local...

Printing changes layout
I am printing a booklet on Pub 2002 - when I print one page, it prints a page where everything has changed - resized, cut off the edges, etc. The worst part - when done printing, it goes back to normal view and it actually changes my layout to match what is printed - permanently. Any ideas what is going on? I printed on both my Lexmark and HP printers, does the same thing. I have Windows XP. Help! Thanks! Look at your page layout, has it changed? I know this will happen to me occasionally when I change printers. If I change back to the original printer the publication will revert...

changes to message size limits
On Exchange 2003, one huge annoying feature is that even when I make configuration changes, it seems to take so long for them to actually go into effect. This time it's the message size limits. I tried changing them for an individual user in AD, even told the RUS to resync. An hour later that still hadn't taken effect, so we moved on and changed the global default in Exchange's Message Delivery properties. Another hour later and still no dice. We had a company-wide default of 15 MB (huge, I thought). I increased my account to 30 MB in AD, and then on the Message Delivery pro...

How do I link a shape on a organ chart to another org chart page
If I have a org chart on one page with three shapes, A B C, and a second page with names in shapes of people reporting to C on page 1. How can I click on Shape C on first page and bring up the second page showing the employees who work under him C -right click shape C -Format > Behavior > Double-Click -Select "Go to page: Page-2" Frank Help <Help@discussions.microsoft.com> schrieb in im Newsbeitrag: 9A0D1770-05EE-42D1-ACB3-A0958688AE64@microsoft.com... > If I have a org chart on one page with three shapes, A B C, and a second page > with names in shapes of pe...

How do I add a Trendline to a Pivot Chart
How do I add a trendline to a pivot chart? Help Thanks Leroy Maybe rightclick on one of the series lines in the chart and select Add TrendLine. Leroy wrote: > > How do I add a trendline to a pivot chart? Help! > > Thanks, > > Leroy -- Dave Peterson ec35720@msn.com ...

Font size change
Is there a way to increase the size of the font. I have a large, hi-res screen and MS-Money insists on using something the size of a 6-7 point font. Thanks, In microsoft.public.money, Linda wrote: >Is there a way to increase the size of the font. I have a large, hi-res >screen and MS-Money insists on using something the size of a 6-7 point font. Try changing the font setting in IE to a larger size. View->TextSize. If you want to see the size of the font in money. You must change the computer's font size. within money there is no option to do so. you would have to go to...

pivot table changes when data is refreshed.
Hi, I have a pivot table with grouping based on a field called "period which is of date type. I have grouped it into months and quarters However when i refresh the pivot table whenever the data (which is in seperate sheet) is changed, the groupings are going out. I want t preserve the groupings as they are like a template. How do i stop th changing the format and layout of the pivot table whenever i refres the data? any ideas? please help. regards Kiran:mad ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages d...

in creating a pie chart in excel 2007
How many rows or columns of data can the pie chart plot I once tried 360 cells and got a wonderful Moiré pattern Why not just experiment with 1, 2, 3 dozen and see for yourself if the result is acceptable ? best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "bsalohcin" <bsalohcin@discussions.microsoft.com> wrote in message news:F9CF7752-1816-4DCE-BF82-9C25033AE888@microsoft.com... > How many rows or columns of data can the pie chart plot According to MS:- Data series in one chart 255 (max limit) Data points in a...

Custom Formatting a Chart Data Label
I want to create a chart which will display a zero value on the data label where there is in fact a zero value, and show N/A on the data label where it didn't apply. The formula I am using in the spreadsheet uses a nested formula to make this determination: =IF(AND(B22+C22>0,C22>0),B22/C22,NA()) Presently, it works but displays the #N/A on the chart where I would prefer it simply displayed as N/A. Can I do this with a custom format on the data label?? Thanks so much! I don't know how you all do it but your amazing! -- If you can read this, thank a Teacher... If your read...

Shared workbook and VBA problem
Shared workbook and VBA problem Hi Group, I am new at this Excel stuff and I am hitting my head on a silly problem that I am sure many have seen before. I wrote a very simple spreadsheet where I use the Calendar control to pick some dates. Everything was fine until I decided that I needed to share it on the network with other users. As soon as I share it the calendar control stops working. I get a "runtime error 1004, unable to set left property of oleobject class". That's because the first thing I try to do is position the calendar. If I don't do that, it still doesn't...

Summary Chart
Hello, was hoping to find a solution for the following scenerio or the best chart and process for: 1) Have 6 columns with survey results - the headers are (PQ, PA, PR, SP, DT, CI). 2) The data rows under the headers are numbers from 1 - 6 (basically a rating). I am attempting to show a summary chart for each worksheets (each worksheet has survey results from a particular location). So I will create a chart in each worksheet for each location, and then one worksheet will be a summary chart of all the other worksheets/locations combined Any idea how to do the chart inside each workshee...

Change default view of Activies to Service Activities.
When clicking on any "Activities" tab, CRM shows list of activities of the object, and it's the view: "Open Activity Associated View". Can we customize so that the view "Open Service Activity Associated" will be shown instead? Only show the Service Activities. Hi, Here is something for your interest. Thanks to Michael. http://www.stunnware.com/crm2/topic.aspx?id=JS11 -- PLEASE do click on Yes or No button if this post is helpful or not for our feedback. uMar Khan :: CRM Freelance Consultant Email :: imumar at gmail dot com Blog :: http://umarkhan.word...

Charts not recognizing source data if original linked data is changed.
I am very frustrated by Excel (2003) at the moment. I'm relatively new to using Excel in depth. I have always been able to work my way through most intricacies and pitfalls. But now I have been working in a workbook with linked sheets (and with linked workbooks too) and have two problems that I cannot solve. Right now, I have about 47 sheets that are set up as follows: detail data: this is the raw data the the user enters daily totals: this is calculated data from the detail sheets summary sheets: further sums from the daily totals and a chart object that charts these sums (a line or sc...

VBA & Excel
Hi, I don't know a lot about VBA. I need to make an Exe file with VB to give 3 buttons each button starts an excel work book (book1, book2,book3). I am thinking of a form that appears on the screen. When the "Exit" button is cliked it will make an other copy of the 3 excel files on another folder (say: d:\data) for example (like a back up copy). Can any one help thanks Hi You can not make exe files with VBA. If you can use VB5 or VB6 instead then adapt the VBA code from http://www.erlandsendata.no/english/index.php?d=envbaolecontrolexcel see also http://www.erlandsendata...

Pivot Chart Created from Pivot Table is Blank
HELP!!! I am following a tutorial in an Excel 2007 book step-by-step. In one of the lessons I was able to successfully and easily create a pivot chart from a pivot table. In my next lesson, I am following the exact same steps (even have started over a couple of times), and when I create a pivot chart from the pivot table in this lesson, it creates a blank chart. WHY DOES IT DO THIS??? I know I have followed the steps correctly. Any ideas on why Excel isn't cooperating with me on this lesson would be soooo appreciated. Hi, Blank charts usually result from having your cursor in...

Create Folders in Directory with Excel VBA?
Hi, Is there a way to create folders with a specfic names, and have them saved in a directory. I have an excel file with 250 records with my desired folder names. I want to avoid having to through the process of selecting <File | New | Folder> then naming the folder -- 250 times! I imagine there's an easier way with VBA... please help! Thanks It can be done something like this. Attatch code to a command button on the sheet. Alter code to suit your reqirements. Test on a dummy workbook first until you get it to work the way you want. Please note that there are practical lim...

Changing Report Fields
What happens to the reports if we change screen fields. Does anyoneknow what happens in the event of: - adding a field – presumably doesn’t appear on any report - changing a field – label or properties - removing a field? Any help please? Hi, - Adding a field would change nothing to a report. You will have to alter the report itself to make it visible there. - Labels are set inside the report itself, so this won't change if you change them on the form. - Removing the field will not change the report, but it will be empty becaus the user cannot enter data for that field (unless som...

Changing of Cell protections after saving Excel File (2002)
This problem occurs when I protect a document using a macro 4.0 function: =PROTECT.DOCUMENT(TRUE,,,TRUE,TRUE). When I use the function within a macro4.0 macro, on an original file, everything works fine. The sheet has unlocked cells, and when the sheet is protected, it allows me to access those cells. But if I save the file, or save.as another name, then the fun begins. The enable selection of the sheet( view codes) has gone from 0-xlNoRestrictions to -4142- xlNoSelection. This locks me out of doing anything in the sheet. When I unprotect and then re-protect the sheet using the T...

how do I change cell references automatically in formulas
In Excel 2000, I have data in 80 rows and 10 columns. Each week I add a new row. I have a separate chart for each column with the data range from the first row to the last.. Each week I have to change the data range to reflect the new last row for each chart. Is there someway I can do this automatically? http://peltiertech.com/Excel/Charts/Dynamics.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "jnw3" <jnw3@discussions.microsoft.com> wrote in message news:8A3551F9-FBC6-4841-95B4-618AB1893190@micros...

Change the anchor property of a control.
Hello. How can I capture the event, when changing the anchor property of the control ? I.e : myPanel.anchor = AnchorStyles.Left & AnchorStyles.Bottom .... How can I know, when the Anchor property is changed (which event?) ? Thanks :) Am 12.06.2010 23:46, schrieb Mr. X.: > Hello. > How can I capture the event, when changing the anchor property of the > control ? > > I.e : > myPanel.anchor = AnchorStyles.Left & AnchorStyles.Bottom > .... > How can I know, when the Anchor property is changed (which event?) ? Like I said, about once o...

Cannot remember my e-mail logon password, how do I change it?
I use outlook 2007 and have a need to access my e-mail logon password. I've got the remember password box checked but can not remember the password myself. Is there anyway to retrieve this or change or recreate it? You wuld have to contact your ISP to reset or provide you with the password "Rviviani" <Rviviani@discussions.microsoft.com> wrote in message news:B5DAFCAB-FC29-4FF4-A5E2-AE4DDE6DC2A0@microsoft.com... >I use outlook 2007 and have a need to access my e-mail logon password. >I've > got the remember password box checked but can not remem...