Looks like I finally came across the right post. Here is the solution:

Just compose the string in the format you've already got, using the &
operator to piece together the various components, along the lines of:
ActiveChart.SeriesCollection(1).XValues = "='Traffic model'!R" & StartRow &
"C" & StartCol & ":R" & EndRow & "C" & EndCol

link -
http://www.microsoft.com/office/comm...=en-us&m=1&p=1


"Adam_needs_help" wrote:

I am trying to write a macro that makes a bar chart. However the range for
the Xvalues and Values change each time it is run, so I want them to be
dependent on variables. The code below works but it not variable dependent.

Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("For Plots").Range("A1:E183"), _
PlotBy:=xlRows
ActiveChart.SeriesCollection(1).XValues = Sheets("For
Plots").Range("D2:D5")
ActiveChart.SeriesCollection(1).Values = Sheets("For
Plots").Range("A2:A5")
ActiveChart.SeriesCollection(1).Name = "='For Plots'!R2C2"
ActiveChart.Location Whe=xlLocationAsNewSheet
ActiveChart.HasLegend = False

I want something like:

ActiveChart.SeriesCollection(1).Values = Sheets("For
Plots").Range(Cells(1,2),Cells(1,5))

Because I can put variable names in the Cells() area. Is there another way
to do this? I am reading all sorts of posts and not finding this exactly.

Thanks for your help.