Archive of Mr Excel Message Board


Back to Excel VBA archive index
Back to archive home



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)

Check out our Excel VBA Resources

Re: Use VLookup in vba

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


Re: Use VLookup in vba

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



Colo - Benvolio

Posted by Bruno on December 07, 2001 1:50 AM
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

Posted by Colo on December 07, 2001 2:13 AM
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

Posted by Bruno on December 07, 2001 2:36 AM
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

Posted by Colo on December 07, 2001 3:46 AM
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




This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.