Automated charting for drawing boxes

**gshock** · 11-06-2008, 08:19 PM

I did a search and found a post than shg and Andy Pope responded to about making charts with boxes from the data. Some great info. Now, that I've got it working to suite my needs, I'm trying to automate the chart so that it expands to include additional rows of data.

I'm referencing this post in case you want more background on what I'm trying to do: http://www.excelforum.com/excel-char...n-a-chart.html

I've included a sample to indicate as well...

Thanks
-gshock

**Andy Pope** · 11-07-2008, 07:04 AM

auto updating is not easily done as new series are required rather than extending existing series.

**gshock** · 11-07-2008, 08:57 AM

Originally Posted by Andy Pope

auto updating is not easily done as new series are required rather than extending existing series.

I see. To that point, can I just create all the series that I need, but have the data range be blank? It will take some work to set up the first time, but then each one will already be in place.

Is there a limit to how many series I can have in a chart? I need to have a total of 100 series.

**Andy Pope** · 11-07-2008, 09:07 AM

Yes you can do that. 255 is the limit

Will each box need a different colour?

This will add the 100 series. Make sure the x and y value ranges contains a value, even if it's zero, when you run the code.

To remove the legend entries hide unused rows.

Code:

Sub AddSeries()

    Dim lngIndex As Long
    Dim rngName As Range
    Dim rngXData As Range
    Dim rngYData As Range
    
    Set rngName = Range("A2")
    Set rngXData = Range("H3:L3")
    Set rngYData = Range("B3:F3")
    
    With ActiveChart
        For lngIndex = 1 To 100
            If .SeriesCollection.Count < lngIndex Then
                With .SeriesCollection.NewSeries
                    .Name = rngName.Offset(lngIndex)
                    .Values = rngYData
                    .XValues = rngXData
                End With
            End If
            Set rngYData = rngYData.Offset(1)
            Set rngXData = rngXData.Offset(1)
        Next
    End With
    
End Sub

**gshock** · 11-12-2008, 12:24 PM

Andy,

Cheers for that. Sorry that it's taken me so long to respond. It's been a hectic couple of weeks. Thanks very much for helping me with this. That works great.

Regards,
-gshock

**gshock** · 11-13-2008, 11:47 AM

Hi Andy,
Finally got back to working with this, and I have two groups of data that I'd like to plot concurrently, that is 2-sets of x-values, and 2-sets of y-values.

I modified the code you gave me so that I can have two sets of data, but I'm getting an error on the range name.

Can you help? Thanks a lot for what you've already given me. I really appreciate your time.

Code:

Sub AddSeries()

    Dim lngIndex As Long
    Dim NrngName As Range
    Dim NrngXData As Range
    Dim NrngYData As Range

    Set NrngName = Range("A4")
    Set NrngXData = Range("R4:AD4")
    Set NrngYData = Range("AF4:AR4")
    
    Dim BrngName As Range
    Dim BrngXData As Range
    Dim BrngYData As Range

    Set BrngName = Range("A59")
    Set BrngXData = Range("R59:AD59")
    Set BrngYData = Range("AF59:AR59")

    With ActiveChart
        For lngIndex = 1 To 100
            If .SeriesCollection.count < lngIndex Then
                With .SeriesCollection.NewSeries
                    .Name = NrngName.Offset(lngIndex)
                    .Values = NrngYData
                    .XValues = NrngXData
                    
                    .Name = BrngName.Offset(lngIndex)
                    .Values = BrngYData
                    .XValues = BrngXData
                End With
            End If
            Set NrngYData = NrngYData.Offset(1)
            Set NrngXData = NrngXData.Offset(1)
            Set BrngYData = BrngYData.Offset(1)
            Set BrngXData = BrngXData.Offset(1)
        Next
    End With

End Sub

Regards,
-gshock

**Andy Pope** · 11-13-2008, 12:12 PM

Which line exactly is giving the error?

I assume the chart is on the same worksheet as the data?

Can you post example data set and starting chart?

**gshock** · 11-13-2008, 12:56 PM

Originally Posted by Andy Pope

Which line exactly is giving the error?

I assume the chart is on the same worksheet as the data?

Can you post example data set and starting chart?

Hi Andy, I'm getting the error on this line

Code:

Set NrngName = Range("A4")

No, the chart is in a separate sheet, and that was going to be my next question. I have some other macros that reference other sheets, and I was going to try and figure it out. But I keep getting distracted at work. Imagine that!

Thanks for all of your help.

Sample data is attached.

Regards,

-gshock

**Andy Pope** · 11-13-2008, 03:58 PM

Code:

Sub AddSeries()

    Dim lngIndex As Long
    Dim NrngName As Range
    Dim NrngXData As Range
    Dim NrngYData As Range
    Dim BrngName As Range
    Dim BrngXData As Range
    Dim BrngYData As Range

    With Worksheets("Corner_Points")
        Set NrngName = .Range("A4")
        Set NrngXData = .Range("R4:AD4")
        Set NrngYData = .Range("AF4:AR4")
        
    
        Set BrngName = .Range("A59")
        Set BrngXData = .Range("R59:AD59")
        Set BrngYData = .Range("AF59:AR59")
    End With
    
    With ActiveChart
        For lngIndex = 1 To 100
            If .SeriesCollection.Count < lngIndex Then
                If Len(NrngName.Offset(lngIndex)) = 0 Then
                    Exit For
                End If
                
                With .SeriesCollection.NewSeries
                    .Name = NrngName.Offset(lngIndex)
                    .Values = NrngYData
                    .XValues = NrngXData
                    
                    .Name = BrngName.Offset(lngIndex)
                    .Values = BrngYData
                    .XValues = BrngXData
                    .Border.ColorIndex = 1
                    .MarkerStyle = xlNone
                End With
            End If
            Set NrngYData = NrngYData.Offset(1)
            Set NrngXData = NrngXData.Offset(1)
            Set BrngYData = BrngYData.Offset(1)
            Set BrngXData = BrngXData.Offset(1)
        Next
    End With

End Sub

**gshock** · 11-13-2008, 04:45 PM

Andy,
Thanks for helping me with this. Sorry to keep dragging it out, but I'm still having trouble. I'm supposed to run the macro to populate the source data for a chart that I've already created in a separate sheet, right?

When entering the loop, (lngIndex = 1) I'm getting Run-Time error on this line:

Code:

If .SeriesCollection.count < lngIndex Then

The error is 'Object variable or With block variable not set.' I'm using xl 2000.

Thanks
-gshock

**Andy Pope** · 11-13-2008, 04:59 PM

Just tested in xl2000.

You need to run the code when the chart sheet is selected.
The code will not adjust any series that already exist. It will only add new series.

This version will update existing series and add new ones if required.

Code:

Sub AddSeries()

    Dim lngIndex As Long
    Dim NrngName As Range
    Dim NrngXData As Range
    Dim NrngYData As Range
    Dim BrngName As Range
    Dim BrngXData As Range
    Dim BrngYData As Range
    Dim objSeries As Series
    Dim lngNSeries As Long
    
    With Worksheets("Corner_Points")
        Set NrngName = .Range("A4")
        Set NrngXData = .Range("R4:AD4")
        Set NrngYData = .Range("AF4:AR4")
        
    
        Set BrngName = .Range("A59")
        Set BrngXData = .Range("R59:AD59")
        Set BrngYData = .Range("AF59:AR59")
    End With
    
    With ActiveChart
        For lngIndex = 1 To 100
            If Len(NrngName.Offset(lngIndex)) = 0 Then
                Exit For
            End If
            
            lngNSeries = lngNSeries + 1
            If lngNSeries > .SeriesCollection.Count Then
                Set objSeries = .SeriesCollection.NewSeries
            Else
                Set objSeries = .SeriesCollection(lngIndex)
            End If
                
            With objSeries
                .Name = NrngName.Offset(lngIndex)
                .Values = NrngYData
                .XValues = NrngXData
                .Border.ColorIndex = 1
                .MarkerStyle = xlNone
            End With
            
            lngNSeries = lngNSeries + 1
            If lngNSeries > .SeriesCollection.Count Then
                Set objSeries = .SeriesCollection.NewSeries
            Else
                Set objSeries = .SeriesCollection(lngIndex)
            End If
            
            With objSeries
                .Name = BrngName.Offset(lngIndex)
                .Values = BrngYData
                .XValues = BrngXData
                .Border.ColorIndex = 1
                .MarkerStyle = xlNone
            End With
            
            Set NrngYData = NrngYData.Offset(1)
            Set NrngXData = NrngXData.Offset(1)
            Set BrngYData = BrngYData.Offset(1)
            Set BrngXData = BrngXData.Offset(1)
        Next
    End With

End Sub

This also deals with the Bucket and Nozzel series for each row of data. Something I missed in the other code samples.

**gshock** · 11-13-2008, 06:09 PM

Originally Posted by Andy Pope

Just tested in xl2000.

Andy, I love it. It works fantastic. One quirk that I noticed; if I populate the chart, and then run the macro again, it seems to delete the first 11 stages from the chart for the nozzles. I don't understand why.

This tool has become really popular already. I need to do something that allows for user input, but it's off-topic so I'll create a new thread. Thanks for all your help!

You've been a tremendous help.

Regards,
-gshock

**Andy Pope** · 11-14-2008, 07:15 AM

I forgot to update the assignment of seriescollection to use the new index variable.

Code:

Sub AddSeries()

    Dim lngIndex As Long
    Dim NrngName As Range
    Dim NrngXData As Range
    Dim NrngYData As Range
    Dim BrngName As Range
    Dim BrngXData As Range
    Dim BrngYData As Range
    Dim objSeries As Series
    Dim lngNSeries As Long
    
    With Worksheets("Corner_Points")
        Set NrngName = .Range("A4")
        Set NrngXData = .Range("R4:AD4")
        Set NrngYData = .Range("AF4:AR4")
        
    
        Set BrngName = .Range("A59")
        Set BrngXData = .Range("R59:AD59")
        Set BrngYData = .Range("AF59:AR59")
    End With
    
    With ActiveChart
        For lngIndex = 1 To 100
            If Len(NrngName.Offset(lngIndex)) = 0 Then
                Exit For
            End If
            
            lngNSeries = lngNSeries + 1
            If lngNSeries > .SeriesCollection.Count Then
                Set objSeries = .SeriesCollection.NewSeries
            Else
                Set objSeries = .SeriesCollection(lngNSeries)
            End If
                
            With objSeries
                .Name = NrngName.Offset(lngIndex)
                .Values = NrngYData
                .XValues = NrngXData
                .Border.ColorIndex = 1
                .MarkerStyle = xlNone
            End With
            
            lngNSeries = lngNSeries + 1
            If lngNSeries > .SeriesCollection.Count Then
                Set objSeries = .SeriesCollection.NewSeries
            Else
                Set objSeries = .SeriesCollection(lngNSeries)
            End If
            
            With objSeries
                .Name = BrngName.Offset(lngIndex)
                .Values = BrngYData
                .XValues = BrngXData
                .Border.ColorIndex = 5
                .MarkerStyle = xlNone
            End With
            
            Set NrngYData = NrngYData.Offset(1)
            Set NrngXData = NrngXData.Offset(1)
            Set BrngYData = BrngYData.Offset(1)
            Set BrngXData = BrngXData.Offset(1)
        Next
    End With

End Sub

Thread: Automated charting for drawing boxes

Thread Tools

Display

Automated charting for drawing boxes

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions