Series range construct problem

Stupid problem really 

 I cannot properly construct a series range object for my non adjacent
series columns:


MyRows = Range("A1").End(xlDown).Rows
 

 ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range( _
        "A2:A" & MyRows & " , C2:C" & MyRows, E2:E" & MyRows), 
        PlotBy:=xlColumns
    

The above range object is the problem.

What am I doing wrong?


Thanks

Bill
0
bill
7/28/2004 12:06:26 AM
excel.charting 18370 articles. 0 followers. Follow

2 Replies
699 Views

Similar Articles

[PageSpeed] 54

Bill -

Try this approach.

Sub DoChart()
     Dim MyRows As Long
     MyRows = Sheets("Sheet1").Range("A1").End(xlDown).Rows

     Dim rng1 As Range
     Dim rng2 As Range
     Dim rngChart As Range

     Set rng1 = Sheets("Sheet1").Cells(2, 1).Resize(MyRows - 1)
     Set rng2 = Sheets("Sheet1").Cells(2, 3).Resize(MyRows - 1)
     Set rngChart = Union(rng1, rng2)

     ActiveChart.SetSourceData Source:=rngChart, PlotBy:=xlColumns
End Sub

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

bill.. wrote:

> Stupid problem really 
> 
>  I cannot properly construct a series range object for my non adjacent
> series columns:
> 
> 
> MyRows = Range("A1").End(xlDown).Rows
>  
> 
>  ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range( _
>         "A2:A" & MyRows & " , C2:C" & MyRows, E2:E" & MyRows), 
>         PlotBy:=xlColumns
>     
> 
> The above range object is the problem.
> 
> What am I doing wrong?
> 
> 
> Thanks
> 
> Bill

0
7/28/2004 12:41:32 AM
That did it!

Thanks

Bill

On Tue, 27 Jul 2004 20:41:32 -0400, Jon Peltier
<DOjonNOTxlmvp@SPAMpeltiertechME.com> wrote:

>
>     Dim rng1 As Range
>     Dim rng2 As Range
>     Dim rngChart As Range
>
>     Set rng1 = Sheets("Sheet1").Cells(2, 1).Resize(MyRows - 1)
>     Set rng2 = Sheets("Sheet1").Cells(2, 3).Resize(MyRows - 1)
>     Set rngChart = Union(rng1, rng2)
>
>     ActiveChart.SetSourceData Source:=rngChart, PlotBy:=xlColumns
>End Sub

0
bill
7/28/2004 1:41:11 AM
Reply:

Similar Artilces:

Named Range
2007 with compatibility with 2003 In my data validation I have the following formula =IF(C23="",Settle_List,INDIRECT(payout_opt)) payout_opt can be several options If the range name of "selected" payout_opt is static =Model_info!$M$3:$M$14 everything works fine, bue whent I want to change the range name dynamically to =OFFSET(Model_info!$M$3,0,0,Model_info!$M$37,1) or =Indirect("Model_info!$M$3:M"&M37+2) it doesn't (The dropdown function only lists the first options. What am I doing wrong? ...

SQL Server Remote Connection Problem
Hi All, I did RDP to a server that has SQL server named ABCsql. From my local machine, I created a new connection and put ABCsql in Server name dropdown box and selected Windows Authentication/SQL Server Authentication (sa,sa) but it failed to connect using Database Engine. Because I want to remote to the SQL Server: ABCsql but I don't know how to do it. Thanks for any help from you guys. ...

Rank and multiple Tie-breaks problem
I have 5 columns each containing 7 scores, where only the best 4 score are used to create a total-at the bottom of each column. For this used the SUM(LARGE,..,1...)etc. I then used Rank to rank these scores, with the highest score receivin the lowest rank. My problem is that to resolve a tie-break situtation I want to use th next highest score in the tieing columns to have a unique rank; i these are equal, then the 6th highest score would be used, and s on-but only in the columns that are tied. This sounds simple, and probably is, but I'm no good at VBA and only beginner in Excel, so ...

Add formula to cell to sum range of cells
I am trying to write a macro that selects a range of cells and then format a nearby cell to show the sum of that range. The code i have does so by selecting the range, naming it, and using the name in the formula. The problem is that I want to run this for multiple sheets in the workbook. I can't figure out how to use a variable to name the range and use it in the sum formula. Using ActiveSheet.name + "Days" in the sum formula creates an error. Range("B11").Select Range(Selection, Selection.End(xlDown).Offset(-1#)).Select ActiveWorkbook.N...

Problem editing Publisher 2003 document since last Office03 Update
The problem is that a publication previously created, when opened can now no longer be edited. I can get a "select all" option for trying to copy into new publication, but "copy"/"save" etc are all greyed out. Any clues valued as to how I unlock.? On format tab for instamce every option greyed out except "Auto Fit". Recall publication was originally created in earlier version, 2000 I recall, but have always been able to edit since our IT dept upgraded earlier in year. Have checked other pub files on hard drive and they all edit OK. OS=Win XP2. Thanks...

Delete Range if duplicate value
I have a list of names in column B row 1 thru 50 of a worksheet. I would like to search the column for duplicate values in column B only and remove any duplicates. I also have data below the search range that I would like to stay in cell B200. I have tried some codes from previous posts, but they all either searched on column A or you had to select the column before you started the macro also any data below the search area was brought up by the amount of rows deleted Thanks for you help Hi try the following macro (borrowed from http://www.cpearson.com/excel/deleting.htm#DeleteDuplicateRows...

Bizarre problem
I had a Bizarre problem with Outlook(2002). It started filling up my Inbox with a message that one of my colleagues had sent out using the same account earlier in the day(She had sent an email from her computer from Outlook since we both have access to that email account). Every time I hit Send/Receive it just received the same email over and over again. I logged on to the Web interface and the email was not there, so it was not on my ISP's server. I closed Outlook and opened it again and it would still do the same thing. I rebooted my computer, opened Outlook and it quit doing i...

problem with posting payments on account for multiple stores??
we are using 1.3.1009, has anyone else experienced this or a similar problem in a multi-store environment: we have two separate stores, a customer does one sale on account in each store. they send one check for both sales. we apply the payment in one of the stores at the POS. the account balance for the sale that was done at the same store where the payment is applied gets, zeroed out, but the other balance at the other store does not. i know in hotfix 1010 it mentions that there is something that gets fixed having to do with making payments on account at one store when the sale or...

Moving data series horizontally (offset) to prevent overlapping
Hi, I could use some help with my Line with Markers -chart. My four different series overlap each other a little, which isn't that much of a problem until I add error bars, which are almost impossible to format so that you can know for sure which error bars belong to which data series. Is it possible to move a data series (including its error bars) right or left just a few pixels so the error bars are distinguishable from each other? I've seen this done in some published charts, but can't find a way to do it. Using google I found an example of what I need: http://www.emera...

Problem tracking and sending email in 3.0 #3
This is a CRM 3.0 issue. When sending an email that has been tracked in CRM, a user gets this error: An error occurred promoting this item to Microsoft CRM. The Microsoft CRM server could not be contacted or the user has insufficient permissions to perform this action. Note the following: - CRM can be browsed from Outlook or ID. - I changed the user's role to system admin and did not resolve the problem. - The user created an appointment in Outlook and was able to successfully track it in CRM and save it. Thanks, -Rick M. ...

Painting Range
-- Shrikant continue... ( I accidently pressed 'enter') When I define the range by painting the cells, and when the cursor is on the last row of the range, i had a need to extend the range on the upper part by, say 2 rows. How do I move the curser to the top row without un-painting the selection ? -- Shrikant "Shrikant" wrote: > > -- > Shrikant By pressing Ctrl . (dot), I am able to move my cursor to the four corners of the range without un-painting the range. However, this does not help in extending the range on the top row side. -- Shrikant "...

database problem
i created a database in MS ACESS and i tried to acess it using VC front end.I am getting an error message stating "unrecoganised database format".Can you offer any suggestions.Thanks in advance Jeevan "Jeevan" <fenn_j@rediffmail.com> wrote in message news:<u9KetewaEHA.1652@TK2MSFTNGP09.phx.gbl>... > i created a database in MS ACESS and i tried to acess it using VC front > end.I am getting an error message stating "unrecoganised database > format".Can you offer any suggestions.Thanks in advance > What versions are your running (Access/...

Compare each value in a range to each value in another range
I am looking for a macro that will return a comparison of each cell in a range to each cell in another range. example. the first range would have vales of 1,2,3 and the second range would have values of 5,6,7 Thus the macro should return 9 possible comparisons: 1 & 5, 1 & 6, 1 & 7, 2 & 5, 2 & 6, 2 & 7, 3 & 5, 3 & 6, 3 & 7, Can anybody help me with this?? Just hazarding some thoughts here .. Perhaps using formulas would suffice ? Example: Assume 1st range is A1:A3, 2nd range is B1:B3. Then Compare 1st range against 2nd range I...

XMLDomElement problem (C++)
When I append one Element object to another and when for child I'm trying get pointer on his pather I get different address then orginal Element. I get of course Node pointer of parent but how can I change Node into Element ????? (DOM model of MSXML) ...

Series colors
I cant find the syntax to specify the color of each series in a line chart. Can someone help? Thanks. I get as far asWorksheets("mysheet").Chart(1).SeriesCollection(n) ... Hi Dan, Try something like this, Worksheets("Sheet1").chartobjects(1) _ .chart.SeriesCollection(1).Border.ColorIndex = 4 Dan Sully wrote: > I cant find the syntax to specify the color of each series in a line chart. > Can someone help? Thanks. > > I get as far asWorksheets("mysheet").Chart(1).SeriesCollection(n) ... > > -- Cheers Andy http://www.andypope.info...

chart with two data series and two colors for each data series
Hi! I am using visual studio.net 2.0 and trying to use OWC11 to draw a chart from the database. If its only one data series it is fine but i cant see the second data series. all i was trying to do was display actual and budgeted against year. below are the code for clarification gvPortfolioHisex.DataSource = dtPropertyCapex gvPortfolioHisex.DataBind() 'Now Build a graph from the dataset Dim oChartSpace As New Owc11.ChartSpaceClass() Dim objChart As Owc11.ChChart = oChartSpace.Charts.Add(0) objChart.HasTitle = True objChart.Title.Caption = "Historical Expenditure Graph&quo...

mhtml problems
I have outlook express 5 when I open my email everything comes as an attachment.Can any help? ...

Smtp problem #2
Hello, I have a window small business 2003 server and whenever i start the smtp service , it send a lot of email to the same person. All the machine are spyware free, no virus. The relay of the smtp service on the server is set only for internal machine. What can it be help thanks I'd be asking at this point if you are sure that you are a) relay-secure - do you really need to list *any* IP addresses in the allowed to relay list? I suspect no. and b) how are you sure that you are virus free? You may consider doing a network sniff to see if there are any internal clients atte...

Financial series vs Sales Series
This may or may not be an unusual question, but when I have to do something in GP 9.0 like a utility and it tells me that everyone 'has to be out of the system to do so', does this include anyone who is doing something 'strictly with the financials?' I had told my employer one day that he would have to get out of the system so I could take care of what he requested that I take care of and he said he wasn't doing anything with sales or inventory, just working on the 'financial side' and he shouldn't have to get out. Because of my instance that it would o...

Problem with "att-1.unk" file
I use MS Outlook 2000 SR1 (9.0.0.3821). When receiving e-mail with attached file (extention doesn't matter) from one of my partners I always see this file as "att-1.unk" file. He uses MS Outlook too. This "att-1.unk" may be "stripped" of additional header using programs designed for "winmail.dat" files (for instance tnef.exe). I verified, that my partner used "Plain text", not "Rich text formant" when sending his e-mails. I received the same "att-1.unk" file when I tried to use Outlook Express instead of MS Outlook. Its...

Retentions to vendors invoices in the Construction Industry
I would like to know how to retain a % on vendor invoices. These amounts must be paid at end of contract. This is normal in the construction industry. Any suggestions? ...

MedianIf multiple range and criteria
Hi all, I have this formula that works perfectly for one criteria for the range A3:A5000: =MEDIAN(IF((Calc!$C$3:Calc!$C$5000=1)*(Calc!$A$3:Calc!$A$5000=A19)* (Calc!$E$3:Calc!$E$5000>0),Calc!$E$3:Calc!$E$5000)) I need to modify this formula in order to scan the range A3:A5000 and if any values in this range are equal to A17 and A18 and A19, I need it to return the median of the range E3:E5000>0 AND that also satisfies the condition C3:C5000=1. Is this possible? Any thoughts? Does this make sense? Thanks! If I understood what you want... Array entered: =MEDIAN(I...

PO Printing Problem (sorting)
We recently upgraded to RMS 1.3 from 1.2. When printing a purchase order, I prefer to have the PO sorted by Order No. rather than lookup code (usually a worthless barcode). This makes it vastly easier for my distributors to input these. However, since the upgrade, when I hit print, RMS re-sorts the PO by lookup code. Is there a way to stop this from happening? Under General Options there's a "Don't Remember List Sorts" check box, but this doesn't effect the problem. Thanks! Gary Ray Black Diamond Games Concord, CA gray(at)blackdiamondgames. com This is a multi-part m...

Autofill a series from the name box?
If I want to autofill a series of values from 1 to 10, using the autofill handle is the easiest way to do it. But if I want to autofill 1 to 1000, that's a bit more tedious with the autofill handle. Is it possible to do it from the name box? I've been told it can be done but I can't figure it out. Help doesn't tell you and I've checked several books and references, but they all talk about using the fill handle only. You can use the name box to select the range you're interested in and fill a series. Suppose you want to fill A1:A10 with the sequential number...

problems with an accdb saved as an mdb
I have some customers who have 2003, so I sent them an accdb converted to a 2003 version. When we open it in 2003, most of the toolbars are missing. It acts a lot like a runtime version. Also, some of the macros don't make it through the transition. -- Christy Wyatt Regarding the missing menu/toolbars, under: Office Button | Access Options | Current Database make sure you have checked the boxes for: Allow Full Menus Allow Built-in Toolbars (The Toolbars option does not exist for ACCDB, only MDB.) The macros that did not make it are probably the new embedded macros. If ...