View original (French)

Vlookup

Solved
Marc -  
 Marc -
Hello everyone,

I would like to perform a Vlookup in VB within a Do Until loop.

My Excel file has several sheets.

The Vlookup will be on the sheet called test.

i is a counter on the "test" sheet.

I tried the following syntax:

Cells(i, 8) = Application.WorksheetFunction.VLookup(Cells(i, 3), Sheets("Input").Range("A:A", "AO:AO"), 3, False)

Unfortunately, it doesn't work. I think it's related to the second argument of the function...

Thank you very much for your help.

2 answers

  1.
    f894009 Posted messages 17417 Registration date   Status Member Last intervention   1 717
     
    Hello,

    example with error handling if not found: 

    Sub test1()
     If Not IsError(Application.VLookup(Cells(i, 3), Sheets("Input").Range("A:C"), 3, False)) Then
     y = Application.VLookup(Cells(i, 3), Sheets("Input").Range("A:C"), 3, False)
     ElseIf Not IsError(VLookup(Cells(i, 3), Sheets("Input").Range("AO:AQ"), 3, False)) Then
     y = Application.VLookup(Cells(i, 3), Sheets("Input").Range("AO:AQ"), 3, False)
     Else
     y = "Not Found"
     End If
     Cells(i, 8) = y
    End Sub
    0