Adding multiple series to a graph

[p.douglas]

Hi,
I'm quite new to VB and I've gotten stuck with this particular problem, despite several internet searches!

I have two columns of data, I'm trying to add the first 7 rows as series one, and the next 7 as series two etc. until I run out of data. I've tried to write some code but it doesn't work:

Dim a As Integer
Dim x As Integer

Sub Graph()
x = 1
a = 1

Do

ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatter
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "=""x"""
ActiveChart.SeriesCollection(1).XValues = "='Met1'!$B$a:$B$a+6"
ActiveChart.SeriesCollection(1).Values = "='Met1'!$C$a:$C$a+6"

x = x + 1
a = a + 7

Loop Until a = 456

End Sub


Can anyone help? I would appreciate any pointers with this one as it would take me days to do this 'by hand'

Many thanks
Philippa

[Kenneth Hobs]

Welcome to the forum!

If you record a macro, you would see that you need to use r1c1 notation.

e.g.
Sub UpdateSeries()
Dim rSales As Range, rIngresos As Range, rContribution As Range, cell As Range
Dim lastDataRow As Long, cellval As Variant

Range("A1").Select 'Make sure that no object is selected.
Set cell = Range("A" & Rows.Count).End(xlUp)
cellval = cell.Value2
Do
Set cell = cell.Offset(-1)
cellval = cell.Value2
Loop Until cellval <> 0
lastDataRow = cell.Row
Debug.Print lastDataRow
Set rSales = Range("B1", "B" & lastDataRow)
Set rIngresos = Range("C1", "C" & lastDataRow)
Set rContribution = Range("D1", "D" & lastDataRow)

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Values = "=" & rSales.Worksheet.Name & "!" & rSales.Address(True, True, xlR1C1)
ActiveChart.SeriesCollection(2).Select
ActiveChart.SeriesCollection(2).Values = "=" & rIngresos.Worksheet.Name & "!" & rIngresos.Address(True, True, xlR1C1)
ActiveChart.SeriesCollection(3).Select
ActiveChart.SeriesCollection(3).Values = "=" & rContribution.Worksheet.Name & "!" & rContribution.Address(True, True, xlR1C1)
Range("A1").Select
End Sub