ExcelBanter - Pasting line to a chart point

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Pasting line to a chart point (https://www.excelbanter.com/excel-programming/361790-pasting-line-chart-point.html)

Pasting line to a chart point

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

Pasting line to a chart point

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

Pasting line to a chart point

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

Pasting line to a chart point

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

Pasting line to a chart point

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