using variables in the range of source data

[Guest]

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

 

[Guest]

Hi

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

Alok

 

[Dave Peterson]

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.

 

[Guest]

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

 

[Guest]

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]

Only if you tell me what Rows means...

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

 

[Guest]

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]

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.

 

[Guest]

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