Create Chart Using Dynamic Range (Q1460550

[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 bu 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

 

[johnwm]

As your question is specifically VBA you may do better in the VBA forum. You can find it in the 'Related Forums' panel at the right of the page (just belows 'Forum MVPs'). It's forum708

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[thunderkid]

thanks johnwm. i have moved thread to VBA forum
thunderkid