CODEWITHSUNDEEP
vbaexcel
Jenny
Jenny

Reputation: 451

automation error while generating the chart

I am trying to generate a chart out of a table.

The table is in my sheet "Test" and I want my chart in the sheet "status".

I am using a code in which I am getting an automation error in the line

.SetSourceData Source:=rng

I am not getting the error everytime. for every 30 or more iterations, I get this error.

Could anyone help, how I can rectify this

Sub chart()
Dim rng As Range
Dim cht As Object
Dim Ws As Worksheet
Set Ws = Sheets("Test")
Set rng = Ws.Range("G1:J2")
Set sh = ActiveSheet.ChartObjects.Add(Left:=400, _
    Width:=390, _
    Top:=100, _
    Height:=250)
sh.Select
Set cht = ActiveChart
With cht
.SetSourceData Source:=rng
.ChartType = xlColumnClustered
 ActiveChart.FullSeriesCollection(1).Points(1).Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
 ActiveChart.FullSeriesCollection(1).Points(2).Format.Fill.ForeColor.RGB = RGB(0, 255, 0)
  ActiveChart.FullSeriesCollection(1).Points(3).Format.Fill.ForeColor.RGB = RGB(0, 0, 255)
  ActiveChart.FullSeriesCollection(1).Points(4).Format.Fill.ForeColor.RGB = RGB(80, 100, 10)
'cht.Axes(xlSecondary).TickLabels.NumberFormat = "0.0%"
End With
'cht.SeriesCollection(1).name = ""
cht.SeriesCollection(1).HasDataLabels = True
cht.HasTitle = True
cht.ChartTitle.Text = "Status"
End Sub

Upvotes: 0

Views: 626

Answers (1)

Rafael Matos
Rafael Matos

Reputation: 258

Give this a try:

Sub chart()
    Dim rng As Range
    Dim cht As Chart
    Dim Ws As Worksheet

    Set Ws = ThisWorkbook.Sheets("Test")
    Set rng = Ws.Range("G1:J2")
    Set sh = ThisWorkbook.Sheets("status")

    sh.Shapes.AddChart(Left:=400, Top:=100, Width:=390, Height:=250).Select
    Set cht = ActiveChart

    With cht
        .SetSourceData Source:=rng
        .ChartType = xlColumnClustered
        .SeriesCollection(1).Points(1).Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
        .SeriesCollection(1).Points(2).Format.Fill.ForeColor.RGB = RGB(0, 255, 0)
        .SeriesCollection(1).Points(3).Format.Fill.ForeColor.RGB = RGB(0, 0, 255)
        .SeriesCollection(1).Points(4).Format.Fill.ForeColor.RGB = RGB(80, 100, 10)
        .ChartTitle.Text = "Status"
    End With
End Sub

Upvotes: 2

Related Questions