How to set Chart SetSourceData in code..?

[Andrew]

I have...

Worksheet("Sheet1")
r1, c1, r2, c2 As Integer

r1 = 24: c1 = 2: r2 = 68: c2 = 2

Charts("Chart1").SeriesCollection(1).Values =
Worksheets("Sheet1").Range(Cells(r1, c1), Cells(r2,c2))

r1 & r2 will change according to user interaction in other code.
What have i missed..?

Any help would be greatly appreciated.

 

[Barb Reinhardt]

Try it with a continuous range and see if that works. If it does, that's
your problem.

 

[Barb Reinhardt]

Just noticed this

Charts("Chart1").SeriesCollection(1).Values =
Worksheets("Sheet1").Range(Cells(r1, c1), Cells(r2,c2))

If Sheets1 isn't the active sheet, you may have problems with the Cells
piece since it's pointing to the active sheet.

 

[Andrew]

Made sure Sheet is active..

Runtime Error '9':
Subscript out of range

Nice try though Barb..!

message | Just noticed this
|
| Charts("Chart1").SeriesCollection(1).Values =
| Worksheets("Sheet1").Range(Cells(r1, c1), Cells(r2,c2))
|
| If Sheets1 isn't the active sheet, you may have problems with the
Cells
| piece since it's pointing to the active sheet.
|
| "Andrew" wrote:
|
| > I have...
| >
| > Worksheet("Sheet1")
| > r1, c1, r2, c2 As Integer
| >
| > r1 = 24: c1 = 2: r2 = 68: c2 = 2
| >
| > Charts("Chart1").SeriesCollection(1).Values =
| > Worksheets("Sheet1").Range(Cells(r1, c1), Cells(r2,c2))
| >
| > r1 & r2 will change according to user interaction in other code.
| > What have i missed..?
| >
| > Any help would be greatly appreciated.
| >
| >
| >
| >

 

[Barb Reinhardt]

I don't do this often, so you may want to look here for ideas

http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html

HTH,
Barb Reinhardt

 

[Dave Peterson]

I'd try:

with worksheets("Sheet1")
Charts("Chart1").SeriesCollection(1).Values _
= .Range(.Cells(r1, c1), .Cells(r2,c2)).value
end with

If the code is behind a worksheet, then those unqualifed ranges won't refer to
the activesheet--they'll refer to the sheet that owns the code.

 

[Dave Peterson]

Subscript out of range means that you're trying to use something that doesn't
exist.

Is there a worksheet named "Sheet1"?
Is there a chart named "Chart1"?