Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Charting one cell from multiple sheets | Excel Discussion (Misc queries) | |||
convert relative cell references to absolute cell references via amacro? | Excel Discussion (Misc queries) | |||
Charting in a cell | Charts and Charting in Excel | |||
How to rename references from range names to cell references | Excel Discussion (Misc queries) | |||
Custom charting - Stacked charting with a line | Charts and Charting in Excel |