charting cell references
 

Im getting a nasty message when using this code


With ActiveChart.SeriesCollection.NewSeries
.Name = "2"
.Values = Range(.Cells(5, "b"), .Cells(5, "l"))
.XValues = Range(.Cells(length, "b"), .Cells(length, "l"))
End With

I want to be able to cycle through several samples ( the length var).
Im assuming .values doesnt like range(.cell etc

Is there another way to get this done

Cheers

Ian

charting cell references
 

Ian Mangelsdorf wrote:
Im getting a nasty message when using this code

With ActiveChart.SeriesCollection.NewSeries
.Name = "2"
.Values = Range(.Cells(5, "b"), .Cells(5, "l"))
.XValues = Range(.Cells(length, "b"), .Cells(length, "l"))
End With


It is certainly a mistake to have . in front of Cells.

That is trying to make Cells a property of the Series object (the
object in the enclosing With statement), whereas leaving out the .
makes it a property of the ActiveSheet.

Try this

With ActiveChart.SeriesCollection.NewSeries
.Name = "2"
.Values = Range(Cells(5, "b"), Cells(5, "l"))
.XValues = Range(Cells(length, "b"), Cells(length, "l"))
End With

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup

charting cell references
 

Thanks Bill

Ive made the changes but am now getting another @!#$@ error

when I run the sub

Method 'cells' of Object '_Global' fails.

Am I not referencing the worksheet properly?

Sample_no is a name reference in the active worksheet

Sub AddChartObject()
Dim myChtObj As ChartObject

Dim sample_no As Range
Dim length As Integer

Set sample_no = Range("sample_No")
length = 5
For Each cell In sample_no
If cell.Value 0 Then
length = length + 1
End If
Next cell


Set myChtObj = ActiveSheet.ChartObjects.Add _
(Left:=100, Width:=375, Top:=75, Height:=225)
myChtObj.Chart.ChartType = xlXYScatterLines

myChtObj.Activate


With ActiveChart.SeriesCollection.NewSeries
.Name = "2"
.Values = Range(Cells(5, "b"), Cells(5, "l"))
.XValues = Range(Cells(length, "b"), Cells(length, "l"))
End With



End Sub

Cheers

Ian

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

charting cell references
 

? Range(Cells(5, "b"), Cells(5, "l")).Address
$B$5:$L$5
Length = 20
? Range(Cells(length, "b"), Cells(length, "l")).Address
$B$20:$L$20

works for me.

Perhaps the problem is with how you are trying to use them.

--
Regards,
Tom Ogilvy

"Ian Mangelsdorf" wrote in message
...
Thanks Bill

Ive made the changes but am now getting another @!#$@ error

when I run the sub

Method 'cells' of Object '_Global' fails.

Am I not referencing the worksheet properly?

Sample_no is a name reference in the active worksheet

Sub AddChartObject()
Dim myChtObj As ChartObject

Dim sample_no As Range
Dim length As Integer

Set sample_no = Range("sample_No")
length = 5
For Each cell In sample_no
If cell.Value 0 Then
length = length + 1
End If
Next cell


Set myChtObj = ActiveSheet.ChartObjects.Add _
(Left:=100, Width:=375, Top:=75, Height:=225)
myChtObj.Chart.ChartType = xlXYScatterLines

myChtObj.Activate


With ActiveChart.SeriesCollection.NewSeries
.Name = "2"
.Values = Range(Cells(5, "b"), Cells(5, "l"))
.XValues = Range(Cells(length, "b"), Cells(length, "l"))
End With



End Sub

Cheers

Ian

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

charting cell references
 

Ian Mangelsdorf wrote:
Ive made the changes but am now getting another @!#$@ error


Is the macro triggered by a Control Toolbox command button?
If so, set its TakeFocusOnClick property to False.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup

charting cell references
 

Ian -

I think you need to be more specific with Range:

With ActiveChart.SeriesCollection.NewSeries
.Name = "2"
.Values = ActiveSheet.Range(Cells(5, "b"), Cells(5, "l"))
.XValues = ActiveSheet.Range(Cells(length, "b"), Cells(length, "l"))
End With

or with Cells

With ActiveChart.SeriesCollection.NewSeries
.Name = "2"
.Values = ActiveSheet.Range(ActiveSheet.Cells(5, "b"), _
ActiveSheet.Cells(5, "l"))
.XValues = ActiveSheet.Range(ActiveSheet.Cells(length, "b"), _
ActiveSheet.Cells(length, "l"))
End With

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Ian Mangelsdorf wrote:

Thanks Bill

Ive made the changes but am now getting another @!#$@ error

when I run the sub

Method 'cells' of Object '_Global' fails.

Am I not referencing the worksheet properly?

Sample_no is a name reference in the active worksheet

Sub AddChartObject()
Dim myChtObj As ChartObject

Dim sample_no As Range
Dim length As Integer

Set sample_no = Range("sample_No")
length = 5
For Each cell In sample_no
If cell.Value 0 Then
length = length + 1
End If
Next cell


Set myChtObj = ActiveSheet.ChartObjects.Add _
(Left:=100, Width:=375, Top:=75, Height:=225)
myChtObj.Chart.ChartType = xlXYScatterLines

myChtObj.Activate


With ActiveChart.SeriesCollection.NewSeries
.Name = "2"
.Values = Range(Cells(5, "b"), Cells(5, "l"))
.XValues = Range(Cells(length, "b"), Cells(length, "l"))
End With



End Sub

Cheers

Ian

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!