VBA to loop through columns and add series to Radar graph

[Kaps_mr2]

Hello,

I have data in columns in each sheet of a workbook.

I would like to set up a xlradar chart on each sheet - with each radar chart having multiple series. The data series are taken from data columns on the respective sheet. Here is my code - I can't seem to get it working:-

Public Sub set_up_graphs()


Dim sh As Long
Dim sheet_name As String
Dim number_of_questions_in_this_category As Long
Dim chart_output_range As Range
Dim series_data_range As Range
Dim series_legend As String
Dim c As Long
Dim r As Long


Set chart_output_range = Range("b2:o16")


For sh = 1 To no_of_categories
    sheet_name = automater.Sheets(1).Range("g16").Offset(sh, 0)
    number_of_questions_in_this_category = automater.Sheets(1).Range("g16").Offset(sh, 2)
    
    
     With output_workbook.Sheets(sheet_name).ChartObjects.Add _
        (Left:=chart_output_range.Left, Width:=chart_output_range.Width, Top:=chart_output_range.Top, Height:=chart_output_range.Height)
        
        .Chart.ChartType = xlRadar
        .Chart.HasTitle = True
        
        .Chart.ChartTitle.Text = sheet_name
        .Chart.ChartTitle.Font.Size = 12
    End With
        
        
        For c = 1 To number_of_questions_in_this_category
        
         series_legend = output_workbook.Sheets(sheet_name).Range("o2").Offset(0, c).Value
                  
         Set series_data_range = output_workbook.Worksheets(sheet_name).Range("o1").Offset(last_row_of_source_data + 2, c).Resize(5, 1)
        
         With ActiveChart.SeriesCollection.NewSeries
         .XValues = series_data_range
         .Name = series_legend
        
         End With
    
    
        Next c
    
    Next sh
    


End Sub

can some one explain why pls ? thank you
,

 

[RoryA]

What is actually happening? I don't see where you set last_row_of_source_data for starters.

 

[Kaps_mr2]

What is actually happening? I don't see where you set last_row_of_source_data for starters.

i get "object variable or with variable not set error 91" on :-

With ActiveChart.SeriesCollection.NewSeries

last_row_of_source_data is set as a public variable. thank you

 

[RoryA]

Use a chart variable:

dim ch as chart
     set ch = output_workbook.Sheets(sheet_name).ChartObjects.Add _
        (Left:=chart_output_range.Left, Width:=chart_output_range.Width, Top:=chart_output_range.Top, Height:=chart_output_range.Height).Chart
    with ch
        .ChartType = xlRadar
        .HasTitle = True
        .ChartTitle.Text = sheet_name
        .ChartTitle.Font.Size = 12
    End With

        For c = 1 To number_of_questions_in_this_category
        
         series_legend = output_workbook.Sheets(sheet_name).Range("o2").Offset(0, c).Value
                  
         Set series_data_range = output_workbook.Worksheets(sheet_name).Range("o1").Offset(last_row_of_source_data + 2, c).Resize(5, 1)
        
         With ch.SeriesCollection.NewSeries
         .XValues = series_data_range
         .Name = series_legend
        
         End With
    
    
        Next c
    
    Next sh

 

[Kaps_mr2]

Use a chart variable:

dim ch as chart
     set ch = output_workbook.Sheets(sheet_name).ChartObjects.Add _
        (Left:=chart_output_range.Left, Width:=chart_output_range.Width, Top:=chart_output_range.Top, Height:=chart_output_range.Height).Chart
    with ch
        .ChartType = xlRadar
        .HasTitle = True
        .ChartTitle.Text = sheet_name
        .ChartTitle.Font.Size = 12
    End With

        For c = 1 To number_of_questions_in_this_category
       
         series_legend = output_workbook.Sheets(sheet_name).Range("o2").Offset(0, c).Value
                 
         Set series_data_range = output_workbook.Worksheets(sheet_name).Range("o1").Offset(last_row_of_source_data + 2, c).Resize(5, 1)
       
         With ch.SeriesCollection.NewSeries
         .XValues = series_data_range
         .Name = series_legend
       
         End With
   
   
        Next c
   
    Next sh

Many thanks.

 

[RoryA]

Please do not mark a post that doesn't contain a solution.

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.