| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Fix a comment to a moving datum point in a line chart | Charts and Charting in Excel | |||
| Line and Point in the same chart? | Charts and Charting in Excel | |||
| how to produce horizontal line chart of point est & 95% ci's | Charts and Charting in Excel | |||
| How do I get a value of a calculated point on an excel line chart? | Charts and Charting in Excel | |||
| How to extend a single point into a line in a chart? | Charts and Charting in Excel | |||
Hybrid Mode
