Use VLookup in vba

Hi Bruno. Try this code!

Sub SampleVlookUp()
Dim rngRequested As Range
Set rngRequested = Sheets("Sheet2").Range("A1")
With Application.WorksheetFunction
Debug.Print .VLookup(rngRequested.Value, Range("lstSupplier"), 2, False) 'TEL
Debug.Print .VLookup(rngRequested.Value, Range("lstSupplier"), 3, False) 'FAX
End With
End Sub

Sub Example_of_Vlookup()
Dim lookFor As Range
Dim rng As Range
Dim col As Integer
Dim found As Variant

Set lookFor = Sheets("Sheet2").Range("A1")
Set rng = Sheets("Sheet1").Columns("A:C")
col = 3

On Error Resume Next
found = Application.Vlookup(lookFor.Value, rng, col, 0)
If IsError(found) Then
MsgBox lookFor & " not found"
Else: MsgBox "The look-up value of " & lookFor & " is " & found & " in column " & col
End If
On Error GoTo 0
End Sub

Colo - Benvolio

Hi guys,
1) the code of Benvolio works fine
2) the code of Colo doesn't work :(
I get : Vlookup is unknown for the classe WorksheetFunction (=translation, I work with a dutch excel, so i get dutch errors, hi)

Yesterday I've tried something like Colo's code, with the same error...

Now my second question : what if "Sheet1" ans "Sheet2" are in different workbooks ?

btw : i changed Benvolio's code like
Set rng = Sheets("Sheet1").Range("lstSupplier")

Bruno

Re: Colo - Benvolio

The code works on my Excel(97-SR2). I don't know why it does not work.
:Vlookup is unknown for the classe WorksheetFunction
If so replace "With Application.WorksheetFunction"-"With Application" only.
And please try again.

:what if "Sheet1" ans "Sheet2" are in different workbooks ?
You need book name like this (If name of a different workbook is "Book1.xls")

Set rng = WorkBooks("Book1.xls").Sheets("Sheet1").Range("lstSupplier")

Here's the code I modified a little.

'-------------------------------------------------------------------
Sub SampleVlookUp()
On Error GoTo ErrLine
Dim rngRequested As Range, rng As Range
Set rngRequested = Sheets("Sheet2").Range("A1")
Set rng = Workbooks("Book1.xls").Sheets("Sheet1").Range("lstSupplier")
With Application
Debug.Print .VLookup(rngRequested.Value, rng, 2, False) 'TEL
Debug.Print .VLookup(rngRequested.Value, rng, 3, False) 'FAX
Exit Sub
ErrLine:
Debug.Print "Error"
End With
End Sub
'-------------------------------------------------------------------

Re: Colo

Colo,
"If so replace "With Application.WorksheetFunction"-"With Application" only."

Ok, now it works fine, yesterday I also used "Application.WorksheetFunction" instead of "Application."

Thank again

Re: Colo

Hello again.
:Ok, now it works fine,
That's well!
In this case, I always use "Find Method" and "offset property".
Here is another sample.

Sub SampleFind()
Dim rngSearch As Range
Dim strWord As String
Dim rngFound As Range
Dim rngFirst As Range

Set rngSearch = Sheets("Sheet1").Range("lstSupplier")
strWord = Sheets("Sheet2").Range("A1").Value
Set rngFound = rngSearch.Find(What:=strWord, LookAt:=xlWhole, MatchByte:=False)

If rngFound Is Nothing Then
Debug.Print "Nothing"
Exit Sub
End If
Set rngFirst = rngFound
Do
With rngFound
Debug.Print " [suppliername] " & .Value;
Debug.Print " [TEL] " & .Offset(, 1).Value;
Debug.Print " [FAX] " & .Offset(, 2).Value
End With
Set rngFound = rngSearch.FindNext(rngFound)
Loop While rngFound.Address <> rngFirst.Address
End Sub