A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Charts and Charting
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

macro to create charts in worksheets with arbitrary names



 
 
Thread Tools Display Modes
  #11  
Old June 11th, 2004, 01:21 PM
z.entropic
external usenet poster
 
Posts: n/a
Default macro to create charts in worksheets with arbitrary names

Jon,

I truly appreciate your reply to my cry of despair ;-).

I've tried your code and see a chart appear with a single bar at X=1 and Y=10, but subsequently the macro chokes on the 'Define the data block'

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

with a run-time error message 1004
'Unable to set the Values property of the Series class'

What went wrong? I use Excel 2002 10.2614.2625 under Win2KPro with the latest updates.

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(xldown))) _ .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.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=Data!R2C9:R1000C9"
ActiveChart.SeriesCollection(1).Name = "=Data!R1C9"
ActiveChart.SeriesCollection(2).XValues = "=Data!R2C10:R1000C10"
ActiveChart.SeriesCollection(2).Values = "=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") ....





  #12  
Old June 11th, 2004, 01:27 PM
z.entropic
external usenet poster
 
Posts: n/a
Default macro to create charts in worksheets with arbitrary names

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(xldown))) _ .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.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=Data!R2C9:R1000C9"
ActiveChart.SeriesCollection(1).Name = "=Data!R1C9"
ActiveChart.SeriesCollection(2).XValues = "=Data!R2C10:R1000C10"
ActiveChart.SeriesCollection(2).Values = "=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") ....





  #13  
Old June 11th, 2004, 01:51 PM
z.entropic
external usenet poster
 
Posts: n/a
Default macro to create charts in worksheets with arbitrary names

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(xldown))) _ .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.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=Data!R2C9:R1000C9"
ActiveChart.SeriesCollection(1).Name = "=Data!R1C9"
ActiveChart.SeriesCollection(2).XValues = "=Data!R2C10:R1000C10"
ActiveChart.SeriesCollection(2).Values = "=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") ....





  #14  
Old June 11th, 2004, 05:48 PM
Jon Peltier
external usenet poster
 
Posts: n/a
Default macro to create charts in worksheets with arbitrary names

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(xldown)) ) _ .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.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=Data!R2C9:R1000C9"
ActiveChart.SeriesCollection(1).Name = "=Data!R1C9"
ActiveChart.SeriesCollection(2).XValues = "=Data!R2C10:R1000C10"
ActiveChart.SeriesCollection(2).Values = "=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") ....





  #15  
Old June 11th, 2004, 08:14 PM
z.entropic
external usenet poster
 
Posts: n/a
Default macro to create charts in worksheets with arbitrary names

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(xldown)) ) _ .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.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=Data!R2C9:R1000C9"
ActiveChart.SeriesCollection(1).Name = "=Data!R1C9"
ActiveChart.SeriesCollection(2).XValues = "=Data!R2C10:R1000C10"
ActiveChart.SeriesCollection(2).Values = "=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") ....






  #16  
Old June 12th, 2004, 11:20 PM
Jon Peltier
external usenet poster
 
Posts: n/a
Default macro to create charts in worksheets with arbitrary names

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(xldown))) _
.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.NewSeries
ActiveChart.SeriesCollection(1).XValues =
"=Data!R2C9:R1000C9"
ActiveChart.SeriesCollection(1).Name = "=Data!R1C9"
ActiveChart.SeriesCollection(2).XValues =
"=Data!R2C10:R1000C10"
ActiveChart.SeriesCollection(2).Values =
"=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") ....





  #17  
Old June 14th, 2004, 01:53 PM
z.entropic
external usenet poster
 
Posts: n/a
Default macro to create charts in worksheets with arbitrary names

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(xldown))) _
.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.NewSeries
ActiveChart.SeriesCollection(1).XValues =
"=Data!R2C9:R1000C9"
ActiveChart.SeriesCollection(1).Name = "=Data!R1C9"
ActiveChart.SeriesCollection(2).XValues =
"=Data!R2C10:R1000C10"
ActiveChart.SeriesCollection(2).Values =
"=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") ....






  #18  
Old June 14th, 2004, 05:34 PM
Jon Peltier
external usenet poster
 
Posts: n/a
Default macro to create charts in worksheets with arbitrary names

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(xldown ))) _
.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.NewSeries
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).Values =
"=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") ....





  #19  
Old June 14th, 2004, 08:14 PM
z.entropic
external usenet poster
 
Posts: n/a
Default macro to create charts in worksheets with arbitrary names

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(xldown ))) _
.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.NewSeries
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).Values =
"=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") ....






  #20  
Old June 16th, 2004, 05:25 AM
Jon Peltier
external usenet poster
 
Posts: n/a
Default macro to create charts in worksheets with arbitrary names

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") ....





 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:54 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.