VBA Macro erroring out with example code / document

Hi,

Any chance that

GroupRange = Range(GetTopRange(Range("C3:C" & LastRow), Location, 10)).Offset(0, 6)

should actually say

GroupRange = Range(GetTopRange(Range("C4:C" & LastRow), Location, 10)).Offset(0, 6)

???

Stepping through the code, the first location is "London", but the first row that is being looked at is the header row (row 3). And changing the 3 to a 4 does get the code past this point.

Some other changes you will need to make:
1. In the "CreateCharts" subroutine, you will need to change GroupRange = to Set GroupRange =, and similarly for ValueRange = on the next line.
2. In the "AddChart" subroutine, you will have to add your series manually. The reason for this is because when using SetSourceData in your case, Excel is unable to recognise chartable data in the range that you have given it, so it creates the chart without an initial series. This also means the chart doesn't have any other features either... no plot area, no legend, and no placeholder for a title. So the code errors out when you try to set the title, because the title placeholder doesn't exist. You can change this by manually adding each series in VBA rather than trying to set the source data.

I hope this has helped

Thanks guys, the fixed code is:

Sub CreateCharts()

    Dim Location As String
    Dim GroupRange As Range, ValueRange As Range
    Dim LastRow As Long, LastRowAC As Long

    LastRow = Range("C65536").End(xlUp).Row
    Range("AC3:AC" & LastRow) = Range("C3:C" & LastRow).Value
    Range("$AC$3:$AC$" & LastRow).RemoveDuplicates Columns:=1, Header:=xlYes
    LastRowAC = Range("AC65536").End(xlUp).Row
    
    For i = 4 To LastRowAC
        Sheets("MainSheet").Activate
        Location = Range("AC" & i).Value
        Set GroupRange = Range(GetTopRange(Range("C4:C" & LastRow), Location, 10)).Offset(0, 6)
        Set ValueRange = GroupRange.Offset(0, 11)
        
        AddChart GroupRange, ValueRange, Location
    Next i
    
End Sub


Sub AddChart(rLabel As Range, rValues As Range, sTitle As String)

    Dim Cht As Chart
    Set Cht = Charts.Add
    
    With Cht
        .Name = sTitle
        .ChartType = xlPie
        .SetSourceData Source:=Union(rLabel, rValues)
        .HasTitle = True
        .ChartTitle.Characters.Text = sTitle
    End With
    
End Sub


Function GetTopRange(Rng As Range, StrLine As String, NumCount As Long) As String

    Application.Volatile
    Dim Cell As Range, URng As Range
    
    For Each Cell In Rng.SpecialCells(xlCellTypeConstants)
        If Cell.Value = StrLine Then
            If URng Is Nothing Then
                Set URng = Cell
            Else
                Set URng = Union(URng, Cell)
            End If
            If URng.Cells.Count = NumCount Then
                Exit For
            End If
        End If
    Next Cell
    
    GetTopRange = URng.Address
    
End Function

No worries.

Please don't forget to mark this thread as solved and please click on the * next to my post to say thanks