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

MS EXCEL

    Sponsored Links

    Next

  • 1. date format...3.1.2010
    Hi all Excamble: In the cell: 3.1.2010, formula bar looks--> 3.1.2010 9:21:48 AM. Cell format is general, why? How I change this to 3.1.2010?
  • 2. Merge 1000+ cells into one
    Good morning I am really hoping someone can help me with this. I have a worksheet with 1 column (A) and 1439 rows. I need to combine all 1439 of these cells into one, using the following format: A1 & "" & A2 & "" etc. Naturally, doing this manually would take several hours (and be extremely vulnerable to mistakes.) Is there a method using VBA that I could do this? Thanks for any ideas. Paul
  • 3. Date Format
    Hi there, I'm updating my excel sheet data from ASP. I've a date field. After updating from Excel Sheet, the cell format changes into a number( I thing dateserial number) eventhough I formatted the cell to accept date. I want to show the date in the excel sheet, I used CDate also & my reginal seeting also correct. Pls help me to sort this. Regards.
  • 4. date format...
    Hi all Excamble: In the cell: Mon Mar 1, 2010 9:21:48 AM looks in formula bar --> Mon Mar 1, 2010 9:21:48 AM. Cell format is general. How I change this to 3.1.2010?

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

Postby S01I » Wed, 10 Mar 2010 23:47:01 GMT

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


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

Postby Phil Hibbs » Thu, 11 Mar 2010 02:26:41 GMT


> If WorksheetFunction.IsNA(Cells(RowNumStart + X - 1, ColNum)) Then

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{*filter*}?

Phil Hibbs.

Similar Threads:

1.Excel 2000 VBA App Now Works in Excel 2003, but not Excel 2002

I have a VBA app that I originally wrote in Excel 2000.   Someone who
had Excel 2003 tried it and found compile errors.   I downloaded a
trial version of Excel 2003 and debugged the app.   I found that the
problem was a Calendar control that was available in Excel 2000, but no
longer supported in Excel 2003.  I replaced the Calendar control with
the new Date Picker control, and the app is working in Excel 2003.

But I had another person the new version with Excel 2002, and that
person is not able to run the app due to compile errors as well.  That
person is coming up with a "Can't find project or library" compile
error.   This error message seem similar to the error I was originally
seeing when I tried to run the original Excel 2000 app in Excel 2003.
Also, when forcing a step over, the error seems to be pointing to a
"Format" statement on a date.   However, I know from debugging the code
for Excel 2003 that the problem is probably not the "Format" statement,
but rather the Calendar control I was using.  I don't understand what
causing the problem now in Excel 2002.  Does Excel 2002 support a Date
Picker control?

Thanks in advance.

2.SUMIF formula works in Excel 2003, does not work in Excel 2007

Folks:
I have an example of a SUMIF formula that works in Excel 2003, but not in 
2007.
We did an IF test of one of the cells in the evaluation range with the 
criteria, and it came back TRUE (in other words, the IF formula confirmed a 
MATCH).

Has anyone else seen this issue in Excel 2007? 

thanks,
Harry

3.Simple VBA Code written in Excel 2003 not working in Excel 200

Thanks Michael for your help.  I made the changes that you suggested, 
explicitly declaring each of the worksheets as worksheets.  I just sent the 
file to the user so I'll let you know if it worked.

Do you think there could be a problem with the "DrawingObjects" part of the 
protect method?  I think that Excel 2003 has more options to proect a 
worksheet than Excel 2000 does, and if so, perhaps this could be a problem as 
well if it is not recognized in Excel 2000???

Thanks again!
Rich


"Michael" wrote:

> Use Worksheets instead of Sheets, or 
> First:
> Dim wks as worksheet
> Dim wks2 as worksheet
> Second:
> 
> Set wks = WorkSheets("TrlInputs")
> Set wks2 = WorkSheets("GenInputs") 
> Third:
> 
>  Application.ScreenUpdating = False                            
>      wks.select                                                
>                                                                
>      wks.Unprotect Password:="password"                        
>      With wks                                                  
>          .Rows("12:13").EntireRow.Hidden = True                
>          .Rows("21:54").EntireRow.Hidden = False               
>      End With                                                  
>      wks.Range("A1").Select                                    
>      wks.Protect Password:="password", DrawingObjects:=True    
>                                                                
>      wks2.Select                                               
>      wks2.Range("H4").Select                                   
>      Application.ScreenUpdating = True                         
>                                                                
> Michael Arch.
> 
> 
> 
> 
> "Rich B." wrote:
> 
> > Hi.  I wrote some simple code in Excel 2003 (see below) to hide certain rows 
> > if the user selects a certain option button.  It works fine on Excel 2003, 
> > but when the user tries it on his machine (he has Excel 2000), he gets a 
> > run-time 1004 error message.  From what he describes it sounds like the error 
> > is ocurring somewhere after the "End With" line since when he clicks "End" on 
> > the error message screen, he is taken to the "TrlInputs" sheet (the started 
> > on the "GenInputs" sheet since that is where the option button is located).
> > 
> > Any help would be greatly appreciated!
> > 
> > Thanks.
> > Rich
> > 
> > 
> > 
> > Private Sub OptionButtonActual_Click()
> > 
> >     Application.ScreenUpdating = False
> >     Sheets("TrlInputs").Select
> >     
> >     Sheets("TrlInputs").Unprotect Password:="password"
> >     With Sheets("TrlInputs")
> >         .Rows("12:13").EntireRow.Hidden = True
> >         .Rows("21:54").EntireRow.Hidden = False
> >     End With
> >     Sheets("TrlInputs").Range("A1").Select
> >     Sheets("TrlInputs").Protect Password:="password", DrawingObjects:=True
> > 
> >     Sheets("GenInputs").Select
> >     Sheets("GenInputs").Range("H4").Select
> >     Application.ScreenUpdating = True
> >     
> > End Sub

4.Simple VBA Code written in Excel 2003 not working in Excel 2000

Hi.  I wrote some simple code in Excel 2003 (see below) to hide certain rows 
if the user selects a certain option button.  It works fine on Excel 2003, 
but when the user tries it on his machine (he has Excel 2000), he gets a 
run-time 1004 error message.  From what he describes it sounds like the error 
is ocurring somewhere after the "End With" line since when he clicks "End" on 
the error message screen, he is taken to the "TrlInputs" sheet (the started 
on the "GenInputs" sheet since that is where the option button is located).

Any help would be greatly appreciated!

Thanks.
Rich



Private Sub OptionButtonActual_Click()

    Application.ScreenUpdating = False
    Sheets("TrlInputs").Select
    
    Sheets("TrlInputs").Unprotect Password:="password"
    With Sheets("TrlInputs")
        .Rows("12:13").EntireRow.Hidden = True
        .Rows("21:54").EntireRow.Hidden = False
    End With
    Sheets("TrlInputs").Range("A1").Select
    Sheets("TrlInputs").Protect Password:="password", DrawingObjects:=True

    Sheets("GenInputs").Select
    Sheets("GenInputs").Range("H4").Select
    Application.ScreenUpdating = True
    
End Sub

5.Function working in Excel 2003 gives an error in 2007

I'm a newbie in VBA, so I might have made a stupid mistake. The
following function procedure worked fine in Excel 2003 at my workplace
but gives a #NAME error in 2007. I appreciate your help.

Also, please comment if you have any recommendation to simplify or
speed up the procedure. Thanks!!

Function Rent(Startmonth As Integer, CurMonth As Integer, RemMonth As
Integer, DT1 As Integer, DT2 As Integer, LeasePrd1 As Integer,
LeasePrd2 As Integer, CurAmount As Currency, AskAmount As Currency,
Inflation As Currency)

Dim i As Integer

For i = 1 To 10

    Select Case CurMonth

    Case Is <= (Startmonth + RemMonth): Rent = CurAmount

    Case Startmonth + RemMonth + DT1 + 1 To _
         Startmonth + RemMonth + DT1 + LeasePrd1
         Rent = AskAmount * Inflation

    Case Startmonth + RemMonth + DT1 + LeasePrd1 + DT2 + 1 To _
         Startmonth + RemMonth + DT1 + LeasePrd1 + DT2 + LeasePrd2
         Rent = AskAmount * Inflation

    Case Startmonth + RemMonth + DT1 + LeasePrd1 + DT2 * (i + 1) +
LeasePrd2 * i + 1 To _
         Startmonth + RemMonth + DT1 + LeasePrd1 + (DT2 + LeasePrd2) *
(i + 1)
         Rent = AskAmount * Inflation

    End Select

Next i

End Function

6. Picture compression in Excel 2007 - works in 2003

7. 2003 vba to hide two grouped charts, works in 2007 but not in 2003

8. Excel 2007 Macro Help (Excel 2003 not working in 2007)



Return to MS EXCEL

 

Who is online

Users browsing this forum: No registered users and 24 guest