If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Vba Error handling for missing (#NV) data point
Hello,
in my code, every chart datapoint with a value 0 is assigned the markerbackgroundcolorindex 3 (red). If however, one datapoint in the list has no value (#NV), the vba loop can't select it, consequently the code stops. How can I possibly trap that case and make the loop ..... For i = 1 to 10 ActiveChart.SeriesCollection(1).Points(i).Select With Selection .Markerbackgroundcolorindex = 3 End with .... continue correctly ? Thank you in advance. Kind regards, H.G. Lamy |
#2
|
|||
|
|||
Vba Error handling for missing (#NV) data point
The posted code changes all points, not just those corresponding to
values 0. Also, I don't know a #NV error; maybe, you meant #N/A. Here's one possible solution: Option Explicit Sub testIt() Dim I As Long, z With ActiveChart.SeriesCollection(1).Points 'On Error Resume Next z = ActiveChart.SeriesCollection(1).Values For i = 1 To .Count If IsError(z(i)) Then ElseIf z(i) 0 Then .Item(i).MarkerBackgroundColorIndex = 3 End If Next i End With End Sub -- Regards, Tushar Mehta MS MVP Excel 2000-2003 www.tushar-mehta.com Excel, PowerPoint, and VBA tutorials and add-ins Custom Productivity Solutions leveraging MS Office In article , says... Hello, in my code, every chart datapoint with a value 0 is assigned the markerbackgroundcolorindex 3 (red). If however, one datapoint in the list has no value (#NV), the vba loop can't select it, consequently the code stops. How can I possibly trap that case and make the loop .... For i = 1 to 10 ActiveChart.SeriesCollection(1).Points(i).Select With Selection .Markerbackgroundcolorindex = 3 End with ... continue correctly ? Thank you in advance. Kind regards, H.G. Lamy |
#3
|
|||
|
|||
Vba Error handling for missing (#NV) data point
Thank you very much, Tushar !
(#NV and #N/A must be the same, the first showing up in my German version) Kind regards, H.G. Lamy "Tushar Mehta" schrieb im Newsbeitrag m... The posted code changes all points, not just those corresponding to values 0. Also, I don't know a #NV error; maybe, you meant #N/A. Here's one possible solution: Option Explicit Sub testIt() Dim I As Long, z With ActiveChart.SeriesCollection(1).Points 'On Error Resume Next z = ActiveChart.SeriesCollection(1).Values For i = 1 To .Count If IsError(z(i)) Then ElseIf z(i) 0 Then .Item(i).MarkerBackgroundColorIndex = 3 End If Next i End With End Sub -- Regards, Tushar Mehta MS MVP Excel 2000-2003 www.tushar-mehta.com Excel, PowerPoint, and VBA tutorials and add-ins Custom Productivity Solutions leveraging MS Office In article , says... Hello, in my code, every chart datapoint with a value 0 is assigned the markerbackgroundcolorindex 3 (red). If however, one datapoint in the list has no value (#NV), the vba loop can't select it, consequently the code stops. How can I possibly trap that case and make the loop .... For i = 1 to 10 ActiveChart.SeriesCollection(1).Points(i).Select With Selection .Markerbackgroundcolorindex = 3 End with ... continue correctly ? Thank you in advance. Kind regards, H.G. Lamy |
Thread Tools | |
Display Modes | |
|
|