+ Reply to Thread
Results 1 to 3 of 3

VBA Vlookup Error Handling

  1. #1
    Registered User
    Join Date
    01-15-2019
    Location
    Houston, TX
    MS-Off Ver
    365
    Posts
    2

    VBA Vlookup Error Handling

    I have done something wrong my my error handler - the vlookup, when it does not find a value, fails. Something to do with it being in a loop. Please help!

    Do While i < (lastrow + 1)
    Range("L" & i).Select
    On Error GoTo ErrorHandler
    Range("l" & i) = Application.WorksheetFunction.VLookup(Workbooks("ITEMT.CSV").Sheets("ITEMT").Range("a" & i), Workbooks("ITEMY.CSV").Sheets("ITEMY").Range("a" & i & ":" & "k" & lastrow), 11, False)

    ErrorHandler:
    If Err.Number = 1004 Then
    result = 0
    End If

    bob = Range("k" & i).Value
    bill = Range("l" & i).Value
    If bill = bob Then
    Range("m" & i).Value = "FALSE"
    Else
    Range("m" & i).Value = "TRUE"
    End If
    i = i + 1
    Loop

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,428

    Re: VBA Vlookup Error Handling

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    01-15-2019
    Location
    Houston, TX
    MS-Off Ver
    365
    Posts
    2

    Re: VBA Vlookup Error Handling

    Not sure sending it will work because it is dependent on several other workbooks. What I am trying to do is simple in theory. I have a Do While loop which runs a vlookup between two files to return certain items. When the lookup item is not in the other workbook, I need the program to accept it is not there, use zero for that iteration's value, and resume using the vlookup on the next item.

    I used an error handler for this modified from what I posted which works to keep the code running to the end of the loop, but after the first error, it returns a zero every time. It is as if each iteration of the loop is seen as an error after the first error appears. I have something wrong here in the error handler I am sure - probably the placement or use of "resume next".

    ErrorHandler:

    Range("l" & i).Value = 0
    Resume Next

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Odd Error Handling Behaviour; goes to error handler even when no error?
    By kalikj in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-04-2018, 10:04 AM
  2. Error handling inside error handling
    By grantastley in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-06-2015, 03:43 AM
  3. RUN TIME ERROR '1004' error handling help needed
    By skop89 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-09-2014, 12:54 PM
  4. [SOLVED] Error Handling: Creating code to display error messages
    By Student1990 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-01-2013, 02:21 PM
  5. Error Handling: Can it report line # where error occurred?
    By PingPing in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-20-2010, 03:47 AM
  6. Error Handling - On Error GoTo doesn't trap error successfully
    By David in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-16-2006, 02:10 PM
  7. Error handling with a handling routine
    By ben in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-15-2005, 11:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1