Pasting line to a chart point

Paul · 05-18-2006, 07:10 AM

The following code fails in the next to last line with error statement,
"Select method of point class failed"

If i use an integer rather than the variable, it works; however, I need to
use the variable to paste the line to he most recent point.

What am I doing wrong???

Sub drawline()
ActiveSheet.ChartObjects("Chart 967").Activate
ActiveChart.SeriesCollection(4).Select
xxx = ActiveChart.SeriesCollection(4).Points.Count
Range("X1").Select
ActiveSheet.Shapes("Line 969").Select
Selection.Copy
ActiveSheet.ChartObjects("Chart 967").Activate
ActiveChart.SeriesCollection(4).Select
ActiveChart.SeriesCollection(4).Points(xxx).Select
Selection.Paste
End Sub

Andy Pope · 05-18-2006, 08:45 AM

Hi,

No need to select things. This works for me and does nothing if last
point in data series is empty or #N/A.

Sub drawline()

Dim vntData As Variant

ActiveSheet.Shapes("Line 969").Copy

With ActiveSheet.ChartObjects("Chart 967").Chart.SeriesCollection(4)
vntData = .Values
If Not IsError(vntData(.Points.Count)) Then
.Points(.Points.Count).Paste
End If
End With

End Sub

Cheers
Andy

Paul wrote:
> The following code fails in the next to last line with error statement,
> "Select method of point class failed"
>
> If i use an integer rather than the variable, it works; however, I need to
> use the variable to paste the line to he most recent point.
>
> What am I doing wrong???
>
> Sub drawline()
> ActiveSheet.ChartObjects("Chart 967").Activate
> ActiveChart.SeriesCollection(4).Select
> xxx = ActiveChart.SeriesCollection(4).Points.Count
> Range("X1").Select
> ActiveSheet.Shapes("Line 969").Select
> Selection.Copy
> ActiveSheet.ChartObjects("Chart 967").Activate
> ActiveChart.SeriesCollection(4).Select
> ActiveChart.SeriesCollection(4).Points(xxx).Select
> Selection.Paste
> End Sub

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Paul · 05-18-2006, 12:10 PM

Thanks Andy, but I cannot get yours to work either. I think the trouble is
that the column data, which the chart depicts starts as filled with #N/A. it
is progressively filled from top to bottom with the values that I want the
chart to display. The reason i use #N/A is that the chart simply does not
attempt to chart it --- so the line just ends with the last point entered.

Maybe an example would help:

an example would be the following data in col A1:A10
---3,4,6,5,7,8,#n/a,#n/a,#n/a,#n/a

the line in a line chart depicting that data would end abruptly at the 8
point.

and I want to paste a horizontal line to that point.

Also, the chart is on a worksheet and the line is not in the chart, but on
the worksheet.

"Andy Pope" wrote:

> Hi,
>
> No need to select things. This works for me and does nothing if last
> point in data series is empty or #N/A.
>
> Sub drawline()
>
> Dim vntData As Variant
>
> ActiveSheet.Shapes("Line 969").Copy
>
> With ActiveSheet.ChartObjects("Chart 967").Chart.SeriesCollection(4)
> vntData = .Values
> If Not IsError(vntData(.Points.Count)) Then
> .Points(.Points.Count).Paste
> End If
> End With
>
> End Sub
>
> Cheers
> Andy
>
> Paul wrote:
> > The following code fails in the next to last line with error statement,
> > "Select method of point class failed"
> >
> > If i use an integer rather than the variable, it works; however, I need to
> > use the variable to paste the line to he most recent point.
> >
> > What am I doing wrong???
> >
> > Sub drawline()
> > ActiveSheet.ChartObjects("Chart 967").Activate
> > ActiveChart.SeriesCollection(4).Select
> > xxx = ActiveChart.SeriesCollection(4).Points.Count
> > Range("X1").Select
> > ActiveSheet.Shapes("Line 969").Select
> > Selection.Copy
> > ActiveSheet.ChartObjects("Chart 967").Activate
> > ActiveChart.SeriesCollection(4).Select
> > ActiveChart.SeriesCollection(4).Points(xxx).Select
> > Selection.Paste
> > End Sub
>
> --
>
> Andy Pope, Microsoft MVP - Excel
> http://www.andypope.info
>

Andy Pope · 05-18-2006, 12:30 PM

Try this modification, which will use the last valid point rather than
the last actual point.

Sub drawline()

Dim vntData As Variant
Dim lngIndex As Long

ActiveSheet.Shapes("Line 969").Copy

With ActiveSheet.ChartObjects("Chart 967").Chart.SeriesCollection(1)
vntData = .Values
For lngIndex = UBound(vntData) To LBound(vntData) Step -1
If Not IsError(vntData(lngIndex)) Then
.Points(lngIndex).Paste
Exit For

End If
Next
End With

End Sub

Paul wrote:
> Thanks Andy, but I cannot get yours to work either. I think the trouble is
> that the column data, which the chart depicts starts as filled with #N/A. it
> is progressively filled from top to bottom with the values that I want the
> chart to display. The reason i use #N/A is that the chart simply does not
> attempt to chart it --- so the line just ends with the last point entered.
>
> Maybe an example would help:
>
> an example would be the following data in col A1:A10
> ---3,4,6,5,7,8,#n/a,#n/a,#n/a,#n/a
>
> the line in a line chart depicting that data would end abruptly at the 8
> point.
>
> and I want to paste a horizontal line to that point.
>
> Also, the chart is on a worksheet and the line is not in the chart, but on
> the worksheet.
>
>
> "Andy Pope" wrote:
>
>
>>Hi,
>>
>>No need to select things. This works for me and does nothing if last
>>point in data series is empty or #N/A.
>>
>>Sub drawline()
>>
>> Dim vntData As Variant
>>
>> ActiveSheet.Shapes("Line 969").Copy
>>
>> With ActiveSheet.ChartObjects("Chart 967").Chart.SeriesCollection(4)
>> vntData = .Values
>> If Not IsError(vntData(.Points.Count)) Then
>> .Points(.Points.Count).Paste
>> End If
>> End With
>>
>>End Sub
>>
>>Cheers
>>Andy
>>
>>Paul wrote:
>>
>>>The following code fails in the next to last line with error statement,
>>>"Select method of point class failed"
>>>
>>>If i use an integer rather than the variable, it works; however, I need to
>>>use the variable to paste the line to he most recent point.
>>>
>>>What am I doing wrong???
>>>
>>> Sub drawline()
>>>ActiveSheet.ChartObjects("Chart 967").Activate
>>> ActiveChart.SeriesCollection(4).Select
>>> xxx = ActiveChart.SeriesCollection(4).Points.Count
>>> Range("X1").Select
>>> ActiveSheet.Shapes("Line 969").Select
>>> Selection.Copy
>>> ActiveSheet.ChartObjects("Chart 967").Activate
>>> ActiveChart.SeriesCollection(4).Select
>>> ActiveChart.SeriesCollection(4).Points(xxx).Select
>>> Selection.Paste
>>>End Sub
>>
>>--
>>
>>Andy Pope, Microsoft MVP - Excel
>>http://www.andypope.info
>>

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Paul · 05-18-2006, 01:15 PM

mmmmm!!!!
]:-)
it don't get no better den dat!!!!

thanks!!!!

"Andy Pope" wrote:

> Try this modification, which will use the last valid point rather than
> the last actual point.
>
> Sub drawline()
>
> Dim vntData As Variant
> Dim lngIndex As Long
>
> ActiveSheet.Shapes("Line 969").Copy
>
> With ActiveSheet.ChartObjects("Chart 967").Chart.SeriesCollection(1)
> vntData = .Values
> For lngIndex = UBound(vntData) To LBound(vntData) Step -1
> If Not IsError(vntData(lngIndex)) Then
> .Points(lngIndex).Paste
> Exit For
>
> End If
> Next
> End With
>
> End Sub
>
>
> Paul wrote:
> > Thanks Andy, but I cannot get yours to work either. I think the trouble is
> > that the column data, which the chart depicts starts as filled with #N/A. it
> > is progressively filled from top to bottom with the values that I want the
> > chart to display. The reason i use #N/A is that the chart simply does not
> > attempt to chart it --- so the line just ends with the last point entered.
> >
> > Maybe an example would help:
> >
> > an example would be the following data in col A1:A10
> > ---3,4,6,5,7,8,#n/a,#n/a,#n/a,#n/a
> >
> > the line in a line chart depicting that data would end abruptly at the 8
> > point.
> >
> > and I want to paste a horizontal line to that point.
> >
> > Also, the chart is on a worksheet and the line is not in the chart, but on
> > the worksheet.
> >
> >
> > "Andy Pope" wrote:
> >
> >
> >>Hi,
> >>
> >>No need to select things. This works for me and does nothing if last
> >>point in data series is empty or #N/A.
> >>
> >>Sub drawline()
> >>
> >> Dim vntData As Variant
> >>
> >> ActiveSheet.Shapes("Line 969").Copy
> >>
> >> With ActiveSheet.ChartObjects("Chart 967").Chart.SeriesCollection(4)
> >> vntData = .Values
> >> If Not IsError(vntData(.Points.Count)) Then
> >> .Points(.Points.Count).Paste
> >> End If
> >> End With
> >>
> >>End Sub
> >>
> >>Cheers
> >>Andy
> >>
> >>Paul wrote:
> >>
> >>>The following code fails in the next to last line with error statement,
> >>>"Select method of point class failed"
> >>>
> >>>If i use an integer rather than the variable, it works; however, I need to
> >>>use the variable to paste the line to he most recent point.
> >>>
> >>>What am I doing wrong???
> >>>
> >>> Sub drawline()
> >>>ActiveSheet.ChartObjects("Chart 967").Activate
> >>> ActiveChart.SeriesCollection(4).Select
> >>> xxx = ActiveChart.SeriesCollection(4).Points.Count
> >>> Range("X1").Select
> >>> ActiveSheet.Shapes("Line 969").Select
> >>> Selection.Copy
> >>> ActiveSheet.ChartObjects("Chart 967").Activate
> >>> ActiveChart.SeriesCollection(4).Select
> >>> ActiveChart.SeriesCollection(4).Points(xxx).Select
> >>> Selection.Paste
> >>>End Sub
> >>
> >>--
> >>
> >>Andy Pope, Microsoft MVP - Excel
> >>http://www.andypope.info
> >>
>
> --
>
> Andy Pope, Microsoft MVP - Excel
> http://www.andypope.info
>