spflash
05-04-2006, 09:29 AM
Hi All, I'm a VBA Newbie. The code below works fine to create an embedded chart if the number of rows in the spreadsheet that go into a series collection does not change.
However, what I'm attempting to do is write code that will read the number of rows as the spreadsheet changes, adjust the series collection and generate the same chart type, but with the correct number of series to match the number of rows.
For example, if the number of rows is the source data is 5 can we make the series collection use all 5 lines? If the number of rows changes to 3 can we use the same macro to use the 3 rows of data in the series collection?
Is it possible to do this? Is anyone familiar with this type of situation?
Please let me know. Thanx.
Sub CreatingChart()
Dim TB As Worksheet
Dim j%, lR%
Dim Rng$
Dim S As SeriesCollection
Set TB = ActiveSheet
lR = Cells(65536, 1).End(xlUp).Row
For j = 4 To lR
Rng = TB.Range(TB.Cells(j, 1), TB.Cells(j, 21)).Address
Next j
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets(TB.Name).Range(Rng), PlotBy _
:=xlRows
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection (1)
ActiveChart.SeriesCollection(1).XValues = TB.Range("G3:U3")
ActiveChart.SeriesCollection(1).Values = TB.Range(TB.Cells(4, 7), TB.Cells(4, 21))
ActiveChart.SeriesCollection(1).Name = TB.Range("B4")
ActiveChart.SeriesCollection (2)
ActiveChart.SeriesCollection(2).XValues = TB.Range("G3:U3")
ActiveChart.SeriesCollection(2).Values = TB.Range(TB.Cells(5, 7), TB.Cells(5, 21))
ActiveChart.SeriesCollection(2).Name = TB.Range("B5")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection (3)
ActiveChart.SeriesCollection(3).XValues = TB.Range("G3:U3")
ActiveChart.SeriesCollection(3).Values = TB.Range(TB.Cells(6, 7), TB.Cells(6, 21))
ActiveChart.SeriesCollection(3).Name = TB.Range("B6")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection (4)
ActiveChart.SeriesCollection(4).XValues = TB.Range("G3:U3")
ActiveChart.SeriesCollection(4).Values = TB.Range(TB.Cells(7, 7), TB.Cells(7, 21))
ActiveChart.SeriesCollection(4).Name = TB.Range("B7")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection (5)
ActiveChart.SeriesCollection(5).XValues = TB.Range("G3:U3")
ActiveChart.SeriesCollection(5).Values = TB.Range(TB.Cells(8, 7), TB.Cells(8, 21))
ActiveChart.SeriesCollection(5).Name = TB.Range("B8")
ActiveChart.Location Where:=xlLocationAsObject, Name:=TB.Name
With ActiveChart
.HasTitle = True
.ChartTitle.Text = TB.Range("A1")
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlCategory).TickLabelSpacing = 1
.Axes(xlValue, xlPrimary).HasTitle = False
End With
End Sub
However, what I'm attempting to do is write code that will read the number of rows as the spreadsheet changes, adjust the series collection and generate the same chart type, but with the correct number of series to match the number of rows.
For example, if the number of rows is the source data is 5 can we make the series collection use all 5 lines? If the number of rows changes to 3 can we use the same macro to use the 3 rows of data in the series collection?
Is it possible to do this? Is anyone familiar with this type of situation?
Please let me know. Thanx.
Sub CreatingChart()
Dim TB As Worksheet
Dim j%, lR%
Dim Rng$
Dim S As SeriesCollection
Set TB = ActiveSheet
lR = Cells(65536, 1).End(xlUp).Row
For j = 4 To lR
Rng = TB.Range(TB.Cells(j, 1), TB.Cells(j, 21)).Address
Next j
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets(TB.Name).Range(Rng), PlotBy _
:=xlRows
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection (1)
ActiveChart.SeriesCollection(1).XValues = TB.Range("G3:U3")
ActiveChart.SeriesCollection(1).Values = TB.Range(TB.Cells(4, 7), TB.Cells(4, 21))
ActiveChart.SeriesCollection(1).Name = TB.Range("B4")
ActiveChart.SeriesCollection (2)
ActiveChart.SeriesCollection(2).XValues = TB.Range("G3:U3")
ActiveChart.SeriesCollection(2).Values = TB.Range(TB.Cells(5, 7), TB.Cells(5, 21))
ActiveChart.SeriesCollection(2).Name = TB.Range("B5")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection (3)
ActiveChart.SeriesCollection(3).XValues = TB.Range("G3:U3")
ActiveChart.SeriesCollection(3).Values = TB.Range(TB.Cells(6, 7), TB.Cells(6, 21))
ActiveChart.SeriesCollection(3).Name = TB.Range("B6")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection (4)
ActiveChart.SeriesCollection(4).XValues = TB.Range("G3:U3")
ActiveChart.SeriesCollection(4).Values = TB.Range(TB.Cells(7, 7), TB.Cells(7, 21))
ActiveChart.SeriesCollection(4).Name = TB.Range("B7")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection (5)
ActiveChart.SeriesCollection(5).XValues = TB.Range("G3:U3")
ActiveChart.SeriesCollection(5).Values = TB.Range(TB.Cells(8, 7), TB.Cells(8, 21))
ActiveChart.SeriesCollection(5).Name = TB.Range("B8")
ActiveChart.Location Where:=xlLocationAsObject, Name:=TB.Name
With ActiveChart
.HasTitle = True
.ChartTitle.Text = TB.Range("A1")
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlCategory).TickLabelSpacing = 1
.Axes(xlValue, xlPrimary).HasTitle = False
End With
End Sub