using variables in the range of source data
 

I have the following line in working code:
ActiveChart.SetSourceData Source:=Sheets("Traffic model").Range("M14:M32"),
PlotBy:=xlColumns

and I want to change the argument of .Range() to use variables instead. So I
wrote:
ActiveChart.SetSourceData Source:=Sheets("Traffic
model").Range(Cells(initial_blank_count - 1, initial_column + 7), Cells(Rows,
initial_column + 7)), PlotBy:=xlColumns

The error a runtime error to effect of an application- or object-defined
error. The variables were used earlier in the sub without problems.

How do I straighten this out please? TIA ... Greg

using variables in the range of source data
 

Hi

The problem may be because you are not qualifying 'Cells' with the name of
the worksheet.

Alok

"gvm" wrote:


I have the following line in working code:
ActiveChart.SetSourceData Source:=Sheets("Traffic model").Range("M14:M32"),
PlotBy:=xlColumns

and I want to change the argument of .Range() to use variables instead. So I
wrote:
ActiveChart.SetSourceData Source:=Sheets("Traffic
model").Range(Cells(initial_blank_count - 1, initial_column + 7), Cells(Rows,
initial_column + 7)), PlotBy:=xlColumns

The error a runtime error to effect of an application- or object-defined
error. The variables were used earlier in the sub without problems.

How do I straighten this out please? TIA ... Greg

using variables in the range of source data
 

Does it work ok if you run it with "Traffic Model" as the activesheet?

You have some unqualified ranges. And in a general module, unqualified ranges
belong to the activesheet.

To make the typing a bit easier, I'd do something like:

dim myRng as range
with worksheets("traffic model")
set myrng = .range(.cells(initial_blank_count-1,initial_column +7), _
.cells(.rows.count,initial_column+7).end(xlup))
end with

then

ActiveChart.SetSourceData Source:=myrng....

But I was confused about this portion:

Cells(Rows, initial_column + 7)

Was Rows a variable? If it is, you may want to change it to something that
doesn't look like a keyword in VBA.

gvm wrote:

I have the following line in working code:
ActiveChart.SetSourceData Source:=Sheets("Traffic model").Range("M14:M32"),
PlotBy:=xlColumns

and I want to change the argument of .Range() to use variables instead. So I
wrote:
ActiveChart.SetSourceData Source:=Sheets("Traffic
model").Range(Cells(initial_blank_count - 1, initial_column + 7), Cells(Rows,
initial_column + 7)), PlotBy:=xlColumns

The error a runtime error to effect of an application- or object-defined
error. The variables were used earlier in the sub without problems.

How do I straighten this out please? TIA ... Greg

--

Dave Peterson

using variables in the range of source data
 

Thanks, I tried the following but got same error:
ActiveChart.SetSourceData Source:=Sheets("Traffic
model").Range(Sheets("Traffic model").Cells(initial_blank_count - 1,
initial_column + 7), Sheets("Traffic model").Cells(Rows, initial_column +
7)), PlotBy:=xlColumns

using variables in the range of source data
 

Thanks Dave,
I qualified the cell references in response to Alok's suggestion. I may have
done it incorrectly (pls see my response to him) because I am seeing the same
error.

In answer to your questions, yes Traffic Model is always the active sheet.
Rows is a (I admit) a poorly chosen variable. So I tried:
Dim YRng As Range
With Worksheets("traffic model")
Set YRng = .Range(.Cells(initial_blank_count - 1, initial_column + 7),
..Cells(Rows, initial_column + 7))
etc

but am still seeing the same error. Thanks for persisting with me, ... Greg

using variables in the range of source data
 

Only if you tell me what Rows means...



gvm wrote:

Thanks Dave,
I qualified the cell references in response to Alok's suggestion. I may have
done it incorrectly (pls see my response to him) because I am seeing the same
error.

In answer to your questions, yes Traffic Model is always the active sheet.
Rows is a (I admit) a poorly chosen variable. So I tried:
Dim YRng As Range
With Worksheets("traffic model")
Set YRng = .Range(.Cells(initial_blank_count - 1, initial_column + 7),
.Cells(Rows, initial_column + 7))
etc

but am still seeing the same error. Thanks for persisting with me, ... Greg

--

Dave Peterson

using variables in the range of source data
 

Dave, Rows was a variable that was used in calculating the number of the last
row in a data series. If have since renamed that variable "LastRow" and I am
still getting the same error. regards .... Greg

"Dave Peterson" wrote:

Only if you tell me what Rows means...



gvm wrote:

Thanks Dave,
I qualified the cell references in response to Alok's suggestion. I may have
done it incorrectly (pls see my response to him) because I am seeing the same
error.

In answer to your questions, yes Traffic Model is always the active sheet.
Rows is a (I admit) a poorly chosen variable. So I tried:
Dim YRng As Range
With Worksheets("traffic model")
Set YRng = .Range(.Cells(initial_blank_count - 1, initial_column + 7),
.Cells(Rows, initial_column + 7))
etc

but am still seeing the same error. Thanks for persisting with me, ... Greg

--

Dave Peterson

using variables in the range of source data
 

Can you show your current code and what each one of the variables is when that
line of code blows up.

A few lines like
debug.print "initial_blank_count: " & initial_blank_count

right before the offending line may help.

gvm wrote:

Dave, Rows was a variable that was used in calculating the number of the last
row in a data series. If have since renamed that variable "LastRow" and I am
still getting the same error. regards .... Greg

"Dave Peterson" wrote:

Only if you tell me what Rows means...



gvm wrote:

Thanks Dave,
I qualified the cell references in response to Alok's suggestion. I may have
done it incorrectly (pls see my response to him) because I am seeing the same
error.

In answer to your questions, yes Traffic Model is always the active sheet.
Rows is a (I admit) a poorly chosen variable. So I tried:
Dim YRng As Range
With Worksheets("traffic model")
Set YRng = .Range(.Cells(initial_blank_count - 1, initial_column + 7),
.Cells(Rows, initial_column + 7))
etc

but am still seeing the same error. Thanks for persisting with me, ... Greg

--

Dave Peterson


--

Dave Peterson

using variables in the range of source data
 

Hi Dave, I fixed it. The data range for the y-series was pointing to a blank
column because I needed the argument "initial_column+6" instead of
"initial_column+7". I am intrigued why an error was generated though, I
wonder why I didn't just get a graph consisting of zero values. Thanks again
for your help, I think the online support we receive is excellent. Cheers ...
Greg