MrExcel Consulting
Your One Stop for Excel Tips & Solutions

Use VLookup in vba


Posted by Bruno on December 07, 2001 12:13 AM

This is the situation :
worksheet "sheet1" with a 3-column range called "lstSupplier" (suppliername + tel + fax)
worksheet "sheet2" ,cell a1 with the requested suppliername
I want to use Vlookup in a vba module, but i can't figure it out how to do this...
(Vlookup in the worksheet works fine)

Posted by Colo on December 07, 2001 12:31 AM

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

Posted by Benvolio on December 07, 2001 1:19 AM

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


Posted by Bruno on December 07, 2001 1:50 AM

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

Posted by Colo on December 07, 2001 2:13 AM

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
'-------------------------------------------------------------------


Posted by Bruno on December 07, 2001 2:36 AM

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

Posted by Colo on December 07, 2001 3:46 AM

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