Slightly Urgent: Problem Resetting Chart Value Ranges

[LarryP]

WITH APOLOGIES FOR RE-POSTING, PUT IT IN CHARTS ORIGINALLY AND GOT NO
RESPONSES.

I have a macro that is supposed to reset three data ranges (one X, two Y)
based on the number of data rows in the current worksheet. The first two
work fine, the third one errors out, saying it is "unable to set the Values
property of the Series class." I'm I'm darned if I can see why, as the code
seems identical to the two previous lines. The code follows, and below that
a sample of the data it's referring to:

Sheets("S-curve Chart").Select
ActiveChart.ChartArea.Select
##works## ActiveChart.SeriesCollection(1).XValues = "='S-curve Chart
Data'!R2C1:R" & Chart6LastRow & "C1"
##works## ActiveChart.SeriesCollection(1).Values = "='S-curve Chart
Data'!R2C3:R" & Chart6LastRow & "C3"
##fails## ActiveChart.SeriesCollection(2).Values = "='S-curve Chart
Data'!R2C5:R" & Chart6LastRow & "C5"
ActiveChart.Deselect


Here's a few rows of the data from the worksheet. The code is referring to
columns A, C, and E, and further down, Col E DOES have data in
it. I thought the latter (i.e., nulls in the first several rows) might be
causing the error, but filling Col E with zeroes or other values didn't help.

ColA ColB ColC ColD ColE
181 $0 $0 $-
180 $- $0 $-
179 $- $0 $-
178 $- $0 $-
177 $- $0 $-
176 $- $0 $-
175 $- $0 $-

 

[J Sedoff comRemove>]

My first guess would be that the graph number/name changed, I had something
similar happen before (no warning, I hadn't even changed anything, try
re-recording the macro, just need to select the graph and do a couple basic
command to see if the name changed).

My second guess would be the lines with:
"R2C5:R" & Chart6LastRow & "C5"
It seems like you have the R1C1 format (ex: R2C5:R18C5), so is Chart6LastRow
= 3 (or some integer/long data type)?

From my experiments though, it looks like (from what I can reasonably
recreate) it should work just fine...

Hope this helps,
Jim

 

[LarryP]

Yup, Chart6LastRow is a Long, in my test case 182. The code substitutes that
value for both of the previous lines, but fails on the third one. With
regard to the graph name/number, I recorded a macro doing what I want
manually, and it generated exactly the code shown (except for inserting the
variable value, of course, but that works fine on 2 out of 3 lines). So I am
flat stumped on why that third reset won't work. Thanks for your thoughts,
if anything else comes to mind by all means let me know.

 

[J Sedoff comRemove>]

(Stupid question, I know, but it can't hurt...) And you know that the chart
you are trying to modify already has two y-variables set up? I'm not sure if
it would screw up if you didn't have a second series previously set up, but
maybe it's a shot (in the dark, *cough*).

Jim

 

[LarryP]

Yes, this is an existing chart with an X and two Y's. Didn't want to muddy
the water with too much information before, but this is all contained in a
humongous macro-rich workbook that serves as a template for numerous users.
The user imports from a raw data file (Excel), does a whole series of things
to it, and one of several results is the data in the worksheet that feeds
this chart. And wouldja believe, with some source files it works and with
some (or at least one!), the aforementioned error occurs.

The infamous Column E, which appears to be the root of the problem, starts
out with formulas all the way down, but to prevent Excel from charting
zeroes, the very last VBA step just before trying to reset the chart series
evaluated the whole column and sets everything that isn't a valid, >0 number
to null.

Don't know if that will ring any Aha! bells, but I sure appreciate your
sticking with me through all this.