Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code in 2003 not working in 2007 | Excel Programming | |||
2007 code not working in 2003 | Excel Programming | |||
Simple Excel 2003 macro not working in Excel 2007 | Excel Programming | |||
Simple VBA Code written in Excel 2003 not working in Excel 2000 | Excel Programming | |||
Excel 2003 won't run simple code that Excel XP and Excel 2000 will run | Excel Programming |