Add Chart Via VBA

[BrianExcel]

I am trying to add a chart via VBA. The workbook I am working in is an add-in, but the data sources for the chart exist in the workbook and work properly. If I add the chart manually via Insert > Chart > etc., it works fine.

However, when I try to add the chart, I continually get a "Compile Error: Invalid or unqualified reference" error.

Here is the code I am using..

Sub Apply_Graph_Formatting(controlo As IRibbonControl)
Dim VenueColumn As Long
Dim PercentColumn As Long

VenueColumn = .Cells(Rows.Count, "A").End(xlUp).Row
PercentColumn = .Cells(Rows.Count, "D").End(xlUp).Row

    ActiveWorkbook.Activeworksheet.Shapes.AddChart(201, xlColumnClustered).Select
    ActiveChart.ApplyChartTemplate ("C:\Users\brian.david\AppData\Roaming\Microsoft\Templates\Charts\OpsReviewHistogramTemplate.crtx")
    ActiveChart.SetSourceData Source:=Range(VenueColumn, PercentColumn)

End Sub

Any thoughts on why this won't work?

 

[BrianExcel]

Update:

I got the chart to add (had some spelling errors), but now the last line above, the data source, is giving me a "Run-time error '1004':

Method 'Range' of object '_global' failed

Working on this, but could use some help...

 

[BrianExcel]

One final correction: this is the code I am using, functional until the last row (set source data)...

Sub Apply_Graph_Formatting(control As IRibbonControl)
Dim VenueColumn As Long
Dim PercentColumn As Long

VenueColumn = ActiveWorkbook.Sheets("7DayEdits").Cells(Rows.Count, "A").End(xlUp).Row
PercentColumn = ActiveWorkbook.Sheets("7DayEdits").Cells(Rows.Count, "D").End(xlUp).Row

    ActiveWorkbook.ActiveSheet.Shapes.AddChart(201, xlColumnClustered).Select
    ActiveChart.ApplyChartTemplate ("C:\Users\brian.david\AppData\Roaming\Microsoft\Templates\Charts\OpsReviewHistogramTemplate.crtx")
    ActiveChart.SetSourceData Source:=Range(VenueColumn, PercentColumn)
    
End Sub

 

[NdNoviceHlp]

You can trial this. HTH. Dave

Dim ChartRange As Range
VenueColumn = ActiveWorkbook.Sheets("7DayEdits").Cells(Rows.Count, "A").End(xlUp).Row
PercentColumn = ActiveWorkbook.Sheets("7DayEdits").Cells(Rows.Count, "D").End(xlUp).Row
Set ChartRange = ActiveWorkbook.Sheets("7DayEdits").Range(VenueColumn, PercentColumn)
ActiveWorkbook.ActiveSheet.Shapes.AddChart(201, xlColumnClustered).Select
ActiveChart.ApplyChartTemplate ("C:\Users\brian.david\AppData\Roaming\Microsoft\Templates\Charts\OpsReviewHistogramTemplate.crtx")
ActiveChart.SetSourceData Source:=ChartRange, PlotBy:=xlColumns

 

[BrianExcel]

Thanks ND -

Looks like because my data is in a table format I was able to select the columns by using the following:

Range("Sum7EditsTable[[#All],[Venue]]").Select
ActiveWorkbook.Names.Add Name:="VenueColumn", RefersToR1C1:="=Sum7EditsTable[[#All],[Venue]]"

 

[JAWS1]

I had something similar I had a 35,000 list of names and wanted a to search for an individual .I have office 365 on the customize ribbon under command buttons not in the ribbon there is a FORM
command move this to quick access dont know whether it is in earlier versions. I have 8 columns in a header I changed the spread sheet to a table and clicked on the form button in quick access bingo it works a treat i can delete, insert new and search under criteria. NO VBA needed when i want to do anything with the form i just click on the form in the quick access bar.