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

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,375
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,113,878
Messages
5,544,832
Members
410,638
Latest member
Satishj013
Top