Excel 2003 VB referencing #N/A not working but works in Excel 2007

  • Thread starter Thread starter KMH
  • Start date Start date
K

KMH

I have a very simple macro that works in Excel 2007 but hangs up constantly
in Excel 2003. Does anyone have a work around?
Problem:
When referencing cells that have a #N/A value, Excel 2003 craps out even if
I use the ISNA application function before it. The following 2 statements
will cause Excel 2003 VB to stop regardless of order if the cell that is
referenced comes up with #N/A.
Series = Cells(RowNumStart + X - 1, SeriesCol).Value
If WorksheetFunction.IsNA(Cells(RowNumStart + X - 1, ColNum)) Then

Further background.... I am actually wanting to find #N/A values to turn
"off" the data labels in a stacked bar chart if they are #N/A. In Excel
2007, this is not even required because it won't put #N/A values on the
chart, but Excel 2003 will continue to show them on the chart as 0 value
which clutters up the chart. My macro just checks the value and if a value
turns on the data label and if #N/A turns off the data label. Again, macro
works perfectly in Excel 2007 but I need to give to users who still have
Excel 2003.

Thanks for help
 
KMH said:
When referencing cells that have a #N/A value, Excel 2003 craps out even if
I use the ISNA application function before it. The following 2 statements
will cause Excel 2003 VB to stop regardless of order if the cell that is
referenced comes up with #N/A.
            Series = Cells(RowNumStart + X - 1, SeriesCol).Value
            If WorksheetFunction.IsNA(Cells(RowNumStart + X -1, ColNum)) Then
Click to expand...

Works fine for me, I just wrote a quick test case that calls IsNA(1,2)
and throws up a MsgBox and it works, are you sure that statement is
the one that's hanging?

Phil Hibbs.
 
Back
Top