Charts.Add method causing error

I needed a macro to add a chart with a very specific format. I created a macro that usually works, but I am getting an error that I don't understand.

Here is a snippet from the macro:

Set chartRange = Selection
Charts.Add ' This line results in a "Type Mismatch Error" the first time it is run
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=chartRange, PlotBy:=xlColumns
'...

When I open a new sheet, and put some data in and run the macro, I get a type mismatch error for the Charts.Add line. When I abort the macro and try the very same thing again it works fine for as long as I have the workbook open. Then, if I close the workbook, open a new one and run the macro I get the same error, but only the first time the macro is run. What is causing this to happen, and how do I fix it This error doesn't make any sense to me, what exactly is being mismatched

Thanks for any assistance,
Jeff

Answer this question

Charts.Add method causing error

Nadeem_IQBAL_NL
I made the change, but I'm still getting the 'type mismatch error'

Jeff
Sanjiro

Sorry, that was kind of a weak answer.

Is the workbook protected This will prevent adding a sheet. Is it shared Shared workbooks do not support adding charts (among their other missing features).

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

Your code isn't fully referenced. Try

ActiveWorkbook.Charts.Add

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______
Mike Green
No, I don't think that is the problem. I am only getting this error the first time that I run the macro on a new workbook. After the error is generated, I end execution, delete the half-finished chart (the chart gets added with my data, but no formatting) and try again. The second and any subsequent attempts to run the macro succeed with the correct formatting. The actual 'type mismatch error' is apparently coming from the Charts.Add method, which is the line that the debugger jumps to when the macro encounters the error. Here is the complete macro:

'=================
Sub CalibrationCurve()
On Error GoTo catch
Set chartRange = Selection
ActiveWorkbook.Charts.Add ' This is the line causing the type mismatch error
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=chartRange, PlotBy:= _
xlColumns

ActiveChart.Location Where:=xlLocationAsNewSheet
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
ActiveChart.HasLegend = False
ActiveChart.PlotArea.Select
Selection.ClearFormats
ActiveChart.SeriesCollection(1).Select
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Trendlines.Add(Type:=xlLinear, Forward:=0, _
Backward:=0, DisplayEquation:=True, DisplayRSquared:=True).Select
ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Select
Selection.Left = 500
Selection.Top = 200
Exit Sub
catch:
MsgBox "Error creating graph", vbExclamation

End Sub
'=======================

I am not sure what is being 'mismatched' in the code, and I can't seem to figure out why this only has a problem the first time I run it.

Thanks for the help,
Jeff
Charts.Add method causing error

Answer this question