I'm a fairly new user of VB. I'm trying to pull together some code that will automatically add a new series to a bubble graph. The graph references a data table on another worksheet. The new series should be the last row of that table - which is created by an eariler macro. But. I can't get the new series to look at the right cells. Any ideas much appreciated.
This is the code I'm using:
Dim intSeriesCount As Integer
Dim strName As String
Dim xValue As String
Dim yValue As String
Dim bubble
'The sheet "Summary Assessment Scores" being where the data table is. the Range "HiddenSummary" is the cell at the left top corner of the data table.
Sheets("Summary Assessment Scores").Select
Range("HiddenSummary").End(xlDown).Select
strName = ActiveCell.Address
xValue = ActiveCell.Offset(0, 2).Address
yValue = ActiveCell.Offset(0, 1).Address
bubble = ActiveCell.Offset(0, 3).Address
'The worksheet "Country Comparison" being where the graph is stored
Sheets("Country Comparison").Select
ActiveChart.SeriesCollection.NewSeries
intSeriesCount = ActiveChart.SeriesCollection.Count
ActiveChart.SeriesCollection(intSeriesCount).xValue = "='Summary Assessment Scores'!" & CStr(xValue)
ActiveChart.SeriesCollection(intSeriesCount).Value = "='Summary Assessment Scores'!" & CStr(yValue)
ActiveChart.SeriesCollection(intSeriesCount).Name = "='Summary Assessment Scores'!" & CStr(strName)
ActiveChart.SeriesCollection(intSeriesCount).BubbleSizes = "='Summary Assessment Scores'!" & CStr(bubble)
ActiveChart.ChartType = xlBubble
This is the code I'm using:
Dim intSeriesCount As Integer
Dim strName As String
Dim xValue As String
Dim yValue As String
Dim bubble
'The sheet "Summary Assessment Scores" being where the data table is. the Range "HiddenSummary" is the cell at the left top corner of the data table.
Sheets("Summary Assessment Scores").Select
Range("HiddenSummary").End(xlDown).Select
strName = ActiveCell.Address
xValue = ActiveCell.Offset(0, 2).Address
yValue = ActiveCell.Offset(0, 1).Address
bubble = ActiveCell.Offset(0, 3).Address
'The worksheet "Country Comparison" being where the graph is stored
Sheets("Country Comparison").Select
ActiveChart.SeriesCollection.NewSeries
intSeriesCount = ActiveChart.SeriesCollection.Count
ActiveChart.SeriesCollection(intSeriesCount).xValue = "='Summary Assessment Scores'!" & CStr(xValue)
ActiveChart.SeriesCollection(intSeriesCount).Value = "='Summary Assessment Scores'!" & CStr(yValue)
ActiveChart.SeriesCollection(intSeriesCount).Name = "='Summary Assessment Scores'!" & CStr(strName)
ActiveChart.SeriesCollection(intSeriesCount).BubbleSizes = "='Summary Assessment Scores'!" & CStr(bubble)
ActiveChart.ChartType = xlBubble