Oops - in my previous post I neglected to mention that you need to force the
address to be written back in R1C1 notation (I've never succeeded in getting
a chart dataseries assigned using A1 notation).

ActiveSheet.Range("R27C9", Selection.End(xlDown)).Select
temp = Selection.Address(False,False,xlR1C1)



"Girish" wrote:

Hey Guys!

Below is a part of my code to generate charts. I want a dynamic (not fixed)
range.
for example, In "=Calculations!R27C9:R34C9", I don't want to fix my data
range. How do I go about it?


Charts.Add
ActiveChart.ApplyCustomType ChartType:=xlUserDefined, TypeName:="Girish"
ActiveChart.SetSourceData
Source:=Sheets("Calculations").Range("J27:L34"), _
PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).XValues = "=Calculations!R27C9:R34C9"
ActiveChart.SeriesCollection(1).Values = "=Calculations!R27C10:R34C10"
ActiveChart.SeriesCollection(1).Name = "=""Count"""
ActiveChart.SeriesCollection(2).XValues = "=Calculations!R27C9:R34C9"
ActiveChart.SeriesCollection(2).Values = "=Calculations!R27C11:R34C11"
ActiveChart.SeriesCollection(2).Name = "=""Individual"""
ActiveChart.SeriesCollection(3).XValues = "=Calculations!R27C9:R34C9"
ActiveChart.SeriesCollection(3).Name = "=""Cumulative"""
ActiveChart.Location Whe=xlLocationAsObject, Name:="Calculations"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
.Axes(xlCategory, xlSecondary).HasTitle = False
.Axes(xlValue, xlSecondary).HasTitle = False
End With