Vlookup between different workbooks with Error Handling

  • Hi All,


    I have some code that vlookups between two different workbooks. It works perfectly when there is no error handling (skips when finds an error).


    However, now I need to return a value from another column (Column A) when the error occurs. The code I wrote will get to the first error. Return the value I want, but then the values afterwards are wrong (ie not vlookuping correctly).


    My code is as follows:


  • Re: Vlookup between different workbooks with Error Handling


    Possibly because after an error occurs you never reset the Error object, so Err.Number will always be > 0.


    Try

    Code
    If Err.Number <> 0 Then 
             
            sh.Range("U" & row).Value = sh.Range("A" & row).Value 
            Err.Clear
     
        End If
  • Re: Vlookup between different workbooks with Error Handling


    Thank you so much! It worked!


    Does that mean if I don't reset the error object (with Err.clear) it will just return:


    Code
    sh.Range("U" & row).Value = sh.Range("A" & row).Value
  • Re: Vlookup between different workbooks with Error Handling


    Quote

    it will just return:...


    It never goes away. Once an error is raised (even if you use 'On Error Resume Next'), that error is Active until:


    • The Procedure Exits
      or
    • A Resume/Resume Next statement executes
      or
    • It is cleared using Err.Clear


    Err.Number will always be <> 0 while an Error is Active

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!