Vba Error handling for missing (#NV) data point

[hglamy]

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

 

[Tushar Mehta]

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

 

[hglamy]

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]

You are welcome. Glad to be of help.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions