Simple VBA Code Working in Excel 2007 but not Excel 2003 #N/A
 

I have a fairly simple VBA subroutine that turns data labels on/off on a
stacked bar chart as long as the value is not 0 or NA it turns on. The macro
works perfectly in Excel 2007, but stops on the first #N/A value.
The code stops on one of the following 2 simple commands.

Series = Cells(RowNumStart + X - 1, SeriesCol).Value
If WorksheetFunction.IsNA(Cells(RowNumStart + X - 1, ColNum)) Then

What could be causing this. I have checked that the location in the Cells
location is correct. Just as an FYI, this whole macro wasn't required in
Excel 2007 because Excel 2007 won't chart the #N/A value at all, but Excel
2003 charts it as a 0 value. Thanks for your help.
K

Simple VBA Code Working in Excel 2007 but not Excel 2003 #N/A
 

The IsNA function is new in 2007, which is why your code fails in 2003. Try
something like this

Dim bIsNA As Boolean

With Cells(RowNumStart + X - 1, SeriesCol)
bIsNA = False ' be sure to initialize
If varType(.value) = vbError Then
bIsNA = .Value = CVErr(xlErrNA) Then
End if
' If bIsNa then ' ?

End with

This would probably be faster in 2007 than using the worksheet function

Not sure what your line starting Series = is intended for

' Excel 2003 charts it as a 0 value.
Assuming you are talking about a Line chart #N/A should be interpolated, as
in 2007. Also, with the chart selected, look at Tools, Options, Chart, Plot
empty cells as.

Regards,
Peter T






"KMH" wrote in message
...
I have a fairly simple VBA subroutine that turns data labels on/off on a
stacked bar chart as long as the value is not 0 or NA it turns on. The
macro
works perfectly in Excel 2007, but stops on the first #N/A value.
The code stops on one of the following 2 simple commands.

Series = Cells(RowNumStart + X - 1, SeriesCol).Value
If WorksheetFunction.IsNA(Cells(RowNumStart + X - 1, ColNum)) Then

What could be causing this. I have checked that the location in the Cells
location is correct. Just as an FYI, this whole macro wasn't required in
Excel 2007 because Excel 2007 won't chart the #N/A value at all, but Excel
2003 charts it as a 0 value. Thanks for your help.
K