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
896 Views

Similar Articles

[PageSpeed] 19

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:

Summing by Date Range
I have a spreadsheet containing purchases made at various suppliers. Columns are Supplier, Invoice Date and Amount. It's easy enough to use SUMIF and return a figure for how much I spent at each supplier, but I can't figure out to calculate how much I spent in a given span of time, or better yet, how much I spent in a given timespan at a given supplier. Right now my primary concern is calculating how much I spent in each tax year, say, from Jan 1, 1998 to Dec 31, 1998 and so on. It must be simple but I'm breaking my head on it. Any takers? KZ Hi! To sum for a specific time-spa...

Problem viewing existing .XLS file
I saved a 698KB file w/multiple worksheets. When I go to open it, either within Excel or by clikcing on the file name, Excel opens the file, but I cannot see it. The worksheet does not appear inside the window. Excel is showing no scroll bars and no worksheet is visible. I can print out a previously defined print area in the worksheet that I had open when I last saved the file. There are no temp files that I can find. Any suggestions? You can print something or do a print preview? I think I'd try: window|arrange|tiled (and resize by hand) (Maybe the window is just off the visib...

XPath problem with dateTime strings comparison
Hello, I've got an XML file saved from DataSet. One table has datetime column (datatype - xs:dateTime). XML file fragment for example: <Root> <Element>2004-03-15T00:00:00.0000000+01:00</Element> <Element>2004-03-16T00:00:00.0000000+01:00</Element> <Element>2004-03-17T00:00:00.0000000+01:00</Element> <Element>2004-03-18T00:00:00.0000000+01:00</Element> </Root> I'm trying to get MyXmlNodeList using MyXmlDataDocument.DocumentElement.SelectNodes(MyXPathString) method, where MyXPathString, for example, should loo...

Encumbrance process in purchases done in project series
We have a prospect that is interested in the functionality of encumbrance accounting. Since they want to track projects, all POs are going to be registered in project series. I understand that encumbrance accounting works only with purchases done in Purchasing Order Processing module not in project accounting series. Any suggestions... Thanks ...

Dynamic Range??? Please Help
=IF(C7="","",VLOOKUP(C7,'Sheet3 (2)'!$A$2:$C$152,2,FALSE)) this is my formula I would like to be able to add the list. I tried following the dynamic range instrutions but it's not working. Thank you hen I add to the list I want some with the formula and so without. Is that possible. What determines the bottom of your list? Col a, b or c? on the sheet where the list is>insert>name>define>name it mylist or whatever in the refers to box type =offset($a$2,0,0,counta($a:$a),3) then =IF(C7="","",VLOOKUP(C7,mylist,2,0)) -- Don G...

Exchange 5.5 problem #8
I had a currupt IS and renamed it and let another information store be created, my mailboxes came back and all looked well, the mail boxes had the same setings and IMC had the same also no big deal right? Yes, now we are not passing mail. We use this to forward SQL mail to a Lotus notes server. (so it's just a gateway I assume I didn't set it up orginally) I am not sure why....all the settings are the same and I have changed nothing else Is there something I forgot THanks in advance Any error messages anywhere? See if you can send mail using Outlook. Russ Allen wrote: > ...

Problems connecting to accounts
I am having problems downloading from most of my accounts. Some work but most do not, there is nothing I can do about this, is there? ...

Problem finding duplicate email addresses
Hello, I am trying to compare two columns that contain email addresses. The goal is to identify email addresses that exist in both columns. I tried VLOOKUP, and Countif functions but I can't get them to return any results. I also did a copy and paste special (multiply) to make sure the formatting in both columns is the same. Column A has email addresses of people who read the email (total 168) I sent out, and Column C has all the email addresses (total 1938) it was sent to. Can anyone please help? Thank you! Sandeep You can go to Chip Pearson's web site, scroll down to the D'...

Strange problem modifying system menu
I am creating a CMDIChildWnd in my app. In the onCreate handler for the window, I am using GetSystemMenu() and AppendMenu to add a separator and then another menu item. The strange thing is, if the window is created maximized, my menu item is always disabled. Otherwise it works. I have tried handling ON_WM_INITMENU as well, but it didn't help. Do you have handler defined for it? joe On Wed, 11 Jul 2007 17:02:08 -0000, "Mr. T" <ted.pederson@gmail.com> wrote: >I am creating a CMDIChildWnd in my app. In the onCreate handler for >the window, I am using GetSystemM...

Update fixes Loan Pmt Problem--Any other fixes noticed???
MS Money updated M07 last evening when I logged in. I immediately checked to see if the Loan Payment problem was fixed... and it was... unless it was fixed with a previous update and I just didn't realize it, Microsoft actually fixed something! What other fixes or improvements has anyone noticed Anna Oh....I just found the KB articled on this update, and this lists the fixes. http://support.microsoft.com/kb/927811 Anna wrote: > MS Money updated M07 last evening when I logged in. I immediately > checked to see if the Loan Payment problem was fixed... and it was... > unless it...

Rounding Problem #4
I'm performing a golf handicapping sheet and within that sheet I'm using an average of previous scores such as; In cell AM33 I have =average(q33,w33,ac33,ai33)-35 This value ends up as 11.5... My rounding goes up to 12 which is what I desire but... when I utilize that cell value to subtract 2 other values the sheet does not round properly..? Example: In cell AO33 I insert 48 In cell AP33 I insert =sum(AO33-AM33) Looking at AM33 as a 12 and looking at AO33 as 48, I expected the result to be 36 but it shows 37..! How can I make sure that the rounding carries into the new formula ...

I have come across a problem with VARCHAR(MAX)
Hi, I Have a stored procedure in which I am dynamically creating a query and executing it. At times the length of the query may exceed 8000 charaters. So I tried with VARCHAR(MAX) for declaring the variable to store the query string. But the length of string is not going beyond 8000. Following is a simple simulation where also I am getting this problem. DECLARE @MESSAGE VARCHAR(MAX) SET @MESSAGE = REPLICATE('1234567890',5000) SELECT LEN(@MESSAGE) Can anyone tell me if there is any other way out of it? Thanks in advance Lancy EggHeadCafe - Software Developer Portal of...

Named Range #2
I've named few ranges through VBA by using this code For i = 1 To 15 ' Define the name for the variables ActiveWorkbook.Names.Add Name:=Worksheets("ST&GT DATA").Cells(1, i), RefersToR1C1:= _ "=OFFSET('ST&GT DATA'!R1C1,'ST&GT DATA'!R1C214," & (i - 1) & ",'ST&GT DATA'!R1C216,1)" Next i which is working fine, but when i try to refer it in a chart, an error msg is popping up saying "Your formula contains an invalid external reference to a worksheet, Verify that the path, workbook, and range name...

Edit Named Ranges
I have a Named Range, Alaska, with the cities in Alaska I need to add more cities, so I need to change the range to includ more cities. Also I have a named range, Alabama, with cities in Alabama. I need to delete cities, so I need to change the range to include les cites. Any Advice or hints? thank You in advanc -- Message posted from http://www.ExcelForum.com Try dynamic ranges: http://cpearson.com/excel/named.htm#Dynamic In article <monagan.1ad7se@excelforum-nospam.com>, monagan <<monagan.1ad7se@excelforum-nospam.com>> wrote: > I have a Named Range, Alaska, ...

Outlook 2002 daylight saving problem
Hi guys, I am in australia. I have my GMT set correctly. +10 Sydney. I have a SBS2000 box running exchange with exchange sp3. I have outlook 2002 with the latest sp. Ever since day light saving came into effect, my appointments to parties external to the network have been out by an hour, if I send an appointment to someone internally its fine. I have checked the day time settings in Outlook and they look fine. I have ensured that the adjust for day light saving is ticked in outlook as well as Win2k. Anyone have any ideas how to fix this? Is this a problem with exchange? ...

search two tables for all dates in range
Hi all (Access 2003) I have a union query which combines all dates for an activity from two tables (Itinerary [ReviewDate] and Itinerary Dates [ReviewDates]). I then have another query which links this query with the Itinerary table and filters on a specified period (startdate and enddate), it only shows the records and start dates from the Itinerary table so I get the Itinerary displayed only once. This returns the correct recordset but it is not updateable and I need the user to be able to edit the records on my form. Is there another way round this please? Thanks... Sue ...

usage of series and justify in excel
just want to know what is the usage of series and justify in excel -- Scorpionk88 ------------------------------------------------------------------------ Scorpionk88's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25790 View this thread: http://www.excelforum.com/showthread.php?threadid=392198 If you look in Excel's help, you may find the information you need. If not, you could provide more information on the problem you're trying to solve, and someone may be able to help. Scorpionk88 wrote: > just want to know what is the usage of series and ...

Problem Applying Formula to cell
Hi I have a problem a with macro I have recorded/written. The macro applies a formula to a range of cells which references values in a column in a separate worksheet and returns cell contents from this worksheet then deletes blank rows. The macro runs as expected when the formula has been entered manually in the starting cell but as soon as i try to apply the formula as part of the macro (commented out second line) it returns error code 1004. Range("A3").Select ' ActiveCell.FormulaR1C1 = "=IF('Raw data'!$I1-25 =TODAY(),'Raw data&#...

Installation Problem with 10.0
I am having an issue installing GP 10.0 on our client machines. I have it installed on our server and up and running but am getting the following message when trying to install on the clients. "Error" "ProductInfo,Product,Attribute" I am always getting this after we get to the ODBC section and it asks us for the server name. We have entered the same of our new server and also the name of the server that GP is using when running on the new server. (For some reason on the new server you have to call it server Dynamics GP instead of the name we gave our new server). Ca...

Problem with named range for a large spreadsheet in Excel 2007
Hello, I am trying to generate a large Excel spreadsheet apprx. 10000 rows and 40 columns. I am generating defined name section in the Workbook.xml part of the XLSM package. Here is a sample entry from that section <definedName name="_._44802_._0_._0_._0_._top_line" localSheetId="0" hidden="1">Sheet1!$B$2</definedName> Although the generation goes fine, I can not open the spreadsheet as the Excel throws an error message saying the package is corrupt. But this is not the case if the spreadsheet is small say, 200 rows by 10 columns. ...

HELP! Copy a Data Series to Another Graph?
Hi- I'm using Excel 2002 and I've got a lot of graphs, all of which are based on a common spreadsheet and all are VERY similar. When I add a new data series to one graph, it would be wonderful if there was a simple way to copy this data series to the other graphs. Can it be done? (Now I add the new line to the first graph. Then I copy the X-values from the first graph, open the second graph & paste, re-open the first graph and copy the Y-values, re-open the second graph & paste, third graph, fourth graph, etc., etc., etc.......) Just make the series refer to a defined na...

Small problem with a line chart
Hi I have a smal problem with a line chart. The chart shows progress of my teams league points over a season, the data is derived from a series of rows which I fill in after a game. In the data, I am using COUNTBLANK to have a blank cell until the row contains data EG =IF(COUNTBLANK(P53:Y53)>0,"",((P53+U53)*3)+Q53+V53) The problem is that the data series assumes unfilled rows (IE the future unplayed games) are zero, so there is a line that connects from the top of my line down to the zero on my X axis. I would just like the line to stop at the last value. Can anyone suggest a w...

Public Variable problem
I have the line below in a module: Public NoSave As Boolean In my Auto_Open routine I have: Sub Auto_Open() ' Events.Enable_Events If ActiveWorkbook.ReadOnly = True Then NoSave = True Protection.ProtectAllSheets Else NoSave = False Protection.UnProtectAllSheets End If Module7.StartPoint Module6.StartTimer End Sub Then I in ThisWorkbook ihave the following" Private Sub Workbook_BeforeClose(Cancel As Boolean) If NoSave = True Then ThisWorkbook.Close False...

Outlook 2003
I have several email accounts set up. I set up a rule to move email received on a specific account to a specific folder. Works great. The problem is that I have also set up rules to move junk email with specific text to the Delete Items folder. Primarily I'm happy with body part sizes and I'm not sick so the medication offers just aren't for me. :) Anyway when these rules run, even after specifying the order in which I run them the messages that match the "junk" list are still appearing in my Inbox and subfolder as if they were never moved. I have seen them ...

Delegate Problem in Outlook 2003
Hello, I am having this weird problem in Outlook. Outlook 2003 Small Bisiness Edition on Exchange 2000. Latest patches and Service packs on both client and server. All Outlook versions are the same. I am also the Exchange admin for the organization All of the sudden the Delegates cannot view calendar items. The items are NOT marked private. In any case the box is checked to allow users to view private items. This person has a very full calendar adn the Delegate can see the days in bold that indicate there is something there, but cannot see the individual items. When I log in as a Do...