Change pie chart data range in vba

[Ron2020]

Hi there I am a newbie at Excel,

1. I have a budget sheet as per the table below

2. I created a pie chart named "chart1" based on selections A1,A2 and B1, B2, and C1,C2. Just to show faculty, and comparison of two columns.

3. How do I update the pie chart data through vba? I know how to do the if statements etc. I have a variable that stores the faculty name. I used a match statement to locate the row for each faculty.

IF Faculty (A)
vba code to update chart series to A1,A2 and B1, B2, and C1,C2

IF Faculty (B)
vba code to update chart series to A1,A3 and B1, B3, and C1,C3
End
...etc..


Any help would be appreciated.

 

[Domenic]

The following assumptions are made...

1) The workbook running the code contains the source data, the chart, and the faculty of interest.

2) Sheet1 in that workbook contains the source data, the chart, and the faculty of interest. (Change the sheet name accordingly.)

3) Cell G2 in that sheet contains the faculty of interest, for example "B". (Change the cell reference accordingly.)

Try the following code, making any necessary changes where indicated...

Option Explicit

Sub UpdateChart()

    Dim sourceWorksheet As Worksheet
    Set sourceWorksheet = ThisWorkbook.Worksheets("Sheet1") 'change the sheet name accordingly
    
    Dim theFaculty As String
    theFaculty = sourceWorksheet.Range("G2").Value 'change the cell reference accordingly
    
    Dim theChart As Chart
    Set theChart = sourceWorksheet.ChartObjects("Chart 1").Chart 'change the chart name accordingly
    
    'Get the source range for the chart data
    Dim sourceRange As Range
    With sourceWorksheet
        Set sourceRange = .Range("A1:C" & .Cells(.Rows.Count, "A").End(xlUp).Row)
    End With
    
    'Find the the row matching the faculty
    Dim matchVal As Variant
    matchVal = Application.Match(theFaculty, sourceRange.Columns(1), 0)
    
    'If a match is found, update the chart
    If Not IsError(matchVal) Then
        'Match is found, so update chart
        With sourceRange
            theChart.SetSourceData Source:=Union(.Rows(1), .Rows(matchVal))
        End With
    Else
        'No match found, notify user
        MsgBox theFaculty & " not found!", vbExclamation
    End If
    
    'Clear from memory
    Set theChart = Nothing
    Set sourceRange = Nothing
    Set sourceWorksheet = Nothing

End Sub

Hope this helps!

 

[Ron2020]

Thank you.