Dynamic Charts #7

I have a 2 column data set that I need to chart.  The last row of this 
dataset can increase or decrease.  I need the chart to reflect the values 
dynamically.

For this, I am calculating the last row value in the macro and transferring 
it to the variable "lr".

When I try to introduce the value of lr in the following statement and run 
it,  I get an error "Run-time error '1004': Method 'run' of object 
'_application' failed"

activechart.setsourcedata source:=sheets(custname).range("c1:d" & lr & "), 
plotby:=xlcolumns


Does anyone know how to resolve this problem?

0
vrk1 (10)
8/31/2005 6:01:02 PM
excel.charting 18370 articles. 0 followers. Follow

4 Replies
367 Views

Similar Articles

[PageSpeed] 2

Your syntax is wrong. Remove the last quote:

   activechart.setsourcedata source:=sheets(custname).range("c1:d" & lr),
     plotby:=xlcolumns

But this kind of thing can be done without using VBA. You can create a 
named range with a formula that detects the number of rows, and adjusts 
accordingly:

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

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


vrk1 wrote:

> I have a 2 column data set that I need to chart.  The last row of this 
> dataset can increase or decrease.  I need the chart to reflect the values 
> dynamically.
> 
> For this, I am calculating the last row value in the macro and transferring 
> it to the variable "lr".
> 
> When I try to introduce the value of lr in the following statement and run 
> it,  I get an error "Run-time error '1004': Method 'run' of object 
> '_application' failed"
> 
> activechart.setsourcedata source:=sheets(custname).range("c1:d" & lr & "), 
> plotby:=xlcolumns
> 
> 
> Does anyone know how to resolve this problem?
> 
0
8/31/2005 6:40:08 PM
Thank you Jon.  I need to do this via VBA as this is a client requirement.

I removed the last quote like you suggested and I am getting the following 
error:

"Run Time Error 91:  Object Variable or withblock variable not set."


I played around with this a bit and also tried replacing the above statement 
with the following.  But this time, I got a different error "Run Time Error 
1004: Method Run of Object '_Application' failed.  Can you help please?  
Thanks for your time!


Set myrange = Range("c1:c3, d1:d" & lr)
    With ActiveChart
        .SetSourceData Source:=Sheets(custname).Range(myrange), 
PlotBy:=xlColumns
        .Location Where:=xlLocationAsObject, Name:=custname
        .HasTitle = True
        .ChartTitle.Characters.Text = UserForm1.title1 & " - " & custname
        .Axes(xlCategory, xlPrimary).HasTitle = False
        .Axes(xlValue, xlPrimary).HasTitle = False
    End With

0
vrk1 (10)
8/31/2005 9:09:05 PM
 > 1004: Method Run of Object '_Application'

Are you using Application.Run anywhere?

Also, this is going to give you problems: Range("c1:c3, d1:d" & lr)

Do you mean Range("c1:d" & lr) ?

This is how your little snippet of code should look. Define the 
worksheet-qualified range first, then just use the range variable in 
SetSourceData

Sub Test123()
   Dim myrange As Range
   Dim lr As Long
   Dim custname As String

   lr = 4
   custname = "Sheet1"
   Set myrange = Worksheets(custname).Range("c1:d" & lr)
   With ActiveChart
     .SetSourceData Source:=myrange, PlotBy:=xlColumns
   End With
End Sub

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

vrk1 wrote:
> Thank you Jon.  I need to do this via VBA as this is a client requirement.
> 
> I removed the last quote like you suggested and I am getting the following 
> error:
> 
> "Run Time Error 91:  Object Variable or withblock variable not set."
> 
> 
> I played around with this a bit and also tried replacing the above statement 
> with the following.  But this time, I got a different error "Run Time Error 
> 1004: Method Run of Object '_Application' failed.  Can you help please?  
> Thanks for your time!
> 
> 
> Set myrange = Range("c1:c3, d1:d" & lr)
>     With ActiveChart
>         .SetSourceData Source:=Sheets(custname).Range(myrange), 
> PlotBy:=xlColumns
>         .Location Where:=xlLocationAsObject, Name:=custname
>         .HasTitle = True
>         .ChartTitle.Characters.Text = UserForm1.title1 & " - " & custname
>         .Axes(xlCategory, xlPrimary).HasTitle = False
>         .Axes(xlValue, xlPrimary).HasTitle = False
>     End With
> 
0
8/31/2005 10:19:40 PM
That worked Jon!  Thanks for your time.


"Jon Peltier" wrote:

>  > 1004: Method Run of Object '_Application'
> 
> Are you using Application.Run anywhere?
> 
> Also, this is going to give you problems: Range("c1:c3, d1:d" & lr)
> 
> Do you mean Range("c1:d" & lr) ?
> 
> This is how your little snippet of code should look. Define the 
> worksheet-qualified range first, then just use the range variable in 
> SetSourceData
> 
> Sub Test123()
>    Dim myrange As Range
>    Dim lr As Long
>    Dim custname As String
> 
>    lr = 4
>    custname = "Sheet1"
>    Set myrange = Worksheets(custname).Range("c1:d" & lr)
>    With ActiveChart
>      .SetSourceData Source:=myrange, PlotBy:=xlColumns
>    End With
> End Sub
> 
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
> 
> vrk1 wrote:
> > Thank you Jon.  I need to do this via VBA as this is a client requirement.
> > 
> > I removed the last quote like you suggested and I am getting the following 
> > error:
> > 
> > "Run Time Error 91:  Object Variable or withblock variable not set."
> > 
> > 
> > I played around with this a bit and also tried replacing the above statement 
> > with the following.  But this time, I got a different error "Run Time Error 
> > 1004: Method Run of Object '_Application' failed.  Can you help please?  
> > Thanks for your time!
> > 
> > 
> > Set myrange = Range("c1:c3, d1:d" & lr)
> >     With ActiveChart
> >         .SetSourceData Source:=Sheets(custname).Range(myrange), 
> > PlotBy:=xlColumns
> >         .Location Where:=xlLocationAsObject, Name:=custname
> >         .HasTitle = True
> >         .ChartTitle.Characters.Text = UserForm1.title1 & " - " & custname
> >         .Axes(xlCategory, xlPrimary).HasTitle = False
> >         .Axes(xlValue, xlPrimary).HasTitle = False
> >     End With
> > 
> 
0
vrk1 (10)
9/1/2005 2:16:15 PM
Reply:

Similar Artilces:

Export Chart to JPG in VB.NET
Hi, I tried searching this group and others to find a way to export a chart from excel using vb.net but I can't seem to find any solution. I'm trying to avoid using VBA. I'm trying: xlsheet.ChartObjects("ChartName").Export("test.jpg", "jpg") but that doesn't seem to be working. The export method doesn't seem to work. I get this error: "Public member 'Export' on type 'ChartObject' not found." Any help would be great. Thanks, JP Nevermind, I found it. Wrong: xlsheet.ChartObjects("ChartName").Export...

IE 7 and Restricted sites
I went into restricted sites and add facebook on my son's computer and now I would like to remove that so he can get to facebook. The problem is when I go back into restricted sites it is not listed; however when I open up the facebook page it show at the bottom restricted site. How do I remove this to allow him to go there? He has Microsoft Vista on is computer. Thank you Sheila Are you thinking of Content Advisor vs. Restricted Sites security zone? [Placing facebook.com in Restricted Sites zone wouldn't keep him from accessing the website.] sheila4typing wrote: ...

Read Receipts #7
I have two offices with a 20-computer windows 2k network at each, with Outlook 2k CW installed on all computers. All clients at both offices are set to automatically send a read receipt. If somebody at office A sends a message to everybody in office A, they get a receipt from all. If somebody at office A sends a message to everybody at office B, they receive a read receipt from ONE recipient only. Why would they not receive a receipt from the others at office B. Thanks. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usen...

Pivot Charts
I noticed that you are limited to the amount of editing you can do to a pivot chart such as sizing it differently. Is there a way to allow me to size it, like you would in a regular Excel chart?? Thanks [This followup was posted to microsoft.public.excel.charting with an email copy to David M.. Please use the newsgroup for further discussion.] Not directly, no. PivotCharts are limited in their formatting capability. However, if you create a regular chart from the PivotTable data, you will have more control over the chart. Of course, to do so requires (a) the PT be formatted with...

How to specify pie chart total?
Hi guys, I'm sure this is me being dim but can you help. I have a worksheet showing a column of figures that each shows the number of different 'incidents' that have occurred. At the bottom of the sheet is the overall number of calls. Many calls do not become an 'incident'. Some calls can end up with two or more incidents. I'd like to create a pie chart showing the percentage of each type of incident - with much of the pie chart blank as many calls did not progress to becoming an incident. For the life of me I can't work out how to do this. I can easily c...

anyone know how to plot Ternary Charts in Excel ?
Is it possible to plot Ternary (x vs y vs z) in Excel, like in Deltagraph ? Hi, Here are some links, from previous posts, on ternary plots. http://www.sfu.ca/~marshall/ternplot.htm http://www.uni-wuerzburg.de/mineralogie/links/drawing/ternary.html http://www.geologynet.com/programs/ternplot.xls http://www.prodomosua.it/zips/triangolare.xls http://www.lboro.ac.uk/research/phys-geog/tri-plot/ http://www.agri.upm.edu.my/~chris/tal/talxl.html ftp://www-sst.unil.ch/ftp/jfurrer/Trix_fr.zip http://www-sst.unil.ch/perso_pages/jfurrer/Applications/Trixcel/Trixcel.htm Cheers Andy Nigel Goodwin wrot...

Excel 2007
I can't select a label and move it with a radar chart and excel 2007. Whenever I click a category label, all the labels are selected as well as the chart, so it's not possible to move one individually. ...

Pick up date for Charts from the Spreadsheet
I have a workbook with one data sheet and several charts which represent weekly activity. I want to be able to pick up the title of the charts from the spreadsheet, e.g. "Sales Activity for Week Ending April 7, 2006". This title information is contained in a cell in the spreadsheet. How do I get it to display at the top of each chart without having to manually type it in the title area under Chart Options? Wilfred: Here' a link to a post I have on dynamic titles that sounds like what you are looking for. http://processtrends.com/pg_chart_dynamic_chart_title.htm I ...

surface charts in 2-D
There are horizontal and verticle lines across the surface chart in 2-D. Can these be removed. They do not respond to the gridline feature. I have been unable to find a way to remove them. You can't click on them either. Hi, You need to remove the border from each series. http://spreadsheetpage.com/index.php/tip/removing_lines_from_a_surface_chart/ Cheers Andy Sarah Jurak wrote: > There are horizontal and verticle lines across the surface chart in 2-D. Can > these be removed. They do not respond to the gridline feature. I have been > unable to find a w...

Page Breaks #7
How do I remove the page break lines (-------) around the pages after I go into print view mode??? tools>options>view and uncheck page break Regards, Peo Sjoblom "chris" wrote: > How do I remove the page break lines (-------) around the pages after I go > into print view mode??? Chris Tools>Options>View Tab Then deselect 'Page Break' at bottom left. Alex "chris" wrote: > How do I remove the page break lines (-------) around the pages after I go > into print view mode??? ...

extending source data in several charts
I have a big Excel doc with numerous charts, each with numerous lines. About once per month, I need to extend the charts for about a month. This is a very painful process for each graph (multiplied by numerous graphs!). Does anyone have any simple method to do a mass change, standard or VBA? This would be extremely helpful. I also noticed some posts using OFFSET, but this would be too confusing for my father (for whom I am doing this) and too cumbersome. Something like a standard function or a VBA macro that I can program would make his life so much easier. Thanks much! if you don...

Tally chart cells exist in Excel!??
Hello I was wondering if for example there exists a way in which yo have a cell which u put a line like ║ to represent "1" so fo example if you have 5 of those lines in a cell, i want the cell besid to display the number "5". Is this possible, if so can anyone explain me how to do it Thanks for your time From John Woo -- Neo ----------------------------------------------------------------------- Neo1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3032 View this thread: http://www.excelforum.com/showthread.php?threadid=50618 H...

is there a way to enter data interactively and dynamically?
Hi all, Can I enter data interactively and dynamically? To give an example: Approach 1: Currently, when I enter grades for students, I have to find his/her name first, after finding that row, I have to scroll the row to the far right, since there are so many columns already there. Let's suppose that I scroll to column 32, and it is often times hard to remain on the same row(then I ended up entering a wrong score for this student ...) ; suppose I scrolled successfully with my eyes staring at that row, and then I enter score. And so on and so forth for the next student, etc. The wh...

Dates in Charts
I have a chart that I have created based on data that lists dates and amounts. The dates are not consecutive however when I create the chart it shows all dates March 1, March 2, March 3 Even though I did not enter March 2 at all. How do I stop it from doing that? you cant. just like if you had numeric data 0, 3, 9, 15 and plotted it. the numbers in between are still shown on the axis. AGP "Erika" <Erika@discussions.microsoft.com> wrote in message news:4BC1E855-7741-44A3-9E5F-7E928A16AE15@microsoft.com... >I have a chart that I have created based on data that lists ...

6 items line-column chart on 2 axis
I would like a bar chart to show (in 2 bars) the forecast dollars and actual percentage, where the forecast $$ use the right axis and the actual % use the left axis. The final 4 indicies will be charted using a line chart. When I create this chart my bars stack instead displaying beside each other presumable because each bar uses a different axis. Is there anyway to show the bars in separate columns? Thank you. http://peltiertech.com/Excel/Charts/ColumnsOnTwoAxes.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "...

variable height variable width stacked bar charts
Hi I need to create a chart which would resemble a variable width variable height stacked bar chart with values on both sides of the Y axis (This I achieved by giving values needed on the left a negative sign). I tried to get the above chart using all these basic charts in excel - stacked bar charts, stacked column charts,stacked area charts, stacked scatter plots but could not get the final chart. Though I was able to get a variable height stacked bar chart(with pos n neg values), I am not able to get the final desired chart. Please let me know how I can get the above mentioned chart. Than...

Changing Default Chart Format
Hello all, I I'm trying to change the format a chart has by default. Basically what I'm trying to change is the grey background in the chart area, making it white by default on any new workbook I make a chart on. Can this be done? Thanks in advance, Cheers Juan Hi, Jon Peliter's article explains this. http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=299 Cheers Andy Saurjusa wrote: > Hello all, > > I I'm trying to change the format a chart has by default. Basically what I'm > trying to change is the grey background in the chart area, making ...

Pivot Table Chart Legend
Hello all. I am currently developng a Line chart via a pivot table. One thing I have noticed is that the legend runs in ascending order. E.g. Z at the top down to A's at the bottom. Is there any way of getting the legend "flipped" so it runs A-Z without amending the pivot table itself?? Thank you. Hi, Double check the chart type. A flipped legend would suggest a Stacked Line chart rather than a plain Line chart. Cheers Andy Chiccada wrote: > Hello all. > > I am currently developng a Line chart via a pivot table. One thing I have > noticed is that th...

Exporting from Smartlist to Dynamic Excel Spreadsheet
Hi, I have a smartlist in GP V10.0 that shows me Purchase Order line items that are outstanding with our suppliers. I currently export to Excel to track shipments as they arrive. My question is "How do I export the smartlist as a Dynamic Excel Spreadsheet so that it automatically updates each time I open it rather than my current manual update?" Thanks. Wayne The optimal solution is to create an excel sheet that has your Company database as the datasource to get updated records when needed. Regards, -- Mohammad R. Daoud MVP, MCP, MCBMSP, MCTS, MCBMSS Mob: +962 - 79 -999 6...

Charts change by user
I have a set of charts to track daily data. On my PC with my resolution they look perfect. But when other users look at them with their lower resolutions they look different. The legend and other items move and do not fit the chart anymore. Is there a way to make the charts look the same no matter who looks at them and no matter what their resolution is? Thanks! Tim - I suppose you could export them as GIF files, and report them in a web page. Then everyone has the same low resolution. - Jon ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index...

Disappearing charts
I have a chart I added a text box to and now the graph will not print if I use a print area. If I print the chart window, the graph prints but not if I print using a print area that includes data outside of the chart window. Any ideas how I can get my chart to print including the graph? ...

Trying to do this chart
I'm hoping somebody could tell me how to do this chart: I have several scheduled tasks that start at different times and run for various durations of time. I'd like to create a chart with the scheduled times as the Y axis and durations as the X axis. I'd like to have bars (or lines, etc.) that fill in for the duration. So the end result will be something where I can see from a glance how many of these processes are overlapping. Any ideas? tod It sounds like you want a Gantt Chart. http://www.peltiertech.com/Excel/Charts/GanttLinks.html "Tod" <todtown@swbell...

SBS 2003 and Windows 7 Folder Redirection
Hi I have a simple Windows 2003 server, and we have 4 XP Pro clients... I have setup folder redirection for Desktop and My Documents to my users home directory.., and this works fine..., no problems... We have just brought a Windows 7 machine, and I have added it to the Domain and usual, rebooted and logged in, but the folder redirection doesn't seem to be working...? Is there something else I have to do here to get this to work...? Any help is mush appreciated.... Col Vista and Windows 7 use a different group policy for folder redirection that is not backwar...

Hash marks in a chart
I have a chart where I am charting Left Center and Right weights of a strip of material. My chart is fine for showing the average of these 3 measurements and I have a second chart showing the ranges. But I also need it to show hash marks for each of the L, C, R measurements. So, for example. left =260 center = 257 right = 263 The average is shown using the standard charting but the individual numbers (260, 257, 263) need to be shown also Now the left , center, and right on the chart will need to be represented like this: Left = \ Center = - Right = / Any Idea how I might accomplish this? ...

automatic chart update for my stocks
Hi: I have a question: Is there a way of creating excel chart that would automaticaly update my chart with the latest stock price? Please respond and Thanks! Bogdan [This followup was posted to microsoft.public.excel.charting with an email copy to Bogdan. Please use the newsgroup for further discussion.] See the Dynamic Charts tutorial on my web site. -- Trouble finding replies to your posts? Use a newsreader. See the tutorial 'Outlook Express and Newsgroups' on my web site Regards, Tushar Mehta, MS MVP -- Excel www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tut...