A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Charts and Charting
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Vba Error handling for missing (#NV) data point



 
 
Thread Tools Display Modes
  #1  
Old October 17th, 2003, 02:43 PM
hglamy
external usenet poster
 
Posts: n/a
Default 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  
Old October 21st, 2003, 05:50 PM
Tushar Mehta
external usenet poster
 
Posts: n/a
Default 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  
Old October 22nd, 2003, 09:34 AM
hglamy
external usenet poster
 
Posts: n/a
Default 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






  #4  
Old October 22nd, 2003, 04:11 PM
Tushar Mehta
external usenet poster
 
Posts: n/a
Default Vba Error handling for missing (#NV) data point

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

In article ,
says...
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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:28 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.