Recorded excel macro doesnt work :( I need a help ASAP

[VinoBob]

Hi all !

I recorded inserting a chart, modified it a bit to suit the code i am using it in, and everythings fine, only one method fails, and i dont get it how, because it shouldnt

[VBA] Dim row As Integer
Dim col As Integer
Dim rng As Range

row = Worksheets(1).Range("A1").CurrentRegion.Rows.Count
col = Worksheets(1).Range("A1").CurrentRegion.Columns.Count
Set rng = Worksheets(1).Range(Cells(1, col), Cells(row, col))

Charts.Add
ActiveChart.ChartType = xl3DPie
ActiveChart.SetSourceData Source:=rng, PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).XValues = "=Work1!R1C1:R" & Trim(str(row)) & "C1"
ActiveChart.Location Where:=xlLocationAsObject, Name:="Work1"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Title"
End With
ActiveChart.HasLegend = False
ActiveChart.SeriesCollection(1).DataLabels.Select '<< this is the one
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Normal"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 41
.Background = xlTransparent
End With[/VBA]

I get a 1004 runtime error code, and it says that the Select method of the series is wrong.

Whats the solution?

Thanks in advance!

[Bob Phillips]

[vba]

Dim row As Integer
Dim col As Integer
Dim rng As Range

row = Worksheets(1).Range("A1").CurrentRegion.Rows.Count
col = Worksheets(1).Range("A1").CurrentRegion.Columns.Count
Set rng = Worksheets(1).Range(Cells(1, col), Cells(row, col))

Charts.Add
With ActiveChart

.ChartType = xl3DPie
.SetSourceData Source:=rng, PlotBy:=xlColumns
.SeriesCollection(1).XValues = "=Work1!R1C1:R" & Trim(Str(row)) & "C1"
.Location Where:=xlLocationAsObject, Name:="Work1"
End With
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Title"
.HasLegend = False
.ApplyDataLabels AutoText:=True, HasLeaderLines:=True, ShowValue:=True
With .SeriesCollection(1).DataLabels
' .AutoScaleFont = True
With .Font
.Name = "Arial"
.FontStyle = "Normal"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 41
.Background = xlTransparent
End With
End With
End With
[/vba]

[rbrhodes]

...and the difference is?

How come two 'With activechart/end with?

[Bob Phillips]

I added some code to apply datalabels, so that the formatting of same could actually work.

The two With activechart/end with are necessary because the chart gets built on a chart sheet, and then gets moved relocated, so the with loses its context.

[rbrhodes]

Thanks