Updating a VBA code so it runs for all charts in a worksheet insted of a selected one

Tshelky

New Member
Joined
Aug 5, 2020
Messages
5
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hello,

I need to update a range set of hunderds of charts each time I run a macro. I have already found a code on the internet that works as a perfect base for this pourpose. The only problem with it is that it updates the data only for a selected chart insted of all charts in the worksheet. Obviously, I cant do VBA nor any sort of coding whatsoever; could anyone help me out?

VBA Code:
Sub ChangeChartRange()
    Dim i As Integer, r As Integer, n As Integer, p1 As Integer, p2 As Integer, p3 As Integer
    Dim rng As Range
    Dim ax As Range

    'Cycles through each series
    For n = 1 To ActiveChart.SeriesCollection.Count Step 1
        r = 0

        'Finds the current range of the series and the axis
        For i = 1 To Len(ActiveChart.SeriesCollection(n).Formula) Step 1
            If Mid(ActiveChart.SeriesCollection(n).Formula, i, 1) = "," Then
                r = r + 1
                If r = 1 Then p1 = i + 1
                If r = 2 Then p2 = i
                If r = 3 Then p3 = i
            End If
        Next i


        'Defines new range
        Set rng = Range(Mid(ActiveChart.SeriesCollection(n).Formula, p2 + 1, p3 - p2 - 1))
        Set rng = Range(rng, rng.Offset(0, 1))

        'Sets new range for each series
        ActiveChart.SeriesCollection(n).Values = rng

        'Updates axis
        Set ax = Range(Mid(ActiveChart.SeriesCollection(n).Formula, p1, p2 - p1))
        Set ax = Range(ax, ax.Offset(0, 1))
        ActiveChart.SeriesCollection(n).XValues = ax

    Next n
End Sub
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,387
You can iterate through each chart within the active sheet as follows...

Code:
    Dim chrt_obj As ChartObject
    Dim curr_chrt As Chart

    For Each chrt_obj In ActiveSheet.ChartObjects
    
        Set curr_chrt = chrt_obj.Chart
        
        'etc
        '
        '
        
    Next chrt_obj

So your code can be amended as follows...

VBA Code:
Sub ChangeChartRange()
    Dim i As Integer, r As Integer, n As Integer, p1 As Integer, p2 As Integer, p3 As Integer
    Dim rng As Range
    Dim ax As Range
    Dim chrt_obj As ChartObject
    Dim curr_chrt As Chart

    For Each chrt_obj In ActiveSheet.ChartObjects
    
        Set curr_chrt = chrt_obj.Chart
        
        'Cycles through each series
        For n = 1 To curr_chrt.SeriesCollection.Count Step 1
            r = 0
    
            'Finds the current range of the series and the axis
            For i = 1 To Len(curr_chrt.SeriesCollection(n).Formula) Step 1
                If Mid(curr_chrt.SeriesCollection(n).Formula, i, 1) = "," Then
                    r = r + 1
                    If r = 1 Then p1 = i + 1
                    If r = 2 Then p2 = i
                    If r = 3 Then p3 = i
                End If
            Next i
    
    
            'Defines new range
            Set rng = Range(Mid(curr_chrt.SeriesCollection(n).Formula, p2 + 1, p3 - p2 - 1))
            Set rng = Range(rng, rng.Offset(0, 1))
    
            'Sets new range for each series
            curr_chrt.SeriesCollection(n).Values = rng
    
            'Updates axis
            Set ax = Range(Mid(curr_chrt.SeriesCollection(n).Formula, p1, p2 - p1))
            Set ax = Range(ax, ax.Offset(0, 1))
            curr_chrt.SeriesCollection(n).XValues = ax
    
        Next n
    Next chrt_obj
End Sub

Hope this helps!
 

Tshelky

New Member
Joined
Aug 5, 2020
Messages
5
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
You can iterate through each chart within the active sheet as follows...

Code:
    Dim chrt_obj As ChartObject
    Dim curr_chrt As Chart

    For Each chrt_obj In ActiveSheet.ChartObjects
   
        Set curr_chrt = chrt_obj.Chart
       
        'etc
        '
        '
       
    Next chrt_obj

So your code can be amended as follows...

VBA Code:
Sub ChangeChartRange()
    Dim i As Integer, r As Integer, n As Integer, p1 As Integer, p2 As Integer, p3 As Integer
    Dim rng As Range
    Dim ax As Range
    Dim chrt_obj As ChartObject
    Dim curr_chrt As Chart

    For Each chrt_obj In ActiveSheet.ChartObjects
   
        Set curr_chrt = chrt_obj.Chart
       
        'Cycles through each series
        For n = 1 To curr_chrt.SeriesCollection.Count Step 1
            r = 0
   
            'Finds the current range of the series and the axis
            For i = 1 To Len(curr_chrt.SeriesCollection(n).Formula) Step 1
                If Mid(curr_chrt.SeriesCollection(n).Formula, i, 1) = "," Then
                    r = r + 1
                    If r = 1 Then p1 = i + 1
                    If r = 2 Then p2 = i
                    If r = 3 Then p3 = i
                End If
            Next i
   
   
            'Defines new range
            Set rng = Range(Mid(curr_chrt.SeriesCollection(n).Formula, p2 + 1, p3 - p2 - 1))
            Set rng = Range(rng, rng.Offset(0, 1))
   
            'Sets new range for each series
            curr_chrt.SeriesCollection(n).Values = rng
   
            'Updates axis
            Set ax = Range(Mid(curr_chrt.SeriesCollection(n).Formula, p1, p2 - p1))
            Set ax = Range(ax, ax.Offset(0, 1))
            curr_chrt.SeriesCollection(n).XValues = ax
   
        Next n
    Next chrt_obj
End Sub

Hope this helps!

Thanks a lot, it works! Really aprreciate it!
 

Watch MrExcel Video

Forum statistics

Threads
1,114,314
Messages
5,547,175
Members
410,775
Latest member
alal1030
Top