-
Adding multiple series to a graph
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:
[VBA]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
[/VBA]
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
Last edited by Bob Phillips; 08-23-2011 at 10:00 AM.
Reason: Added VBA tags
-
Welcome to the forum!
If you record a macro, you would see that you need to use r1c1 notation.
e.g.
[VBA]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[/VBA]
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules