Results 1 to 7 of 7

Thread: Dealing With An Anticipated Error In A Loop

  1. #1
    Board Regular
    Join Date
    Apr 2005
    Location
    Ontario, Canada
    Posts
    3,175
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Dealing With An Anticipated Error In A Loop

    I have this line of code in my application that I know in advance there may be times it will err out, usually because the value .cells(i,3) doesn't exist ... yet.

    Code:
    .Cells(i, 4) = Application.WorksheetFunction.VLookup(.Cells(i, 3), ws_rd.Range("A:C"), 3, False)
    When it does err, I would like to recognize that by executing some code exclusive to that scenario. Since this line is within a loop though, I don't want this to stop the loop, but carry on.

  2. #2
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,812
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Dealing With An Anticipated Error In A Loop

    Use Application.Vlookup and store the result in a Variant. Then test it with Iserror(variable) and react accordingly.

  3. #3
    Board Regular
    Join Date
    Apr 2005
    Location
    Ontario, Canada
    Posts
    3,175
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dealing With An Anticipated Error In A Loop

    That makes too much sense. Thanks Rory.

  4. #4
    Board Regular
    Join Date
    Apr 2005
    Location
    Ontario, Canada
    Posts
    3,175
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dealing With An Anticipated Error In A Loop

    Quote Originally Posted by Ark68 View Post
    That makes too much sense. Thanks Rory.
    But maybe not enough ...

    Code:
           For i = 2 To lrow
                str_recid = Application.WorksheetFunction.VLookup(.Cells(i, 3), ws_rd.Range("A:C"), 3, False)
                If IsError(str_recid) Then
                    MsgBox "Value missing."
                    'Stop
                End If
           Next i
    I'm encountering an error whether I put the vlookup before or after the IsError.

  5. #5
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,812
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Dealing With An Anticipated Error In A Loop

    Remove the Worksheetfunction. part:

    Code:
    str_recid = Application.VLookup(.Cells(i, 3), ws_rd.Range("A:C"), 3, False)
    and I assume that str_recid is actually declared as Variant as I suggested?

  6. #6
    Board Regular
    Join Date
    Apr 2005
    Location
    Ontario, Canada
    Posts
    3,175
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dealing With An Anticipated Error In A Loop

    Yeah ... things are going well now. It was the worksheetfunction that was the issue.
    I'll need to do some research whn to use it and when not to!

  7. #7
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,812
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Dealing With An Anticipated Error In A Loop

    If you use Worksheetfunction it will cause a run-time error in the code when the function fails. If you use the Application version, it returns an error value that you can test with IsError.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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