Graph with variable range
 

I'm having trouble graphing data on a worksheet that has variable ranges.
Can anyone spot the problem with this code? The problem is always with
setting a range for the data.

Here are my variables - I know they work just fine.

xaxis = "A" & "51" & ":" & "A" & 51 + xrange - 1
projected = "D" & "51" & ":" & "D" & 51 + xrange - 1
actual = "E" & "51" & ":" & "E" & 51 + xrange - 1

And here is the first bit of the code for the graph.

Charts.Add
ActiveChart.ChartType = xlXYScatterLines
ActiveChart.SeriesCollection(1).XValues = "Sheets(sheetname).Range(xaxis)"
ActiveChart.SeriesCollection(1).Values =
"Sheets(sheetname).Range(projected)"
ActiveChart.SeriesCollection(2).XValues = "Sheets(sheetname).Range(xaxis)"
ActiveChart.SeriesCollection(2).Values = "Sheets(sheetname).Range(actual)"

"sheetname" is the name of each individual worksheet in the file. That
variable is a string that I've populated earlier in the code.

Thanks!

Graph with variable range
 

You have put quotes around the range definitions so you are just passing text
instead of ranges. Remove the quotes...

ActiveChart.SeriesCollection(1).XValues = Sheets(sheetname).Range(xaxis)

--
HTH...

Jim Thomlinson


"kcrad" wrote:

I'm having trouble graphing data on a worksheet that has variable ranges.
Can anyone spot the problem with this code? The problem is always with
setting a range for the data.

Here are my variables - I know they work just fine.

xaxis = "A" & "51" & ":" & "A" & 51 + xrange - 1
projected = "D" & "51" & ":" & "D" & 51 + xrange - 1
actual = "E" & "51" & ":" & "E" & 51 + xrange - 1

And here is the first bit of the code for the graph.

Charts.Add
ActiveChart.ChartType = xlXYScatterLines
ActiveChart.SeriesCollection(1).XValues = "Sheets(sheetname).Range(xaxis)"
ActiveChart.SeriesCollection(1).Values =
"Sheets(sheetname).Range(projected)"
ActiveChart.SeriesCollection(2).XValues = "Sheets(sheetname).Range(xaxis)"
ActiveChart.SeriesCollection(2).Values = "Sheets(sheetname).Range(actual)"

"sheetname" is the name of each individual worksheet in the file. That
variable is a string that I've populated earlier in the code.

Thanks!

Graph with variable range
 

Sometimes I can be such an idiot!!!

Thanks!

"Jim Thomlinson" wrote:

You have put quotes around the range definitions so you are just passing text
instead of ranges. Remove the quotes...

ActiveChart.SeriesCollection(1).XValues = Sheets(sheetname).Range(xaxis)

--
HTH...

Jim Thomlinson


"kcrad" wrote:

I'm having trouble graphing data on a worksheet that has variable ranges.
Can anyone spot the problem with this code? The problem is always with
setting a range for the data.

Here are my variables - I know they work just fine.

xaxis = "A" & "51" & ":" & "A" & 51 + xrange - 1
projected = "D" & "51" & ":" & "D" & 51 + xrange - 1
actual = "E" & "51" & ":" & "E" & 51 + xrange - 1

And here is the first bit of the code for the graph.

Charts.Add
ActiveChart.ChartType = xlXYScatterLines
ActiveChart.SeriesCollection(1).XValues = "Sheets(sheetname).Range(xaxis)"
ActiveChart.SeriesCollection(1).Values =
"Sheets(sheetname).Range(projected)"
ActiveChart.SeriesCollection(2).XValues = "Sheets(sheetname).Range(xaxis)"
ActiveChart.SeriesCollection(2).Values = "Sheets(sheetname).Range(actual)"

"sheetname" is the name of each individual worksheet in the file. That
variable is a string that I've populated earlier in the code.

Thanks!