Change pie chart data range in vba

Ron2020

New Member
Joined
Mar 3, 2020
Messages
2
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi there I am a newbie at Excel,

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


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.
1583224536924.png


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.
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,402
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...

VBA Code:
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,075
Messages
5,570,051
Members
412,309
Latest member
essobsan
Top