Just an idea. Before this line:

'' Add first series (sometimes charts.add does, sometimes not)

insert this:

'' Delete first series
Do While ActiveChart.SeriesCollection.Count 0
ActiveChart.SeriesCollection(1).Delete
Loop

This will delete all series before you start adding new ones. There
might be something about the first series (if it's present when the
chart is created) that prevents VBA from accessing the series formula.

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

z.entropic wrote:

Thanks for your effort, Jon. I tried your suggestion in the form:

'' declare variables for active sheet name and selected range
Dim sSheet As String
Dim rRange As Range
Dim sAddr As String
sSheet = ActiveSheet.Name

'' add chart and put it where you want it
Charts.Add
ActiveChart.Location Whe=xlLocationAsObject, Name:=sSheet

'' Add first series (sometimes charts.add does, sometimes not)
If ActiveChart.SeriesCollection.Count = 0 Then
ActiveChart.SeriesCollection.NewSeries
End If

'' Define the data and type
sAddr = ActiveSheet.Range(ActiveSheet.Range("H2"), _
ActiveSheet.Range("H2").End(xlDown)).Address _
(ReferenceStyle:=xlR1C1)

ActiveChart.SeriesCollection(1).Values = _

"=" & sSheet & "!" & sAddr
ActiveChart.SeriesCollection(1).Values = "=" & sSheet & "!R2C8:R288C8"
ActiveChart.SeriesCollection(1).XValues = "=" & sSheet & "!R2C9:R288C9"
ActiveChart.SeriesCollection(1).Name = "=" & sSheet & "!R1C9"
ActiveChart.ChartType = xlXYScatterLinesNoMarkers

but the macro bombed out at the line with the error 1004 message again... I don't know what else I can ask for; perhaps that you e-mail your first working worksheet with the example given to so I could try on my system? I don't want to exceed my welcome here, especially that I can't contribute much in terms of VBA expertise...

z.entropic


"Jon Peltier" wrote:


I wonder if this will work (I don't know why the other doesn't work) in
place of the line I cited below.

Dim sAddr As String
sAddr = ActiveSheet.Range(ActiveSheet.Range("H2"), _
ActiveSheet.Range("H2").End(xlDown)).Address _
(ReferenceStyle:=xlR1C1)
ActiveChart.SeriesCollection(1).Values = _
"=" & sSheet & "!" & sAddr

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

z.entropic wrote:

There is data in rows 2-288, and then empty rows to the end.

z.entropic

"Jon Peltier" wrote:



This line is giving the error:

ActiveChart.SeriesCollection(1).Values = _
"=" & sSheet & "!R2C8:R1000C8"

What's in H2:H1000?

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


z.entropic wrote:


Jon, again, thanks for your time. I tried an empty and populated
cell with the same result. I tried a range with two columns,
including and not, the title. I tried three and more. All the
same...

When I moved the ActiveChart.ChartType = xlXYScatterLinesNoMarkers
line on top of the block, I obtained TWO time series lines, not an XY
scatter chart, instead of the bar graph chart which I was getting
before the move. The points plotted are those highlighted before the
macro is run, not those given in the RC segment.

z.entropic

"Jon Peltier" wrote:




And you would only need one valid data cell in the range for it not
to give that particular error.

I ran the code myself to make sure the chart was created and
populated as expected. What's in your range?

One more point, I had selected a blank cell prior to running the
macro. What's in the active cell when you start it?

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

z.entropic wrote:



Sorry for talking to myself, but my guess was wrong. Changing
the range to filled cells only didn't fix the problem.

z.entropic

"z.entropic" wrote:





I think I know what might have gone wrong: the specified
range, just in case, is larger than the number of existing data
points (it contains empty cells), but one of the reasons for
error 1004 is, to quote, "The method can't be used in the
applied context. Specifically, some Range object methods
require that the range contain data. If the range does not
contain data, the method fails. "

Is that it? If it is, I know there is a way to include
automatically only filled cells... Funny that my half-working
macro with a renamed worksheet does not fail under the same
conditions.

Isn't programming great? ;-)

z.entropic

"Jon Peltier" wrote:





Sorry, Z, it wasn't so long ago I struggled with this stuff,
I should try to remember what it was like.

I'll start with the code you posted halfway up this thread.
We'll define variables for the sheet name and the range. The
range you selected before running the wizard in the macro
recorder has no relation to the ranges used later, so we can
leave it out.

Your Y values for both series are in column H, and the X
values are in columns I and J. Series names are in row 1,
data in rows 2 through 1000.

Sub MakeAChart() '' declare variables for active sheet name
and selected range Dim sSheet As String Dim rRange As Range
sSheet = ActiveSheet.Name

'' add chart and put it where you want it Charts.Add
ActiveChart.Location Whe=xlLocationAsObject, Name:=sSheet

'' Add first series (sometimes charts.add does, sometimes
not) If ActiveChart.SeriesCollection.Count = 0 Then
ActiveChart.SeriesCollection.NewSeries End If

'' Define the data and type
ActiveChart.SeriesCollection(1).Values = _ "=" & sSheet &
"!R2C8:R1000C8" ActiveChart.SeriesCollection(1).XValues = _
"=" & sSheet & "!R2C9:R1000C9"
ActiveChart.SeriesCollection(1).Name = "=" & sSheet & "!R1C9"
ActiveChart.ChartType = xlXYScatterLinesNoMarkers

'' Add second series and define data
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Values = _ "=" & sSheet &
"!R2C8:R1000C8" ActiveChart.SeriesCollection(2).XValues = _
"=" & sSheet & "!R2C10:R1000C10"
ActiveChart.SeriesCollection(2).Name = "=" & sSheet &
"!R1C10" End Sub

This procedure was tested and works just fine. I might make
additional adjustments, but I follow the engineer's creed: If
it works, you haven't messed with it enough.

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

z.entropic wrote:





OK, let me explain mine and others' problem: your answer is
it's like getting a book in a foreign language with one
sentence translated... For you guys fluent in VB, mine and
others questions may seem ridiculous and we may seem dumb,
but since I see a macro code once in a quarter and don't
know any of these German-looking VB statements with 5 terms
joined together, I get stumped by incomplete answers. You
yourself quickly added three additional points to your
original reply...

I and, I trust, many other readers/posters do appreciate
your time and effort in helping us guide through the VBA
jungle, but many of your and others' off-side comments
convince me it would be a waste of my time to try to learn
VBA for the few things I need it. In the end, I have to
rely on your and others' goodwill, time and effort which,
hopefully, is a free ad for your business when more serious
problems must be solved.

Finally, both Jon's and your proposed solutions to my
original problem might seem trivial to you, pros, but after
trying many versions of the modified macro, I still don't
know how to write/create a simple chart-creation macro
working in a worksheet with an arbitrary name. So, here is
a simples chart-making macro--how should it be modified
using your or Jon's ideas to make it independent of the
"Sheet1" name?

Regards and thanks,

z.entropic

"Tushar Mehta" wrote:






I gave you a specific example of how to work around the
hardcoded names. Where are you lost?

Three additional points.

First, while the XL macro recorder sets the charttype
first and the sourcedata second, you need to reverse
those steps in your macro.

Second, to get strings for the Values and XValues
properties, use something like

....Values= "='" & SrcSheet.name & "'!R2C8:R1000C8"

Third, to create a chart that refers to only those cells
that contain data, use something like:

with SrcSheet ....Values= "='" & .name & "'!" _ &
.range(.cells(2,8),.cells(2,8).end(xldo wn))) _
.address(false, false, xlr1c1)

Alternatively, use named formulas. See the Dynamic
Charts page of my web site for more on this methodology.

-- Regards,

Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and
VBA add-ins, tutorials Custom MS Office productivity
solutions

In article
,
says...





Now you both lost me--how do I use your code if my
recorded macro is full of references to the specific
name of the renamed active worksheet:

' Keyboard Shortcut: Ctrl+a ' Range("H2:H10").Select
Charts.Add ActiveChart.ChartType =
xlXYScatterLinesNoMarkers ActiveChart.SetSourceData
Source:=Sheets("Data").Range("H2:H10") , PlotBy:= _
xlColumns ActiveChart.SeriesCollection(1).Values =
"=Data!R2C8:R1000C8"
ActiveChart.SeriesCollection.NewSeri es
ActiveChart.SeriesCollection(1).XValue s =
"=Data!R2C9:R1000C9"
ActiveChart.SeriesCollection(1).Name = "=Data!R1C9"
ActiveChart.SeriesCollection(2).XValue s =
"=Data!R2C10:R1000C10"
ActiveChart.SeriesCollection(2).Valu es =
"=Data!R2C8:R1000C8"
ActiveChart.SeriesCollection(2).Name = "=Data!R1C10"
ActiveChart.Location Whe=xlLocationAsObject,
Name:="Data" With ActiveChart

z.entropic


"Jon Peltier" wrote:






Missed that. Of course, the other solution is to use

ActiveSheet.ChartObjects.Add(L, T, W, H).Select
ActiveChart.SetSourceData
Source:=ActiveSheet.Range("G2:H11")

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

Tushar Mehta wrote:






The Charts.Add creates a new chart in its own
sheet. ActiveSheet at that point refers to the
newly minted chartsheet!

Use something like the untested:

dim SrcSheet as worksheet, aChart as chart set
srcsheet=activesheet set achart=charts.add
achart.SetSourceData
Source:=srcsheet.Range("G2:H11") ....