runnerpatch3
New Member
- Joined
- Dec 16, 2011
- Messages
- 3
Hello all,
I am writing a macro to automate bubble charts. I've made one that pulls in the value itself into the chart, but if you go back and change the data, the chart does not update.
Here is an example:
ActiveChart.SeriesCollection(iCounter).XValues = ActiveSheet.Cells(iRowNum, iColNum + 1).Value
But I need it to put in a reference to that cell, not the value itself. Something like this:
ActiveChart.SeriesCollection(iCounter).Name = "=Sheet1!R1C2"
What I really need is the latter along these lines:
ActiveChart.SeriesCollection(iCounter).Name = "=Sheet1!R & iRowNum & C & iColNum"
iRowNum updates based on which row of the data set the loop is in
iColNum is either 0,1,2,3 (name, x-value, y-value, bubble size) but is different based on which field I want to update
The purpose of the macro is to create an empty table with the bubble chart referencing the empty table. And the user can go in and input values into the data table. I have another version that takes a pre-filled table, but I want it this way to ensure that all data tables are set up the same (name, x, y, size INSTEAD OF x,y,size,name....for instance).
Thank you!
I am writing a macro to automate bubble charts. I've made one that pulls in the value itself into the chart, but if you go back and change the data, the chart does not update.
Here is an example:
ActiveChart.SeriesCollection(iCounter).XValues = ActiveSheet.Cells(iRowNum, iColNum + 1).Value
But I need it to put in a reference to that cell, not the value itself. Something like this:
ActiveChart.SeriesCollection(iCounter).Name = "=Sheet1!R1C2"
What I really need is the latter along these lines:
ActiveChart.SeriesCollection(iCounter).Name = "=Sheet1!R & iRowNum & C & iColNum"
iRowNum updates based on which row of the data set the loop is in
iColNum is either 0,1,2,3 (name, x-value, y-value, bubble size) but is different based on which field I want to update
The purpose of the macro is to create an empty table with the bubble chart referencing the empty table. And the user can go in and input values into the data table. I have another version that takes a pre-filled table, but I want it this way to ensure that all data tables are set up the same (name, x, y, size INSTEAD OF x,y,size,name....for instance).
Thank you!
