Results 1 to 6 of 6
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    value not found in VlookUp (2000 sr 1)

    Elenco.Range("F" & Trim(Str(Cont))).Value = Application.WorksheetFunction.VLookup(Range("L" & Trim(Str(Cont))), _
    Worksheets("DB").Range(Worksheets("DB").Range("A2" ), Worksheets("DB").Range("B65536").End(xlUp)), 2, False)

    Woth this routine (Tks to Hans) i find and put a value in to re,lated cell.
    Is possible if i donrt found a related value call "mymacro"?
    For example:
    If the value not found into range A call "mymacro"

    THis my macro with another procedure find and insert into column A and B a value not founded.
    After this operation the routine go in the point when not have found the value and repeat the VlookUp?

  2. #2
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: value not found in VlookUp (2000 sr 1)

    I am not sure...
    Is this correct?

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: value not found in VlookUp (2000 sr 1)

    You must create an error handler:

    Sub ...()
    On Error GoTo ErrHandler ' Activate error handling

    ...
    ...
    Elenco.Range(...).Value = Application.WorksheetFunction...
    ...
    ...

    Exit Sub ' Get out before error handler

    ErrHandler:
    Select Case Err
    Case 1004 ' This error occurs if VLookup fails
    Call MyMacro
    Resume

    Case Else
    MsgBox Err.Description, vbExclamation
    End Select
    End Sub

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: value not found in VlookUp (2000 sr 1)

    Almost. You must place the line Exit Sub between the MsgBox and the ErrHandler label:

    ...
    MsgBox ("IMPORT TERMINATO!")

    Exit Sub ' Get out before error handler

    ErrHandler:
    ...

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: value not found in VlookUp (2000 sr 1)

    For you...

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: value not found in VlookUp (2000 sr 1)

    Thanks!

Posting Permissions

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