How to correctly automate Radar Chart creation process in VBA?

[caesarion]

Hi everybody!

So I'm a total noob and have never used VBA before but I'm trying to automate the creation of 'radar charts' for work. I have a table that looks like this:

And I'm trying to automate the process of creating radar charts that look like this:

So far I've written this:

Sub AddCharts()

Dim i As Integer 'Rows
Dim j As Integer 'Columns

i = Cells(Rows.Count, 1).End(xlUp).Row

For j = 2 To i
    With ActiveSheet.Shapes.AddChart.Chart
    .ChartType = xlRadar
    .SeriesCollection.NewSeries
        With .SeriesCollection(1)
        .Name = "=" & ActiveSheet.Name & "!" & _
        Cells(j, 1).Address
        .Values = "=" & ActiveSheet.Name & "!" & _
        Range(Cells(2, 2), Cells(2, 11)).Address
        End With
    End With
Next j


End Sub

And I've gotten here:

A few things:
1. I've been trying to figure out how to change the x axis labels so that it will display the "skill" instead of a number, but can't figure out how to do it!
2. All the skills are out of 5, and some people don't have a 5 -- but how do I standardize it so 5 appears on all charts?
3. How do I get rid of the "person 5" description and line?

Any help would be appreciated. Thank you so much!

 

[Domenic]

Hi, and welcome to the Board!

The following code assumes that the workbook running the code contains the source data, and that Sheet1 contains the source data. So change the sheet name accordingly. Note that the charts are placed on your worksheet with one following the another.

*EDIT*

The code has been amended to include the line formatting for the value axis. Change the color as desired.

Option Explicit

Sub AddCharts()

    Dim sourceWorksheet As Worksheet
    Set sourceWorksheet = ThisWorkbook.Worksheets("Sheet1") 'change the sheet name accordingly

    With sourceWorksheet
     
        Dim lastRow As Long
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
     
        Dim lastColumn As Long
        lastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
     
    End With
 
    'left position for all charts
    Dim leftPos As Long
    leftPos = 30
 
    'starting top position for charts
    Dim topPos As Long
    topPos = 15
 
    Dim i As Long
    For i = 2 To lastRow
 
        Dim newChart As Chart
        Set newChart = sourceWorksheet.Shapes.AddChart2(XlChartType:=xlRadar, Left:=leftPos, Top:=topPos).Chart
 
        'remove extra series, if any
        With newChart
            Do While .SeriesCollection.Count > 0
                .SeriesCollection(1).Delete
            Loop
        End With
     
        Dim newSeries As Series
        Set newSeries = newChart.SeriesCollection.newSeries
     
        With sourceWorksheet
            newSeries.Name = "=" & .Cells(i, 1).Address(external:=True)
            newSeries.XValues = .Range(.Cells(1, 2), .Cells(1, lastColumn))
            newSeries.Values = .Range(.Cells(i, 2), .Cells(i, lastColumn))
        End With
     
        
        With newChart
            With .Axes(Type:=xlValue)
                .MinimumScale = 0
                .MaximumScale = 5
                With .Format.Line
                    .Visible = msoTrue
                    .ForeColor.RGB = RGB(0, 0, 0) 'black
                End With
            End With
        End With
     
        With newChart
            'If .HasTitle Then .ChartTitle.Delete
            If .HasLegend Then .Legend.Delete
        End With
 
        topPos = topPos + newChart.Parent.Height + 25 'top position of next chart
     
    Next i
 
End Sub

Hope this helps!

 

[caesarion]

Hey, thanks so much for the help, I super appreciate it! I plugged the code in, and I got this:

How would I include the radial/spoke lines? I assumed it would be something like With .Axes & format/line, but I can't seem to figure which axis object property it is!

Thanks so much again.

 

[Domenic]

See my edited post . . .

 

[caesarion]

Works perfectly -- thank you so much!

 

[Domenic]

You're very welcome, I'm glad I could help.

Cheers!