charting cell references

  • Thread starter Thread starter Ian Mangelsdorf
  • Start date Start date
I

Ian Mangelsdorf

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
 
Ian said:
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
Click to expand...

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
 
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
 
? 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.
 
Ian said:
Ive made the changes but am now getting another @!#$@ error
Click to expand...

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
 
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
 
Back
Top