PDA

View Full Version : Change series of a chart without using relative reference....



nikki
10-02-2008, 02:10 AM
Hello again!

I am trying to update a chart automatically as part of mycode (i.e. without changing the reference (relating to cell AF), each month)

This is the code I have from recording the macro:
ActiveChart.SeriesCollection(1).Values = "='BRC Retention'!R25C6:R25C32"
ActiveChart.SeriesCollection(2).Values = "='BRC Retention'R31C6:R31C32"
ActiveChart.SeriesCollection(3).Values = "='BRC Retention'!R26C6:R26C32"
ActiveChart.SeriesCollection(4).Values = "='BRC Retention'!R32C6:R32C32"

I want it to look like this:
ActiveChart.SeriesCollection(1).Values = "='BRC Retention'!F25:AF25"
ActiveChart.SeriesCollection(2).Values = "='BRC Retention'!F31:AF31"
ActiveChart.SeriesCollection(3).Values = "='BRC Retention'!F26:AF26"
ActiveChart.SeriesCollection(4).Values = "='BRC Retention'!F32:AF32"

So I can eventually get something that looks like this (after assigning col_ref6 to a cell within excel that holds the value AF):
ActiveChart.SeriesCollection(1).Values = "='BRC Retention'!F25:" & col_ref6 & "25"
ActiveChart.SeriesCollection(2).Values = "='BRC Retention'!F31:" & col_ref6 & "31"
ActiveChart.SeriesCollection(3).Values = "='BRC Retention'!F26:" & col_ref6 & "26"
ActiveChart.SeriesCollection(4).Values = "='BRC Retention'!F32:" & col_ref6 & "32"

Any ideas?