Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > chart macro
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 68
Default chart macro
Hi,

I want to create a macro to create a chart of a certain range which
the user has to select.

The layout and formatting of all ranges (the number of rows and
columns) is exactly the same.
The position of the range differs. X-axys labels are always the same.

When you turn on the recorder following code is part of the VB

ActiveChart.SetSourceData
Source:=Sheets("Australia").Range("A171:H179"), _
PlotBy:=xlRows

What I want to know is how you can make the selected range as a
variable to put in the "range" part.

And will I stumble upon more difficulties?

The complete code of the recorded macro is below

Cheers,

Harold

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 23/05/2008 by Harold Van Velzen
'

'
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData
Source:=Sheets("Australia").Range("A171:H179"), _
PlotBy:=xlRows
ActiveChart.SeriesCollection(1).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(1).Name = "=Australia!R142C1"
ActiveChart.SeriesCollection(2).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(3).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(4).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(5).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(6).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(7).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(8).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(9).XValues = "=Australia!R4C2:R4C8"
ActiveChart.Location Whe=xlLocationAsObject, Name:="Australia"
End Sub
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 6,582
Default chart macro
I added a few lines in the top of the procedure.


Sub Macro2()
Dim rngChartData As Range

On Error Resume Next
Set rngChartData = Application.InputBox(Prompt:="Select Data Range",
Type:=8)
If rngChartData Is Nothing Then
' user pressed Cancel
Exit Sub
End If
On Error Goto 0

Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=rngChartData, PlotBy:=xlRows
ActiveChart.SeriesCollection(1).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(1).Name = "=Australia!R142C1"
ActiveChart.SeriesCollection(2).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(3).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(4).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(5).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(6).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(7).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(8).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(9).XValues = "=Australia!R4C2:R4C8"
ActiveChart.Location Whe=xlLocationAsObject, Name:="Australia"
End Sub


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


"mohavv" wrote in message
...
Hi,

I want to create a macro to create a chart of a certain range which
the user has to select.

The layout and formatting of all ranges (the number of rows and
columns) is exactly the same.
The position of the range differs. X-axys labels are always the same.

When you turn on the recorder following code is part of the VB

ActiveChart.SetSourceData
Source:=Sheets("Australia").Range("A171:H179"), _
PlotBy:=xlRows

What I want to know is how you can make the selected range as a
variable to put in the "range" part.

And will I stumble upon more difficulties?

The complete code of the recorded macro is below

Cheers,

Harold

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 23/05/2008 by Harold Van Velzen
'

'
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData
Source:=Sheets("Australia").Range("A171:H179"), _
PlotBy:=xlRows
ActiveChart.SeriesCollection(1).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(1).Name = "=Australia!R142C1"
ActiveChart.SeriesCollection(2).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(3).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(4).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(5).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(6).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(7).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(8).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(9).XValues = "=Australia!R4C2:R4C8"
ActiveChart.Location Whe=xlLocationAsObject, Name:="Australia"
End Sub


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 68
Default chart macro
On May 24, 4:57*am, "Jon Peltier"
wrote:
I added a few lines in the top of the procedure.

Sub Macro2()
* * Dim rngChartData As Range

* * On Error Resume Next
* * Set rngChartData = Application.InputBox(Prompt:="Select Data Range",
Type:=8)
* * If rngChartData Is Nothing Then
* * * * ' user pressed Cancel
* * * * Exit Sub
* * End If
* * On Error Goto 0

* * Charts.Add
* * ActiveChart.ChartType = xlLineMarkers
* * ActiveChart.SetSourceData Source:=rngChartData, PlotBy:=xlRows
* * ActiveChart.SeriesCollection(1).XValues = "=Australia!R4C2:R4C8"
* * ActiveChart.SeriesCollection(1).Name = "=Australia!R142C1"
* * ActiveChart.SeriesCollection(2).XValues = "=Australia!R4C2:R4C8"
* * ActiveChart.SeriesCollection(3).XValues = "=Australia!R4C2:R4C8"
* * ActiveChart.SeriesCollection(4).XValues = "=Australia!R4C2:R4C8"
* * ActiveChart.SeriesCollection(5).XValues = "=Australia!R4C2:R4C8"
* * ActiveChart.SeriesCollection(6).XValues = "=Australia!R4C2:R4C8"
* * ActiveChart.SeriesCollection(7).XValues = "=Australia!R4C2:R4C8"
* * ActiveChart.SeriesCollection(8).XValues = "=Australia!R4C2:R4C8"
* * ActiveChart.SeriesCollection(9).XValues = "=Australia!R4C2:R4C8"
* * ActiveChart.Location Whe=xlLocationAsObject, Name:="Australia"
End Sub

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

"mohavv" wrote in message

...



Hi,

I want to create a macro to create a chart of a certain range which
the user has to select.

The layout and formatting of all ranges (the number of rows and
columns) is exactly the same.
The position of the range differs. X-axys labels are always the same.

When you turn on the recorder following code is part of the VB

ActiveChart.SetSourceData
Source:=Sheets("Australia").Range("A171:H179"), _
* * * *PlotBy:=xlRows

What I want to know is how you can make the selected range as a
variable to put in the "range" part.

And will I stumble upon more difficulties?

The complete code of the recorded macro is below

Cheers,

Harold

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 23/05/2008 by Harold Van Velzen
'

'
* *Charts.Add
* *ActiveChart.ChartType = xlLineMarkers
* *ActiveChart.SetSourceData
Source:=Sheets("Australia").Range("A171:H179"), _
* * * *PlotBy:=xlRows
* *ActiveChart.SeriesCollection(1).XValues = "=Australia!R4C2:R4C8"
* *ActiveChart.SeriesCollection(1).Name = "=Australia!R142C1"
* *ActiveChart.SeriesCollection(2).XValues = "=Australia!R4C2:R4C8"
* *ActiveChart.SeriesCollection(3).XValues = "=Australia!R4C2:R4C8"
* *ActiveChart.SeriesCollection(4).XValues = "=Australia!R4C2:R4C8"
* *ActiveChart.SeriesCollection(5).XValues = "=Australia!R4C2:R4C8"
* *ActiveChart.SeriesCollection(6).XValues = "=Australia!R4C2:R4C8"
* *ActiveChart.SeriesCollection(7).XValues = "=Australia!R4C2:R4C8"
* *ActiveChart.SeriesCollection(8).XValues = "=Australia!R4C2:R4C8"
* *ActiveChart.SeriesCollection(9).XValues = "=Australia!R4C2:R4C8"
* *ActiveChart.Location Whe=xlLocationAsObject, Name:="Australia"
End Sub- Hide quoted text -

- Show quoted text -

Almost there,

How can I make this macro sheet unindependant. I've got several
identical sheets per country and on a sheet several regions. It is
possible to make more than 1 chart per sheet.

Now I get an error on the following line:

ActiveChart.SeriesCollection(1).XValues = "=Australia!R4C2:R4C8"

because the sheetname and range are probably incorrect.

Thanks in advance.

Cheers,

Harold
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 6,582
Default chart macro
1. I don't know what you mean by "independent".

2. If the XValues are separate from the source data range, you need to
insert another Application.Inputbox that asks the user to identify which
range to use for XValues.

Sub Macro2()
Dim rngChartData As Range
Dim rngXValues As Range
Dim iSeries as long
Dim sSheetname as String

sSheetName = ActiveSheet.Name
On Error Resume Next
Set rngChartData = Application.InputBox(Prompt:="Select Data Range",
Type:=8)
If rngChartData Is Nothing Then
' user pressed Cancel
Exit Sub
End If
Set rngXValues = Application.InputBox(Prompt:="Select Category Labels",
Type:=8)
On Error Goto 0

Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=rngChartData, PlotBy:=xlRows
ActiveChart.SeriesCollection(1).Name = "=Australia!R142C1"
For iSeries = 1 To ActiveChart.SeriesCollection.Count
ActiveChart.SeriesCollection(iSeries).XValues = rngXValues
Next
ActiveChart.Location Whe=xlLocationAsObject, Name:=sSheetName
End Sub

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


"mohavv" wrote in message
...
On May 24, 4:57 am, "Jon Peltier"
wrote:
I added a few lines in the top of the procedure.

Sub Macro2()
Dim rngChartData As Range

On Error Resume Next
Set rngChartData = Application.InputBox(Prompt:="Select Data Range",
Type:=8)
If rngChartData Is Nothing Then
' user pressed Cancel
Exit Sub
End If
On Error Goto 0

Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=rngChartData, PlotBy:=xlRows
ActiveChart.SeriesCollection(1).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(1).Name = "=Australia!R142C1"
ActiveChart.SeriesCollection(2).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(3).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(4).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(5).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(6).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(7).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(8).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(9).XValues = "=Australia!R4C2:R4C8"
ActiveChart.Location Whe=xlLocationAsObject, Name:="Australia"
End Sub

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

"mohavv" wrote in message

...



Hi,

I want to create a macro to create a chart of a certain range which
the user has to select.

The layout and formatting of all ranges (the number of rows and
columns) is exactly the same.
The position of the range differs. X-axys labels are always the same.

When you turn on the recorder following code is part of the VB

ActiveChart.SetSourceData
Source:=Sheets("Australia").Range("A171:H179"), _
PlotBy:=xlRows

What I want to know is how you can make the selected range as a
variable to put in the "range" part.

And will I stumble upon more difficulties?

The complete code of the recorded macro is below

Cheers,

Harold

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 23/05/2008 by Harold Van Velzen
'

'
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData
Source:=Sheets("Australia").Range("A171:H179"), _
PlotBy:=xlRows
ActiveChart.SeriesCollection(1).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(1).Name = "=Australia!R142C1"
ActiveChart.SeriesCollection(2).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(3).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(4).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(5).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(6).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(7).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(8).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(9).XValues = "=Australia!R4C2:R4C8"
ActiveChart.Location Whe=xlLocationAsObject, Name:="Australia"
End Sub- Hide quoted text -

- Show quoted text -

Almost there,

How can I make this macro sheet unindependant. I've got several
identical sheets per country and on a sheet several regions. It is
possible to make more than 1 chart per sheet.

Now I get an error on the following line:

ActiveChart.SeriesCollection(1).XValues = "=Australia!R4C2:R4C8"

because the sheetname and range are probably incorrect.

Thanks in advance.

Cheers,

Harold


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 68
Default chart macro
On May 27, 1:11*am, "Jon Peltier"
wrote:
1. I don't know what you mean by "independent".

2. If the XValues are separate from the source data range, you need to
insert another Application.Inputbox that asks the user to identify which
range to use for XValues.

Sub Macro2()
* * Dim rngChartData As Range
* * Dim rngXValues As Range
* * Dim iSeries as long
* * Dim sSheetname as String

* * sSheetName = ActiveSheet.Name
* * On Error Resume Next
* * Set rngChartData = Application.InputBox(Prompt:="Select Data Range",
Type:=8)
* * If rngChartData Is Nothing Then
* * * * ' user pressed Cancel
* * * * Exit Sub
* * End If
* * Set rngXValues = Application.InputBox(Prompt:="Select Category Labels",
Type:=8)
* * On Error Goto 0

* * Charts.Add
* * ActiveChart.ChartType = xlLineMarkers
* * ActiveChart.SetSourceData Source:=rngChartData, PlotBy:=xlRows
* * ActiveChart.SeriesCollection(1).Name = "=Australia!R142C1"
* * For iSeries = 1 To ActiveChart.SeriesCollection.Count
* * * * ActiveChart.SeriesCollection(iSeries).XValues = rngXValues
* * Next
* * ActiveChart.Location Whe=xlLocationAsObject, Name:=sSheetName
End Sub

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

"mohavv" wrote in message

...
On May 24, 4:57 am, "Jon Peltier"
wrote:





I added a few lines in the top of the procedure.

Sub Macro2()
Dim rngChartData As Range

On Error Resume Next
Set rngChartData = Application.InputBox(Prompt:="Select Data Range",
Type:=8)
If rngChartData Is Nothing Then
' user pressed Cancel
Exit Sub
End If
On Error Goto 0

Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=rngChartData, PlotBy:=xlRows
ActiveChart.SeriesCollection(1).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(1).Name = "=Australia!R142C1"
ActiveChart.SeriesCollection(2).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(3).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(4).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(5).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(6).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(7).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(8).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(9).XValues = "=Australia!R4C2:R4C8"
ActiveChart.Location Whe=xlLocationAsObject, Name:="Australia"
End Sub

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

"mohavv" wrote in message

...

Hi,

I want to create a macro to create a chart of a certain range which
the user has to select.

The layout and formatting of all ranges (the number of rows and
columns) is exactly the same.
The position of the range differs. X-axys labels are always the same.

When you turn on the recorder following code is part of the VB

ActiveChart.SetSourceData
Source:=Sheets("Australia").Range("A171:H179"), _
PlotBy:=xlRows

What I want to know is how you can make the selected range as a
variable to put in the "range" part.

And will I stumble upon more difficulties?

The complete code of the recorded macro is below

Cheers,

Harold

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 23/05/2008 by Harold Van Velzen
'

'
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData
Source:=Sheets("Australia").Range("A171:H179"), _
PlotBy:=xlRows
ActiveChart.SeriesCollection(1).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(1).Name = "=Australia!R142C1"
ActiveChart.SeriesCollection(2).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(3).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(4).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(5).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(6).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(7).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(8).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(9).XValues = "=Australia!R4C2:R4C8"
ActiveChart.Location Whe=xlLocationAsObject, Name:="Australia"
End Sub- Hide quoted text -

- Show quoted text -

Almost there,

How can I make this macro sheet unindependant. I've got several
identical sheets per country and on a sheet several regions. It is
possible to make more than 1 chart per sheet.

Now I get an error on the following line:

* * ActiveChart.SeriesCollection(1).XValues = "=Australia!R4C2:R4C8"

because the sheetname and range are probably incorrect.

Thanks in advance.

Cheers,

Harold- Hide quoted text -

- Show quoted text -

What Am I doing wrong?

Can't get the Xvalues in the chart. Gives me an error on the line in
the loop for Iseries. When I remove the apostrophe in the line above
the loop it will give me an error there.

Sub Macrotest()
Dim rngChartData As Range
Dim rngXValues As Range
Dim iSeries As Long
Dim sSheetname As String


sSheetname = ActiveSheet.Name
On Error Resume Next

Set rngChartData = ActiveCell.CurrentRegion
tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, _
tbl.Columns.Count).Select

Range(ActiveCell.End(xlUp).End(xlToLeft).Offset(-29, 1),
ActiveCell.End(xlUp).End(xlToLeft).Offset(-29,
1).End(xlToRight)).Select
Set rngXValues = ActiveCell.CurrentRegion

On Error GoTo 0


Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=rngChartData, PlotBy:=xlRows
' ActiveChart.SeriesCollection(1).Name = "=Australia!R142C1"
For iSeries = 1 To ActiveChart.SeriesCollection.Count
ActiveChart.SeriesCollection(iSeries).XValues = rngXValues
Next
ActiveChart.Location Whe=xlLocationAsObject, Name:=sSheetname
End Sub
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 6,582
Default chart macro
Is the series plotted in the chart before the macro runs? If not, because
the data range has only blanks or error values, then VBA cannot access all
of the series properties. This affects line and XY charts. You can
temporarily change the chart type to column or area, then back to line or XY
after updating the properties.

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


"mohavv" wrote in message
...
On May 27, 1:11 am, "Jon Peltier"
wrote:
1. I don't know what you mean by "independent".

2. If the XValues are separate from the source data range, you need to
insert another Application.Inputbox that asks the user to identify which
range to use for XValues.

Sub Macro2()
Dim rngChartData As Range
Dim rngXValues As Range
Dim iSeries as long
Dim sSheetname as String

sSheetName = ActiveSheet.Name
On Error Resume Next
Set rngChartData = Application.InputBox(Prompt:="Select Data Range",
Type:=8)
If rngChartData Is Nothing Then
' user pressed Cancel
Exit Sub
End If
Set rngXValues = Application.InputBox(Prompt:="Select Category Labels",
Type:=8)
On Error Goto 0

Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=rngChartData, PlotBy:=xlRows
ActiveChart.SeriesCollection(1).Name = "=Australia!R142C1"
For iSeries = 1 To ActiveChart.SeriesCollection.Count
ActiveChart.SeriesCollection(iSeries).XValues = rngXValues
Next
ActiveChart.Location Whe=xlLocationAsObject, Name:=sSheetName
End Sub

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

"mohavv" wrote in message

...
On May 24, 4:57 am, "Jon Peltier"
wrote:





I added a few lines in the top of the procedure.

Sub Macro2()
Dim rngChartData As Range

On Error Resume Next
Set rngChartData = Application.InputBox(Prompt:="Select Data Range",
Type:=8)
If rngChartData Is Nothing Then
' user pressed Cancel
Exit Sub
End If
On Error Goto 0

Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=rngChartData, PlotBy:=xlRows
ActiveChart.SeriesCollection(1).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(1).Name = "=Australia!R142C1"
ActiveChart.SeriesCollection(2).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(3).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(4).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(5).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(6).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(7).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(8).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(9).XValues = "=Australia!R4C2:R4C8"
ActiveChart.Location Whe=xlLocationAsObject, Name:="Australia"
End Sub

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

"mohavv" wrote in message

...

Hi,

I want to create a macro to create a chart of a certain range which
the user has to select.

The layout and formatting of all ranges (the number of rows and
columns) is exactly the same.
The position of the range differs. X-axys labels are always the same.

When you turn on the recorder following code is part of the VB

ActiveChart.SetSourceData
Source:=Sheets("Australia").Range("A171:H179"), _
PlotBy:=xlRows

What I want to know is how you can make the selected range as a
variable to put in the "range" part.

And will I stumble upon more difficulties?

The complete code of the recorded macro is below

Cheers,

Harold

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 23/05/2008 by Harold Van Velzen
'

'
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData
Source:=Sheets("Australia").Range("A171:H179"), _
PlotBy:=xlRows
ActiveChart.SeriesCollection(1).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(1).Name = "=Australia!R142C1"
ActiveChart.SeriesCollection(2).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(3).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(4).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(5).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(6).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(7).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(8).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(9).XValues = "=Australia!R4C2:R4C8"
ActiveChart.Location Whe=xlLocationAsObject, Name:="Australia"
End Sub- Hide quoted text -

- Show quoted text -

Almost there,

How can I make this macro sheet unindependant. I've got several
identical sheets per country and on a sheet several regions. It is
possible to make more than 1 chart per sheet.

Now I get an error on the following line:

ActiveChart.SeriesCollection(1).XValues = "=Australia!R4C2:R4C8"

because the sheetname and range are probably incorrect.

Thanks in advance.

Cheers,

Harold- Hide quoted text -

- Show quoted text -

What Am I doing wrong?

Can't get the Xvalues in the chart. Gives me an error on the line in
the loop for Iseries. When I remove the apostrophe in the line above
the loop it will give me an error there.

Sub Macrotest()
Dim rngChartData As Range
Dim rngXValues As Range
Dim iSeries As Long
Dim sSheetname As String


sSheetname = ActiveSheet.Name
On Error Resume Next

Set rngChartData = ActiveCell.CurrentRegion
tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, _
tbl.Columns.Count).Select

Range(ActiveCell.End(xlUp).End(xlToLeft).Offset(-29, 1),
ActiveCell.End(xlUp).End(xlToLeft).Offset(-29,
1).End(xlToRight)).Select
Set rngXValues = ActiveCell.CurrentRegion

On Error GoTo 0


Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=rngChartData, PlotBy:=xlRows
' ActiveChart.SeriesCollection(1).Name = "=Australia!R142C1"
For iSeries = 1 To ActiveChart.SeriesCollection.Count
ActiveChart.SeriesCollection(iSeries).XValues = rngXValues
Next
ActiveChart.Location Whe=xlLocationAsObject, Name:=sSheetname
End Sub


Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 68
Default chart macro
On Jun 5, 7:46*am, "Jon Peltier"
wrote:
Is the series plotted in the chart before the macro runs? If not, because
the data range has only blanks or error values, then VBA cannot access all
of the series properties. This affects line and XY charts. You can
temporarily change the chart type to column or area, then back to line or XY
after updating the properties.

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

"mohavv" wrote in message

...
On May 27, 1:11 am, "Jon Peltier"
wrote:





1. I don't know what you mean by "independent".

2. If the XValues are separate from the source data range, you need to
insert another Application.Inputbox that asks the user to identify which
range to use for XValues.

Sub Macro2()
Dim rngChartData As Range
Dim rngXValues As Range
Dim iSeries as long
Dim sSheetname as String

sSheetName = ActiveSheet.Name
On Error Resume Next
Set rngChartData = Application.InputBox(Prompt:="Select Data Range",
Type:=8)
If rngChartData Is Nothing Then
' user pressed Cancel
Exit Sub
End If
Set rngXValues = Application.InputBox(Prompt:="Select Category Labels",
Type:=8)
On Error Goto 0

Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=rngChartData, PlotBy:=xlRows
ActiveChart.SeriesCollection(1).Name = "=Australia!R142C1"
For iSeries = 1 To ActiveChart.SeriesCollection.Count
ActiveChart.SeriesCollection(iSeries).XValues = rngXValues
Next
ActiveChart.Location Whe=xlLocationAsObject, Name:=sSheetName
End Sub

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

"mohavv" wrote in message

...
On May 24, 4:57 am, "Jon Peltier"
wrote:

I added a few lines in the top of the procedure.

Sub Macro2()
Dim rngChartData As Range

On Error Resume Next
Set rngChartData = Application.InputBox(Prompt:="Select Data Range",
Type:=8)
If rngChartData Is Nothing Then
' user pressed Cancel
Exit Sub
End If
On Error Goto 0

Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=rngChartData, PlotBy:=xlRows
ActiveChart.SeriesCollection(1).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(1).Name = "=Australia!R142C1"
ActiveChart.SeriesCollection(2).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(3).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(4).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(5).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(6).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(7).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(8).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(9).XValues = "=Australia!R4C2:R4C8"
ActiveChart.Location Whe=xlLocationAsObject, Name:="Australia"
End Sub

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

"mohavv" wrote in message

....

Hi,

I want to create a macro to create a chart of a certain range which
the user has to select.

The layout and formatting of all ranges (the number of rows and
columns) is exactly the same.
The position of the range differs. X-axys labels are always the same..

When you turn on the recorder following code is part of the VB

ActiveChart.SetSourceData
Source:=Sheets("Australia").Range("A171:H179"), _
PlotBy:=xlRows

What I want to know is how you can make the selected range as a
variable to put in the "range" part.

And will I stumble upon more difficulties?

The complete code of the recorded macro is below

Cheers,

Harold

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 23/05/2008 by Harold Van Velzen
'

'
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData
Source:=Sheets("Australia").Range("A171:H179"), _
PlotBy:=xlRows
ActiveChart.SeriesCollection(1).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(1).Name = "=Australia!R142C1"
ActiveChart.SeriesCollection(2).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(3).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(4).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(5).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(6).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(7).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(8).XValues = "=Australia!R4C2:R4C8"
ActiveChart.SeriesCollection(9).XValues = "=Australia!R4C2:R4C8"
ActiveChart.Location Whe=xlLocationAsObject, Name:="Australia"
End Sub- Hide quoted text -

- Show quoted text -

Almost there,

How can I make this macro sheet unindependant. I've got several
identical sheets per country and on a sheet several regions. It is
possible to make more than 1 chart per sheet.

Now I get an error on the following line:

ActiveChart.SeriesCollection(1).XValues = "=Australia!R4C2:R4C8"

because the sheetname and range are probably incorrect.

Thanks in advance.

Cheers,

Harold- Hide quoted text -

- Show quoted text -

What Am I doing wrong?

Can't get the Xvalues in the chart. Gives me an error on the line in
the loop for Iseries. When I remove the apostrophe in the line above
the loop it will give me an error there.

Sub Macrotest()
* * Dim rngChartData As Range
* * Dim rngXValues As Range
* * Dim iSeries As Long
* * Dim sSheetname As String

* * sSheetname = ActiveSheet.Name
* * On Error Resume Next

* * Set rngChartData = ActiveCell.CurrentRegion
* * tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, _
* * tbl.Columns.Count).Select

* * Range(ActiveCell.End(xlUp).End(xlToLeft).Offset(-29, 1),
ActiveCell.End(xlUp).End(xlToLeft).Offset(-29,
1).End(xlToRight)).Select
* * Set rngXValues = ActiveCell.CurrentRegion

* * On Error GoTo 0

* * Charts.Add
* * ActiveChart.ChartType = xlLineMarkers
* * ActiveChart.SetSourceData Source:=rngChartData, PlotBy:=xlRows
' * *ActiveChart.SeriesCollection(1).Name = "=Australia!R142C1"
* * For iSeries = 1 To ActiveChart.SeriesCollection.Count
* * * ActiveChart.SeriesCollection(iSeries).XValues = rngXValues
* * Next
* * ActiveChart.Location Whe=xlLocationAsObject, Name:=sSheetname
End Sub- Hide quoted text -

- Show quoted text -

The data is plotted correctly in the chart. when I disregard the loop
in the end, the chart shows exactly what is should show, except for
the labels. In the worksheet the correct labels are selected
(rngXValues).
It has some difficulty identifying the iseries, I guess.

Thanks,

Harold
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Chart Macro zephyr Charts and Charting in Excel 1 December 5th 07 04:37 PM
Get chart point value macro joecrabtree Charts and Charting in Excel 5 November 23rd 07 04:24 PM
making chart using macro roger Excel Discussion (Misc queries) 0 March 30th 07 04:46 PM
Chart using Macro Abhijeet Charts and Charting in Excel 0 August 1st 05 05:00 PM
Chart Macro James Charts and Charting in Excel 2 March 4th 05 04:29 PM


All times are GMT +1. The time now is 11:58 AM.

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

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement