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

    Not found in lookup... (2000 sr 1)

    i have this line of code, how to insert in destination cell range "NON TROVATO" if the vlookup not found value?
    If sh1.Range("J" + RIGA) <> "" Then
    CODICE_R = sh1.Range("J" + RIGA)
    sh2.Range("C" + CONT) = Application.WorksheetFunction.VLookup(CODICE_R, Worksheets("STORIA").Range(Worksheets("STORIA").Ra nge("I2"), Worksheets("STORIA").Range("J10").End(xlUp)), 2, False)
    End If

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Not found in lookup... (2000 sr 1)

    How about this code:

    <pre>Dim lRow As Long
    If sh1.Range("J" & riga) <> "" Then
    With Worksheets("Storia")
    CODICE_R = sh1.Range("J" & riga)
    lRow = 0
    On Error Resume Next
    lRow = Application.WorksheetFunction.Match(CODICE_R, _
    .Range(.Range("I2"), .Range("I10").End(xlUp)), False)
    On Error GoTo 0
    If lRow = 0 Then
    sh2.Range("C" & cont) = "NON TROVATO"
    Else
    sh2.Range("C" & cont) = _
    Application.WorksheetFunction.VLookup(CODICE_R, _
    .Range(.Range("I2"), .Range("J10").End(xlUp)), 2, False)
    End If
    End With
    End If</pre>



    Steve

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

    Re: Not found in lookup... (2000 sr 1)

    KISS!!!
    Work great!

Posting Permissions

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