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