chart macro

M

mohavv

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 Where:=xlLocationAsObject, Name:="Australia"
End Sub
 
J

Jon Peltier

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 Where:=xlLocationAsObject, Name:="Australia"
End Sub


- Jon
 
M

mohavv

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 Where:=xlLocationAsObject, Name:="Australia"
End Sub

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














- Show quoted text -
Click to expand...

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
 
J

Jon Peltier

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 Where:=xlLocationAsObject, Name:=sSheetName
End Sub

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


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 Where:=xlLocationAsObject, Name:="Australia"
End Sub

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














- Show quoted text -
Click to expand...

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
 
M

mohavv

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 CategoryLabels",
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 Where:=xlLocationAsObject, Name:=sSheetName
End Sub

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










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 -
Click to expand...

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 Where:=xlLocationAsObject, Name:=sSheetname
End Sub
 
J

Jon Peltier

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
_______


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 Where:=xlLocationAsObject, Name:=sSheetName
End Sub

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










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 -
Click to expand...

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 Where:=xlLocationAsObject, Name:=sSheetname
End Sub
 
M

mohavv

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
_______


















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 Where:=xlLocationAsObject, Name:=sSheetname
End Sub- Hide quoted text -

- Show quoted text -
Click to expand...

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to change the row number in macro code? 1
Floating Chart? 2
Run Time error 1004 1
Run time error 1004! 0
variable in a chart macro 1
Automatize chart building 1
Unable to automate excel chart from access 1
VB To Re-Name Worksheet Name 3

Top