PDA

View Full Version : Chart reference for Macro



nitzbar
09-15-2008, 11:59 AM
Hi ! I have a macro that I would like to run on different sheets in a workbook ( They all have the same data for different dates).I am running into a syntax error issue when the macro references the sheet while creating a chart . Is there anyway I can substitute 'ActiveSheet' where it refers to the chart name and thus have it run on every single sheet from the same module ?

The syntax issue comes here :

'Set data source range.
With Sheets("Trades")

Bob Phillips
09-15-2008, 02:09 PM
With Activesheet

If TypeName(Activesheet) <> "Chart" Then

...

nitzbar
09-16-2008, 06:57 AM
Hmm.. I tried that but am getting an 'end with' without 'with error.... shouldn't the end with statement remain unchanged ???

Bob Phillips
09-16-2008, 07:26 AM
Show all the code then, probably a missing End If.

nitzbar
09-16-2008, 07:33 AM
Set chtChart = ActiveSheet.ChartObjects(1).Chart
With chtChart
.ChartType = xlXYScatter
'Set data source range.
With Sheets("08-18")
Set rngData = .Range("J1", .Range("J1").End(xlDown)).Resize(, 3)
End With
.SetSourceData Source:=rngData, PlotBy:=xlColumns
.HasTitle = True
.ChartTitle.Text = "Point and Figure"

Bob Phillips
09-16-2008, 07:37 AM
Where is the IF test that you said that you tried?

nitzbar
09-16-2008, 07:42 AM
That was the original code

nitzbar
09-16-2008, 07:44 AM
With ActiveSheet
If TypeName(ActiveSheet) <> "Chart" Then
Set rngData = .Range("J1", .Range("J1").End(xlDown)).Resize(, 3)
End With
.SetSourceData Source:=rngData, PlotBy:=xlColumns
.HasTitle = True
.ChartTitle.Text = "Point and Figure"

Bob Phillips
09-16-2008, 08:17 AM
Therein lies your problem.

You close out an If with and Enf If, not an End With.