Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation to range names for Chart Source Data | Charts and Charting in Excel | |||
Prob with ChartSource DataData Range | Charts and Charting in Excel | |||
How do I select on two variables in a range of data in excel | Excel Worksheet Functions | |||
Chart source data range | Excel Programming | |||
How can i set the source-data-range of pivottable2 to the source . | Excel Discussion (Misc queries) |