CODEWITHSUNDEEP
vbaexcel
Максим Чурсин
Максим Чурсин

Reputation: 11

Loop over array of a charts

I am trying to write a code that loops over a predefined array of chart names and does smth to every chart My initial guess was

Sub Select_Charts_On_Sheet()
'Selects every chart on a sheet

Dim c As Chart
Dim ChartArray As Variant
ChartArray = Array("Chart 1", "Chart 2", "Chart 3", "Chart 4")
For Each c In Charts(ChartArray)
    MsgBox (c.Name)
Next c

End Sub

Yet it does not work. Any suggestions?

Upvotes: 1

Views: 773

Answers (2)

Jon Peltier
Jon Peltier

Reputation: 6063

To do every chart on a worksheet, it's easy:

Dim ChOb As ChartObject
For Each ChOb In ActiveSheet.ChartObjects
    With ChOb.Chart
        ' do something to each chart
    End With
Next

Alternatively:

Dim iCht As Long
For iCht = 1 To ActiveSheet.ChartObjects.Count
    With ActiveSheet.ChartObjects(i).Chart
        ' do something to each chart
    End With
Next

Upvotes: 0

Shai Rado
Shai Rado

Reputation: 33692

You need to loop through the worksheet's ChartObjects, and check per each ChartObject.Name if it's found inside your ChartArray.

Code

Option Explicit

Sub Select_Charts_On_Sheet()
'Selects every chart on a sheet

Dim ChtObj As ChartObject
Dim ChartArray As Variant

ChartArray = Array("Chart 1", "Chart 2", "Chart 3", "Chart 4")

' loop through worksheet's chart objects
For Each ChtObj In Worksheets("Sheet1").ChartObjects ' modify "Sheet1" with your sheet's name
    MsgBox ChtObj.Name

    ' use application match to see if current chart object name is inside the ChartArray (of names)
    If Not IsError(Application.Match(ChtObj.Name, ChartArray, 0)) Then ' Match was successful
        ChtObj.Select
    End If
Next ChtObj

End Sub

Upvotes: 1

Related Questions