Create Chart Using Dynamic Range (Q146055)

[thunderkid]

I am attempting to use Q146055 to build chart using a macro. I have included sample data and code below. I added Dim statement for myrange and mysheetname. I am getting the following error

Runtime error '1004'
Method 'Range of object' _Global failed

Debug jumps to Range ("c4").Select

I am using Microsoft Excel 2002 (10.4302.4219) SP-2 and
Microsoft VB 6.3

I have tried to generate code for building chart by recording macro, but have been unable to incorporate into subroutine below to get it to work.


,Region 1,Region 2,Region 3
Jan,10,80,15
Feb,20,70,25
Mar,30,60,35
Apr,40,50,45



Option Explicit

Sub Createchart()
Dim myrange, mysheetname As Variant
' Select the cell in the upper-left corner of the chart.
Range("c4").Select
' Select the current range of data. This line of code assumes that
' the current region of cells is contiguous - without empty rows
' or columns
Selection.CurrentRegion.Select

' Assign the address of the selected range of cells to a variable
myrange = Selection.Address

' Assign the name of the active sheet to a variable is
' used in order to allow a chart to be created on a separate chart
' sheet.
mysheetname = ActiveSheet.Name

' Add a chart to the active sheet.
' ActiveSheet.chartObjects.Add(125.25, 60, 301.5, 155.25).Select

'To create a chart on a separate chart sheet, remark out the
'previous line, and substitute the next line for te one above

'Charts.Add

Application.CutCopyMode = False

'This line can best be written by recording a macro, and
'modifying the code generated by the Microsoft Excel Macro
'recorder.

ActiveChart.ChartWizard _
Source:=Sheets(mysheetname).Range(myrange), _
Gallery:=xlLine, Format:=4, PlotBy:xlRows, _
Categorylabels:=1, SeriesLabels:=1, HasLegend:=1, _
Title:="", CategoryTitle:="", _
Valuetitle:="",, ExtraTitle:=""
End Sub

 

[PHV]

Range("c4").Select is equivalent to ActiveSheet.Range("c4").Select
So if the active sheet is not a worksheet you raise an error.
As a good habbit, try to always qualify your ranges.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[thunderkid]

PHV, thanks for the comment

I changed the Range("c4').Select with the Activesheet.Range("c4").Select. Now the error msg is

Compile error
Syntax error

The "ActiveChart.ChartWizard _ section of the code is highlight

thunderkid

 

[PHV]

Please reread my previous post:
So if the active sheet [highlight]is not a worksheet[/highlight] you raise an error

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SkipVought]

tk,

When you ADD your chart, set a chart object

Set MyChart = Charts.Add

MyChart.ChartWizard _
    Source:=Sheets(mysheetname).Range(myrange), _
    Gallery:=xlLine, Format:=4, PlotBy:xlRows, _
    Categorylabels:=1, SeriesLabels:=1, HasLegend:=1, _
    Title:="", CategoryTitle:="", _
    Valuetitle:="",, ExtraTitle:=""
End Sub

With MyChart

... other code for chart

End With

Set MyChart = Nothing

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[thunderkid]

I managed to get macro to work with the following. Thanks for the help.

Charts.Add
With ActiveChart
.ChartType = xlColumnClustered
.SetSourceData Source:=Sheets(sh1).Range("E10:F14"), PlotBy _
:=xlColumns
.Location Where:=xlLocationAsNewSheet, Name:="result"
.HasTitle = True
.ChartTitle.Characters.Text = "Regional Results"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Qties"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Months"
.HasLegend = False

thunderkid