Results 1 to 3 of 3

Loop thru Charts in Workbook and Changing Chart 2/Error Handling

Threaded View

  1. #1
    Forum Contributor
    Join Date
    06-16-2010
    Location
    Tampa, FL
    MS-Off Ver
    Excel 2010
    Posts
    278

    Loop thru Charts in Workbook and Changing Chart 2/Error Handling

    Hi, I have code that will change data labels, now I want to loop through all the worksheets and change chart 2. Not every sheet has a chart, but if there are charts there is a chart 1 2 and 3.
    My current code activates chart 2 and then changes it, but that doesn't work with looping and I'm stuck on how to reference it.
    Portion of code below

    
    Dim WSC As Integer
    Dim I As Integer
             
    WSC = ActiveWorkbook.Worksheets.Count
    
        For I = 1 To WSC
    
    
    Dim s1p1, s1p2, s1p3, s1p4 As Long
    Dim s2p1, s2p2, s2p3, s2p4 As Long
    
    ActiveSheet.ChartObjects("Chart 2").Activate
    
        s1p1 = CLng(ActiveChart.SeriesCollection(6).Points(1).DataLabel.Text)
        s1p2 = CLng(ActiveChart.SeriesCollection(6).Points(2).DataLabel.Text)
        s1p3 = CLng(ActiveChart.SeriesCollection(6).Points(3).DataLabel.Text)
        s1p4 = CLng(ActiveChart.SeriesCollection(6).Points(4).DataLabel.Text)
        
        s2p1 = CLng(ActiveChart.SeriesCollection(5).Points(1).DataLabel.Text)
        s2p2 = CLng(ActiveChart.SeriesCollection(5).Points(2).DataLabel.Text)
        s2p3 = CLng(ActiveChart.SeriesCollection(5).Points(3).DataLabel.Text)
        s2p4 = CLng(ActiveChart.SeriesCollection(5).Points(4).DataLabel.Text)
    
    'point 1
        If s1p1 > s2p1 Then
           ActiveChart.SeriesCollection(6).Points(1).DataLabel.Position = xlLabelPositionAbove
            ActiveChart.SeriesCollection(5).Points(1).DataLabel.Position = xlLabelPositionBelow
        Else: ActiveChart.SeriesCollection(6).Points(1).DataLabel.Position = xlLabelPositionBelow
            ActiveChart.SeriesCollection(5).Points(1).DataLabel.Position = xlLabelPositionAbove
        End If
    'more code...
    
            Next I
    'end
    Thank you.
    Last edited by ker9; 03-29-2014 at 09:32 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA Loop for a folder of all the files, Loop all the worksheet in each workbook
    By nanjingwoodworking in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-07-2013, 07:20 PM
  2. Replies: 0
    Last Post: 07-20-2010, 11:42 AM
  3. Titles on Charts in a loop
    By jcfryman in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 11-02-2009, 08:38 AM
  4. [SOLVED] Problem adding charts using Do-Loop Until loop
    By Chris Bromley in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-23-2005, 09:06 AM
  5. help with for loop for charts
    By steve_bris in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-15-2005, 07:34 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1