Change chart range with macro

Greetings.  I am trying to name a variable that is the last row of data in a 
range, then use that variable in a chart's range.  I tried inserting it where 
the last row would be, but it is not working, like this "A20:A & LstRow,... 
Anyone have any ideas?

    LstRow = [B51].End(xlUp).Row

    MsgBox (LstRow)
    
    Charts.Add
    ActiveChart.ChartType = xlLine
    ActiveChart.SetSourceData Source:=Sheets("Mar").Range( _
        "A20:A & LstRow,C20:C46,E20:E46,G20:G46,I20:I46,K20:K46,M20:M46"), 
PlotBy:=xlColumns
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Mar"
    ActiveChart.HasLegend = True
    ActiveChart.Legend.Select
    Selection.Position = xlRight
0
3/27/2009 4:51:02 PM
excel.charting 18370 articles. 0 followers. Follow

1 Replies
541 Views

Similar Articles

[PageSpeed] 16

Ok, I got it.  It should be like this RANGE("A20:A" & LstRow & ",C20:...

"Greg Snidow" wrote:

> Greetings.  I am trying to name a variable that is the last row of data in a 
> range, then use that variable in a chart's range.  I tried inserting it where 
> the last row would be, but it is not working, like this "A20:A & LstRow,... 
> Anyone have any ideas?
> 
>     LstRow = [B51].End(xlUp).Row
> 
>     MsgBox (LstRow)
>     
>     Charts.Add
>     ActiveChart.ChartType = xlLine
>     ActiveChart.SetSourceData Source:=Sheets("Mar").Range( _
>         "A20:A & LstRow,C20:C46,E20:E46,G20:G46,I20:I46,K20:K46,M20:M46"), 
> PlotBy:=xlColumns
>     ActiveChart.Location Where:=xlLocationAsObject, Name:="Mar"
>     ActiveChart.HasLegend = True
>     ActiveChart.Legend.Select
>     Selection.Position = xlRight
0
3/27/2009 4:57:03 PM
Reply:

Similar Artilces:

Pie chart in columnar report
I’ve created a columnar report – one record per page. It shows the values in the various columns of my table, which is linked from Excel. Columns R and S each contain a percentage; the two values for each record add up to 100%. I've added a pie chart to the report, but how do I get the chart on each record page to show the R and S values for that record adding up to 100%? (Access 2003) Many thanks for any suggestions. Dave9023 The chart control has Link Master/Child properties that filter the chart to a value or values in the report's record source. The chart contro...

Change the file name in Macro
I created Macro that open several workbooks (actually 17 of them) at the same time like this; Workbooks.Open Filename:= _ "H:\Month End Work\FY 2009\02-09 RUI SALES\01 - Feb09 CFW.xls" Workbooks.Open Filename:= _ "H:\Month End Work\FY 2009\02-09 RUI SALES\11 - Feb09 CFW.xls" Workbooks.Open Filename:= _ "H:\Month End Work\FY 2009\02-09 RUI SALES\21 - Feb09 CFW.xls" It works fine till I have to use it for the following month. I have to go back to my Macro and change manually the directory from 02-09 to 03-09 and file name from Feb09 to Mar0...

Changing file in all upper case to upper and lower case
I have a file of names and addresses that were entered in all upper case. Is there a fast way to change this file into upper and lower case? Thanks for any help you can give me. -- S Jan http://www.mvps.org/dmcritchie/excel/proper.htm -- Regards, Peo Sjoblom (No private emails please) "Sagit" <Sagit@discussions.microsoft.com> wrote in message news:99B059C4-F5D8-4DD4-BABC-A16AFA3AA6D1@microsoft.com... >I have a file of names and addresses that were entered in all upper case. >Is > there a fast way to change this file into upper and lower case? Thanks &g...

2007 excel autofilter change back to 2003 autofilter?
Is it possible to change the 2007 autofilter back to like the 2003 autofilter worked? I see you have a response to one of your other posts -- in a different forum. jonnybrovo815 wrote: > > Is it possible to change the 2007 autofilter back to like the 2003 autofilter > worked? -- Dave Peterson ...

Change Business unit for Team
Is there a way to move a team from one Business unit to another? I can't find one. Ketil You can set the parent business unit of a team by using the SetParent method http://msdn.microsoft.com/library/en-us/crmsdk/htm/bizteamsetparentmethod.asp there are examples for C# too I hope this helps Andreas Donaubauer [MVP f�r CRM] MCP since 1999, MCSE, MCSA andreas[at]donaubauer.com http://www.crmfaq.de "Ketil Samuelsen" <ketilsam@frisurf.no> schrieb im Newsbeitrag news:5cdab81a.0501250110.3daad9fd@posting.google.com... > Is there a way to move a team from one Business...

Outlook 2007 ignoring "don't save changes" in calendar
HI, I have a strange problem with Outlook 2007 and am hoping someone can assist. It goes something like this: - I create a meeting request for 3pm today (the time doesn't matter) and send it to someone. - I then open up that appointment/meeting in my calendar and change the time to 5pm. - Before saving or sending the update I decide that I actually don't want to change the time of the meeting. - I press the X button to close the window and I'm asked whether I want to save the changes and send an update, or don't save changes. - I click don't save changes...

Formula to sum in columns that change weekly
I enter a column of data weekly into a spread sheet and then total each row for the last 4,8& 12 weeks. As the column numbers to be added change weekly I have to change the formula weekly. Is there a way to do this automatically? Assuming no blanks, this will total the last 4, 8 and 12 weeks in row 2: =SUM(OFFSET($A$2,0,COUNTA($2:$2)-4,1,4) =SUM(OFFSET($A$2,0,COUNTA($2:$2)-8,1,8) =SUM(OFFSET($A$2,0,COUNTA($2:$2)-12,1,12) In article <lf%Lb.3326$Z%4.1704@newssvr24.news.prodigy.com>, "Robert L. Salisbury" <rlsalisbury@c-air-s.com> wrote: > I e...

Stacked bar charts of plan versus actual
Is there an FAQ that tells me how I can create month-by-month stacked bar charts of plan versus actual? I need to plot a set of products (plan versus actual), and each month I would have an additional pair of stacked bars. Thank you. You don't want stacked, because that would add actual to plan. make a clustered (side-by-side) column chart. To get a chart that shows a better trend than clustered columns, keep the clustered column type, but format the plan columns with a border and no fill, and the actual columns with a fill and no border, plot the actual series first and the plan ...

toggling a chart on and off
Hello! I use Excel 2000. I have a macro that creates a chart. I assigned this macro to a autoshape. When I am done looking at the chart I have to delete it Isn't there a way to make things in such a way that I could just toggl the chart to appear and dissapear? For example with an additional macr or toggle button or something... This is how the macro looks like: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 25.2.2004 by Registered User ' ' Range("B3:J3,B5:J5").Select Range("B5").Activate Charts.Add ActiveChart.ChartType = xlColumnClustered Acti...

Excel 2000
I am pretty much a newbie at Excel (don't use it as much as I would lke to be able to). Is there a way to start up a macro when a particular spreadsheet is opened ... I don't want this macro to start on all opened spread sheets. I know I do I can do that, but can't find out anywhere so far that tells me I can start up a macro when I open a particular spreadsheet. Any help with this would be much appreciated. Abay You can put your code behind the ThisWorkbook module and name it workbook_open(). Or you can put your code behind a general module and call it Auto_Open(). ...

how do i change the default directory in Outlook?
I do not use the My Documents Directory. The bulk of the files I save are kept on a server drive which is backed up daily. I wish Outlook to default to this server directory when I am saving. Outlook 2003. Jeff Bryenton <Jeff Bryenton@discussions.microsoft.com> wrote: > I do not use the My Documents Directory. The bulk of the files I save > are kept on a server drive which is backed up daily. I wish Outlook > to default to this server directory when I am saving. Outlook 2003. One way is to change the definition of "My Documents" so that it points to the server. A...

OWA2003 "Change Password" button problems?
The following link: http://support.microsoft.com/default.aspx?scid=kb;en-us;823175 describes how to change the urlscan.ini file to allow things to pass through unaffected. However now that I've got the "Change Password" button to appear, I get a "file not found" error when I click on it. I've used their ..ini file with a few modifications (to loosen it for my environment). I think I understand that because I run Exchange 2003 on Windows Server 2000, that I have to allow .htr files. I've done this but it still doesn't work. It says in one unqualified s...

How to ensure recipient can't change message
Is there a setting I can put on a message I send so that the recipient is prevented from changing any of the content, including attachments? Would this apply even when the recipient forwards the message? Use F1 help to read up on the topic of message sensitivity. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After furious head scratching, Hall asked: | Is there a setting I can put on a message I send so that the | reci...

can i change plot area colors for different date ranges
if i want to shade the plot area of a chart diferent colors, for example gray from Jan 1 through Fed 17, then white until March 10, is that possible? Will want to change the shadinging preiodically and fairly frequently. Thank you Hi, You should be able to use the technique explained on Jon's page http://peltiertech.com/Excel/Charts/ColoredVerticalBand.html Cheers Andy steve wrote: > if i want to shade the plot area of a chart diferent colors, for example gray > from Jan 1 through Fed 17, then white until March 10, is that possible? Will > want to change the shadinging pr...

Vertical ND Curve on Combination Chart
I would like to plot a normal distribution curve horizontally (on the Y axis) in a chart with vertical data histogram plots (up and down) and horizontal line plots (+/- 1 SD) – all currently using the Y axis. I have the histogram/SD portion built. Is there a way to use a secondary axis on the left side of the chart for the nd curve, yet have the nd curve appear on the right side of the chart? Actually, it may not make a difference just which axis, Y or secondary, the plots are attached to so long as the plots can be placed on the chart in an orderly fashion to display three issues: h...

Forumla to change values on situation
Hi all, I have a table of items with "value" "vat" and "total" Columns. I would like to write a formulae to say When value is less than 140.5 then change value to 85.5, vat to 14.96, total to 100.46 Else no change. so im guessings its something like: X=1 If AX<140.5 then AX=85.5, BX=14.96, CX=100.46 Else AX=AX,BX=BX,CX=CX Increment x Or i could just copy and paste the If Then statment down all the columns and forget the increment. Hopefully you will see what im trying to do. Thanks in advance, any help is much appretiated as always. Ernest Lai...

Unhide macro worksheet
I have created a macro in my personal.xls worksheet. I want to delete the macro but am getting a message that I need to unhide the worksheet. I cannot locate the worksheet where the macro is stored and cannot find the command to unhide it. Help!!! Thank you very much, -- JoeF hi the command to unhide the sheet.... 2003 on the menu bar>format>sheet>unhide 2007 Home tab>cells group>Hide&hide>unhide sheet regards FSt1 "JoeF" wrote: > I have created a macro in my personal.xls worksheet. I want to delete the > macro but am get...

Adding a button to a spreadsheet that runs a macro
I would like to add a button to a spreadsheet that runs a macro. I have completely forgotten how...brain dead!!! I'm up in the arctic circle without any reference books and "Help" isn't doing it for me. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Doc, Add a button from the Forms menu (View -> Toolbars -> Forms) If it doesn't automatically pop up the dialog Right click the button and choose Assign Macro. Dan E "doc" <do...

Change Status by Worklfow not working!
I had a simple workflow to change account's status to active. The workflow is an "On demend" one. The workflow is running succeeded. The line in workflow isfor change record status is done. But the inactive account is not changed to active!!! No error found at all. The same workflow is working at another CRM installation(with rollupdate 2). The one has porblem is on rollupdate 4. Any help is great. Thanks -- MS CRM consultants at Melbourne Visit my CRM blog at http://melbournecrm.spaces.live.com/ ...

macro to copy Vlookup formula to some cells with a filter on
Hello, I was wondering if somebody could help me with this macro… I have some data with a filter on, so that only the rows with blank cells are showing. I want to enter a VLOOKUP in the cell, and copy it down to all the blank cells. I tried just recording a macro, but when I run the macro it doesn’t work. Any ideas of how to do it? Example: 1) Before the filter: Colums A -B - C A x x - 1st Row A - 2nd Row A x x - 3rd Row A - 4th row A x x - 5th row 2) After the fil...

change build of myClassLib to Release instead of Debug?
I created a class library (dll) in "Active Debug" mode. After testing it out and seeing it works correctly I went to the Properties page of my Project to the Build section and changed the configuration to "Release" from "Active Debug". The Output Path says "bin\Release\" But when I click on Build myClassLib -- it is still building it in bin\Debug. How do I change this to bin\Release\? Thanks Rich *** Sent via Developersdex http://www.developersdex.com *** Rich P wrote: > I created a class library (dll) in "Active Debug"...

Hyperlink to named ranges and sheets in Excel not working in 2007
I code hyperlinks to named ranges and worksheets all the time in 2003, but since we've moved to 2007, they do not seem to work. The spreadsheet opens, but does not open on the desired worksheet. No reference error seems to be called (even if I deliberately miscode the sub-address). Anyone know what is up? Example: //Network Path/Folder/Spreadsheet.xls#NamedRange -- Thanks, Andy ...

macro to calculate percentage
i have attached a small spreadsheet in yellow column i need percentage (for example G5/G11) but the proble is that number of rows keep changing like instead of G11 it can b G100,G500 another problem is that i want persentage column in column J becos th columns can go upto I please suggest i will be thankful to u al Attachment filename: book12.xls Download attachment: http://www.excelforum.com/attachment.php?postid=53213 -- Message posted from http://www.ExcelForum.com Don't multi-post. See answer in programming. -- HTH Bob Phillips ... looking...

emailing spreadsheet with macros
i have worked out some code to send a link to a spreadsheet to my manager to get his signature. I placed a command button in that spreadsheet to automate the signing process for him. the problem i am having though, is if s/he opens the spreadsheet from the link in the email, the file opens, but macros are disabled. if the manager goes out and opens the file from its network location it does fine and the command button works. I am sure this has to do with security issues in outlook, but maybe there is a way to work it out. Thanks for your help. natan Often comapnies ahave f...

Version change for an Active Document server
Hi, I have an application which can act as an Active Document Server - i.e. you can copy an object from the application and paste it into PowerPoint. The object apperas as a rectangle with some text - and if you double click on it in the PowerPoint slide, (and if you have my application installed), a form opens up that provides more information about the object & even edit it. This is a SDI application where the document is derived from COleDocument & I have a ServerItem derived object as well. I override the Serialize method, which allows the PowerPoint document to store all the in...