Results 1 to 5 of 5
  1. #1
    Join Date
    May 2004
    Posts
    56

    Unanswered: For...Next erroring on #N/A

    A simple loop I've setup to run down a range on an active worksheet to count values is erroring when it hits any #N/A value. I'm using the the "For c Range(A3: A500)..next" looping approach and setting on variable to the column in the range to c.value and an adjacent column to c.offset(0,1).value. I run an If asking if variable = "Yes", or = "No".

    Works if the values are blank or a valid string just not if #N/A from a vlookup!

    I've tried setting the variable as string or variant. Get two different error #s.

    I've also tried using If IsNull(variable) or IsEmpty(variable) or If variable = "" without success.

    Any suggestings for handling these pesky #N/A values???!!!!

    (Sorry I can paste in the code. It's on a PC that's restricted and I can't take anything off it and rather not retype it here. Sorry.)
    Please help...somebody. Thanks!

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    If everything else works, you can add a line as this:

    Code:
    On Error Resume Next
    Or you could try a more complete error handling routine. Check out Jon Peltier's approach.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Feb 2004
    Posts
    533
    Are you getting a Type Mismatch Error?
    This will occur when you compare an Error type to a String Type value ?Error 2042 = "Yes".
    You can avoid this by definining your variable as a String at the top of your Sub or Function:
    Dim strVar as String

    Or, You can check the Cell Value with the isError() function

    Or, You can use cStr() to convert the variable value to a Text string.
    ~

    Bill

  4. #4
    Join Date
    May 2004
    Posts
    56
    Thanks everyone for the ideas. I think I tried the IsError (you mean IsErr?) without success but I'll try again. Will try anything else suggested too. This problem is a real hassle! Ugh!

  5. #5
    Join Date
    Feb 2004
    Posts
    533
    Quote Originally Posted by paulzak
    I think I tried the IsError (you mean IsErr?) without success but I'll try again.
    The Worksheet Formula is, IsErr()

    The IsError funtion is, IsError()

    You would use the isError function in a Procedure with Excel VB script.

    Make sure you refer to the Range in the isError expression and not the cell address as a string value. For example:

    ?isError("D14") 'This returns False as it is checking the text string "D14"
    ?isError(Range("D14")) 'This returns True if there is an error in the range.

    Looks obvious but it can be an easy mistake to make.
    ~

    Bill

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •