Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
Chart Series using non-contiguous calls in VBA
I'm trying to create charts in Excel from a bunch of non-contiguous cells
using VBA. The cells are Sheet1!$C1:C2,Sheet1!$C4:C5,Sheet1!$C6:C7,Sheet1!$ C9:C10, etc. I first tried to set Series.Formula with a string that I generated from the sheet & cell names, but I had enough cells that I hit that 250 character limit in the Series.Formula arguments. I tried a number of other tactics, including: Trying SeriesCollection.Extend (didn't work, since I'm trying to put data from 1 sheet into the chart) Trying to just set Series.Values & Series.XValues separately Applying a name to the renge I wanted to plot, & feeding that into . Formula, then .Values (I can't remember what else) After all this, I found out something absolutely maddening So maddening indeed that I may be without teeth & hair by the end of the day. I can: 1 select the series manually with the mouse, and select the "Source Data" context menu 2 type "=Sheet!NamedRange" into the Values field & hit OK 3 see that the plot has the right data 4 manually select the series again & copy the text of the series fomula from the formula bar 5 paste said text into VBA code that sets Series.Formula 6 FLIP MY FREAKIN LID BECAUSE THE CODE DOESN'T WORK!!!! Richo.Breathe.Value = xlSlowly Richo.Wait(10) Anyway, does anyone know why this doesn't work? Am I doing something wrong, or do multi-area ranges somehow work in Excel, but not in VBA? Obviously I can always make a new sheet specifically for the chart data, but that somehow seems inelegant... Any help / kind words are appreciated, Richo |
#2
|
|||
|
|||
Chart Series using non-contiguous calls in VBA
Richo -
Obviously I can always make a new sheet specifically for the chart data, but that somehow seems inelegant... My point of view on this is: if it works, it's elegant. I much prefer making a separate sheet or range for the data. It makes it easier to run in the first place, and figure out what's wrong in the second, and it usually is much faster than working through the more classically elegant formulations. You can always hide the worksheet if you think you'll be ashamed! - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Richo via OfficeKB.com wrote: I'm trying to create charts in Excel from a bunch of non-contiguous cells using VBA. The cells are Sheet1!$C1:C2,Sheet1!$C4:C5,Sheet1!$C6:C7,Sheet1!$ C9:C10, etc. I first tried to set Series.Formula with a string that I generated from the sheet & cell names, but I had enough cells that I hit that 250 character limit in the Series.Formula arguments. I tried a number of other tactics, including: Trying SeriesCollection.Extend (didn't work, since I'm trying to put data from 1 sheet into the chart) Trying to just set Series.Values & Series.XValues separately Applying a name to the renge I wanted to plot, & feeding that into . Formula, then .Values (I can't remember what else) After all this, I found out something absolutely maddening So maddening indeed that I may be without teeth & hair by the end of the day. I can: 1 select the series manually with the mouse, and select the "Source Data" context menu 2 type "=Sheet!NamedRange" into the Values field & hit OK 3 see that the plot has the right data 4 manually select the series again & copy the text of the series fomula from the formula bar 5 paste said text into VBA code that sets Series.Formula 6 FLIP MY FREAKIN LID BECAUSE THE CODE DOESN'T WORK!!!! Richo.Breathe.Value = xlSlowly Richo.Wait(10) Anyway, does anyone know why this doesn't work? Am I doing something wrong, or do multi-area ranges somehow work in Excel, but not in VBA? Obviously I can always make a new sheet specifically for the chart data, but that somehow seems inelegant... Any help / kind words are appreciated, Richo |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I display multiple series in a pie chart? | Charts and Charting in Excel | |||
1 Chart - Different series format | Charts and Charting in Excel | |||
How to hide a dummy series in a chart legend? | New Users to Excel | |||
How to change Series order in a Combination Chart? | Charts and Charting in Excel | |||
How to change Series Order in a Combination Chart? | Excel Discussion (Misc queries) |