testing for #NA with VBA

Discussion in 'Microsoft Excel Programming' started by Guest, May 25, 2005.

  1. Guest

    Guest Guest

    I am using VBA to loop through a range and if the cell contains an Excel
    generated "#NA" I want to skip executing the functions in the loop.

    To test for the "NA" I have tried the following:

    If ActiveCell.Offset(idx1, 0).Value <> "Error 2042" Then _
    and
    If ActiveCell.Offset(idx1, 0).Value <> "#NA" Then _

    and both give a "Run-time error '13': Type mismatch".

    How can I test for an Excel generated '#NA' in a cell?

    TIA
    Tim Kredlo
    Exterior Wood, Inc
     
    Guest, May 25, 2005
    #1
    1. Advertisements

  2. Guest

    Bob Phillips Guest

    Hi Tim,

    Try this

    Dim fErr As Boolean
    On Error Resume Next
    fErr = ActiveCell.Value = CVErr(xlErrNA)
    On Error GoTo 0
    If fErr Then MsgBox "#N/A"


    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "Tim Kredlo" <> wrote in message
    news:...
    > I am using VBA to loop through a range and if the cell contains an Excel
    > generated "#NA" I want to skip executing the functions in the loop.
    >
    > To test for the "NA" I have tried the following:
    >
    > If ActiveCell.Offset(idx1, 0).Value <> "Error 2042" Then _
    > and
    > If ActiveCell.Offset(idx1, 0).Value <> "#NA" Then _
    >
    > and both give a "Run-time error '13': Type mismatch".
    >
    > How can I test for an Excel generated '#NA' in a cell?
    >
    > TIA
    > Tim Kredlo
    > Exterior Wood, Inc
     
    Bob Phillips, May 25, 2005
    #2
    1. Advertisements

  3. Guest

    STEVE BELL Guest

    Tim,

    Try this:

    Sub xxx()

    If IsError(Activecell) Then

    MsgBox "Hi"

    End If

    End Sub


    --
    rand451
    "Tim Kredlo" <> wrote in message
    news:...
    >I am using VBA to loop through a range and if the cell contains an Excel
    > generated "#NA" I want to skip executing the functions in the loop.
    >
    > To test for the "NA" I have tried the following:
    >
    > If ActiveCell.Offset(idx1, 0).Value <> "Error 2042" Then _
    > and
    > If ActiveCell.Offset(idx1, 0).Value <> "#NA" Then _
    >
    > and both give a "Run-time error '13': Type mismatch".
    >
    > How can I test for an Excel generated '#NA' in a cell?
    >
    > TIA
    > Tim Kredlo
    > Exterior Wood, Inc
     
    STEVE BELL, May 25, 2005
    #3
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Paul James

    testing the state of a checkbox in vba code

    Paul James, Dec 3, 2003, in forum: Microsoft Excel Programming
    Replies:
    10
    Views:
    1,806
    James Cox
    Dec 5, 2003
  2. Mcasteel
    Replies:
    2
    Views:
    1,346
    Guest
    Oct 27, 2004
  3. Ken Loomis

    Testing an unopened (or opened) worksheet for VBA Project protected

    Ken Loomis, Jul 12, 2005, in forum: Microsoft Excel Programming
    Replies:
    1
    Views:
    136
    BrianB
    Jul 18, 2005
  4. Eric

    unit testing code using vba

    Eric, Dec 5, 2005, in forum: Microsoft Excel Programming
    Replies:
    0
    Views:
    202
  5. Guest

    Testing for numeric value in VBA

    Guest, Feb 4, 2006, in forum: Microsoft Excel Programming
    Replies:
    3
    Views:
    312
    Guest
    Feb 4, 2006
Loading...

Share This Page