CODEWITHSUNDEEP
vbaexceldynamiccharts
Braitaq
Braitaq

Reputation: 29

VBA code to create Piechart with dymanic datasource

As a final step in a sub() in VBA i have tried to write a code to create a pie chart over the subs resulting data, however I'm having trouble defining the data-source as the column location of the data, and number of rows vary. Here is the current code.

'Create Pie chart

Charts.Add
ActiveChart.ChartType = xlPie
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range(Cells(2, X + 2), Cells(1 + Y, X + 2)), PlotBy:= _
xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="Market Simulator"
ActiveChart.HasTitle = False

X and Y is populated earlier in the code, X tells how many columns the chart source data have been shifted to the right, and Y tells how many rows long the datasource is (excluding header).

When i try and run the sub I get an "method cells of object _global failed" Error, from searching online it seems this is triggered because the data source location isn't specific enough, but I'm not sure how it can be more specific as both sheet and cells are defined. I tried also adding workbook but it didn't help.

Upvotes: 0

Views: 2080

Answers (1)

SJR
SJR

Reputation: 23081

Probably common mistake in that you are not fully qualifying all Range/Cells references with a sheet. When you add the chart sheet, Sheet1 is no longer active, and the code is then trying to access cells on a chart sheet which is never going to end happily. Note the dots in front of both Cells below.

Charts.Add
ActiveChart.ChartType = xlPie
With Sheets("Sheet1")
    ActiveChart.SetSourceData Source:=.Range(.Cells(2, x + 2), .Cells(1 + y, x + 2)), PlotBy:= _
xlColumns
End With
ActiveChart.Location Where:=xlLocationAsObject, Name:="Market Simulator"
ActiveChart.HasTitle = False

Upvotes: 1

Related Questions