Chris -
Yes, the NA() thing works very well, except when it doesn't. The NA()
lets you skip over points in a series, but if a chart series has only
blanks or only #N/A (or I guess a combination of the two), it isn't
charted, and VBA isn't as smart about some things as the regular Excel UI.
You have two choices. First, your macro can put a dummy piece of data in
the data range, so the series is temporarily charted while you adjust
the ranges. When you're done, put the #N/A back. Second, you could
temporarily change the chart type of the series to an Area chart, which
treats #N/A as a zero. Adjust the series, then change the chart type back.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______
reynoldscm wrote:
John,
Thanks for the help. It worked wonderfully! I did encounter one problem
however. When there is no data in one of the series of the chart (in my
case I have #N/A when no data is available) the macro stops running. Is
this just a simple error handling opportunity or would there be some
other piece of code that would allow it to continue processing?
Chris
Jon Peltier wrote:
*Chris -
As usual, Excel provides several ways to skin this cat, and not all
are
so efficient.
I usually find it more reliable to edit the components of the series
formula directly, rather than build a new formula. Something like
this:
With ActiveChart.SeriesCollection(3)
..Values = worksheets("RangeData").Range("rngACWP")
..XValues = worksheets("RangeData").Range("rngDate")
End With
Although I just noticed that this changed the name to the static
address. Try this:
With ActiveChart.SeriesCollection(3)
..Values = "='" & worksheets("RangeData").Name & "'!" & "rngACWP"
..XValues = "='" & worksheets("RangeData").Name & "'!" & "rngDate"
End With
If the name is a workbook level name, Excel changes the sheet name
prefix to the workbook name. If it is a worksheet level name, then
Excel
leaves the sheet name intact.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______
reynoldscm wrote:
Hi,
I am trying to reference a dynamic range in my code that modifies
an
existing chart SERIES statement. However, whenever I enter the
dynamic
range name (i.e. rngACWP) Excel inserts the workbook file name in
addition. I have tried referencing the sheet name...which didn't
work,
and then tried to establish a variable that referenced the
ActiveWorkbook name but don't think I did right. So, I basically
have
two questions:
1. Is there a way to utilize some sort of variable in the SERIES
statement so that the user isn't limited to a filename that I
create
for them but dynamically pulls their filename to use in the
formula?
2. If the above anwer is yes, how would I define the variable and
utilize it in the SERIES statement?
Here is the snippet of code I am working with:
ActiveSheet.ChartObjects("CUM Chart").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(3).Select
ActiveChart.SeriesCollection(3).Formula = _
"=SERIES('RangeData'!R1C4,'Automated CSPR Charts -down to 2
buttons.xls'!'rngDate','Automated CSPR Charts -down to 2
buttons.xls'!'rngACWP',3)"
Thanks for the help.
Chris
--
reynoldscm
------------------------------------------------------------------------
Posted via http://www.mcse.ms
------------------------------------------------------------------------
View this thread: http://www.mcse.ms/message450438.html
*
--
reynoldscm
------------------------------------------------------------------------
Posted via http://www.mcse.ms
------------------------------------------------------------------------
View this thread: http://www.mcse.ms/message450438.html