Adding additional source data to a chart

**Mavis Cruet** · 10-18-2011, 09:21 AM

This is the first time I'm using Macros in Excel and I'm having trouble trying to add additional source data to a chart. I created a macro using the record macro tool; however it only generates a new series on the chart. The Name, X Value and Y Value fields are completely empty. How do I change the macro to populate them?

Thanks


'Sub add_data()
'
' add_data Macro
' Macro recorded 18/10/2011 by '
    ActiveChart.SeriesCollection(5).XValues = "=BER!R26C2:R33C2"
    ActiveChart.SeriesCollection(5).Values = "=BER!R26C7:R33C7"
    ActiveChart.SeriesCollection(5).Name = "=BER!R20C3:R20C7"
End Sub

**mrice** · 10-18-2011, 03:32 PM

Try

ActiveChart.SeriesCollection.NewSeries

and then set the values in a similar way to those in the code that you recorded.

**Mavis Cruet** · 10-19-2011, 04:21 AM

Thanks. I tried that and it still creates a new series without data. A run-time error occurs, when going into the debug option the second line in code is highlighted. I'm very new to all this and really appreciate you helping me out :-)

Code:
sub snb()
ActiveChart.SeriesCollection(5).XValues = "=BER!R26C2:R33C2
end sub

**mrice** · 10-19-2011, 05:02 PM

Try this.

Sub TEst()
ActiveChart.SeriesCollection(5).XValues = "=BER!R26C2:R33C2"
ActiveChart.SeriesCollection(5).Values = "=BER!R26C7:R33C7"
ActiveChart.SeriesCollection(5).Name = "=BER!R20C3:R20C7"

ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(6).XValues = "=BER!R26C2:R33C2"
ActiveChart.SeriesCollection(6).Values = "=BER!R26C7:R33C7"
ActiveChart.SeriesCollection(6).Name = "=BER!R20C3:R20C7"
'Adjust the values for the new series to the correct ranges

End Sub

**Mavis Cruet** · 10-20-2011, 09:38 AM

That also still doesnt work :-( this time the debugger higlights the .values line. I'm starting to think it might be easier to keep adding measurements manualy :-(

**Andy Pope** · 10-20-2011, 10:10 AM

It would probably help to post example file explain further what the code should do.

**Mavis Cruet** · 10-20-2011, 10:32 AM

I've (hopefully) attatched a simpler version of what I'm attempting to do.

Basically I have three sets of results for one mission. In order to easily compare new missions to older ones I want to plot the data onto a graph. However I currently have over 20 old missions with new ones coming every six months or so. And as I'm not the only person who will be using this document in the future I wanted to automate all the graphing functions. So in the future they will simply copy the data input tabel then implement a macro for it to add the data to the chart.

I thought I'd try to get a simple macro to just plot data before I attempted to work out how to make it shift down the sheet as new data sets are added.

Thanks for trying to help me solve this problem :-)

**Andy Pope** · 10-20-2011, 10:33 AM

No attachment. Is it too big?

**Mavis Cruet** · 10-20-2011, 10:38 AM

I dont think so I completly striped it down its only 36.5KB. It says its attatched in the Attatch files section.

**Andy Pope** · 10-20-2011, 11:19 AM

This will prompt the use for a test name.
It will use series 4 to 6, creating series if required.

Sub Add_data()
'
' Add_data Macro
' Macro recorded 20/10/2011 by lfoote
'
    Dim strTest As String
    Dim lngRow As Long
    Dim rngDataX As Range
    Dim rngDataY As Range
    Dim rngName As Range
    Dim lngIndex As Long
    Dim objSeries As Series
    
'
    strTest = InputBox("Enter Number of test to compare to Test 1", , "Test 2")
    Set rngfind = Sheet1.Range("C:E").Find(strTest)
    If Not rngfind Is Nothing Then
        lngRow = rngfind.Row
        
        Set rngDataX = Sheet1.Cells(lngRow + 6, 2).Resize(8, 1)
        Set rngDataY = rngDataX.Offset(, 5)
        Set rngName = rngfind.Resize(1, 5)
        For lngIndex = 4 To 6
            With Charts("Chart1")
                If lngIndex <= .SeriesCollection.Count Then
                    Set objSeries = .SeriesCollection(lngIndex)
                Else
                    Set objSeries = .SeriesCollection.NewSeries
                End If
            End With
            
            objSeries.Values = rngDataY
            objSeries.XValues = rngDataX
            objSeries.Name = "='" & rngName.Parent.Name & "'!" & rngName.Address(, , xlR1C1)
            
            Set rngDataX = rngDataX.Offset(0, 7)
            Set rngDataY = rngDataY.Offset(0, 7)
            Set rngName = rngName.Offset(0, 7)
        Next
    Else
        MsgBox "Could not locate " & strtext, vbExclamation
    End If
    
End Sub

**Mavis Cruet** · 10-21-2011, 03:51 AM

Thank you so much its perfect! :-D

Thread: Adding additional source data to a chart

Thread Tools

Display

Adding additional source data to a chart

Re: Adding additional source data to a chart

Re: Adding additional source data to a chart

Re: Adding additional source data to a chart

Re: Adding additional source data to a chart

Re: Adding additional source data to a chart

Re: Adding additional source data to a chart

Re: Adding additional source data to a chart

Re: Adding additional source data to a chart

Re: Adding additional source data to a chart

Re: Adding additional source data to a chart

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions